May 02

The most popular post of Excel in 2013 comes from this site :)

 

Excel Best of 2013

The most popular Excel blog posts of 2013 reflect your interest in the new capabilities we’ve added to Excel this year, including exciting updates to Web Excel and the release of Power Map for Excel. Other favorite posts offer valuable Excel tips and tricks for Excel beginners and experts alike.

Have a look on them and don’t miss the #1 :)

Cheers

The top 10 countdown

10. We’ve Updated Excel Web App–What’s New as of June 2013?
Over the past year, we brought many significant new features to the Excel Web App. As we’ve previously announced, our goal is to deliver Office Web Apps that people can rely on to create polished Office documents from start to finish, all from the web. From AutoComplete to real-time co-authoring, check out the latest updates to Web Excel.

9. Use Webservice functions to automatically update Excel 2013 spreadsheets with online data 
Have you ever wanted to pull live data into your spreadsheet, such as stocks or weather? This post shows you how.

8. Power Map for Excel earns new name with significant updates to 3D visualizations and storytelling
One of the most exciting developments in Excel this past year was the release of Power Map (formerly codenamed “Geoflow”), which allows you to create beautiful, animated visualizations of your geospatial data. This post covers important updates and new capabilities of Power Map for Excel.

7. Dallas Utilities: Electricity seasonal use simulation using project codename “GeoFlow” Preview and Power View
We take an in-depth look at how we built a demo of the powerful capabilities of Power Map using real energy consumption data. After reading this post, you’ll have a good idea of the types of visualizations you can create using Power Map.

6. Need to combine two chart types? Create a combo chart and add a second axis
Learn how to create an elegant view of your data by combining two chart types into one.

5. Creative and unexpected uses of Excel
The versatility of Excel has inspired some people to use it in unconventional ways. Explore some fun alternate applications of Excel, from artwork to games inside of the workbook.

4. Summing data across multiple criteria on multiple worksheets
Excel MVP guest blogger Liam Bastick walks you through a powerful method to combine data from multiple worksheets into a single roll-up report.

3. Webinar: 5 things you need to know about Freezing Panes in Excel
Freeze Panes are one of the most useful features in Excel, because they allow you to keep headings of your data in view while you scroll around the workbook. In this 15-minute video tutorial, we cover tips to help you use Freeze Panes like a pro.

2. Public preview of project codename “GeoFlow” for Excel delivers 3D data visualization and storytelling
Our first post announcing Power Map (then codenamed “Geoflow”) covers its key capabilities for creating beautiful visualizations of geospatial data.

1. Using conditional formatting to highlight dates in Excel
Our most popular post of the year is a series of tips and tricks for automatically color coding dates in your data using conditional formatting. Excel MVP guest blogger Frédéric Le Guen covers how to highlight weekends, holidays, and more.

Source: http://blogs.office.com/2014/01/07/your-top-10-favorite-excel-posts-of-2013/

Feb 24

Excel for free

Free for Excel! It was the dream for all and now it’s real :)

For years, the only way to use for free microsoft Excel was to download it and use a “hack” serial key. Very risky technique especially in accordance with the law. Now, this “technique” is difficult to use because the new version of Office is better secured.

But today, you can open, edit and save your Excel documents (an Office document as well) without installing Microsoft Office on your computer.

HOW TO GET EXCEL FOR FREE?

In fact, with the Cloud technology, it is easier to use Office applications from a browser (Internet Explorer, Firefox, Chrome, …) rather than install it on your computer. On the one hand it takes up less space on your computer and most importantly, the updates are made automatically.

By connecting to the address www.office.com , you arrive on the following home page

excel-for-free_1

Then, you click on Connect and you must enter your hotmail address (or msn), which gives you access to your Microsoft account.

excel-for-free_2

There, you should see your name at the top of the page. This way you know that all documents that you create are stored in your personal space OneDrive.

excel-for-free_3

You can now click on the icon Excel to open a new document or a document from a template, or one of your saved in your document directory OneDrive

excel-for-free_4

In this screenshot, you have a new workbook of Excel but in a browse :)

excel-for-free_5

TOOLS

There are fewer menus in the Online version compare to a stand-alone version of Excel but it doesn’t matter because 90% of commonly used features are present in Excel Online

excel-for-free_6

Tool not available

A large number of tools with Excel are not (yet) present in the Online version. But, if you create a workbook from a desktop version, these tools are all editable on the Online version. This is the case for

So if you create a PivotTable in an Excel stand-alone, you can view and edit it in your Online version but not create it.

excel-for-free_7

On the other hand, macros are completely out of Excel Online.

NEW TOOLS

Some tools are only present in the Online version. This is the case of the Survey. Now, you can easily create a survey, send if to your user and collect the result in the same workbook.

But it is especially on collaborative work that Office Excel makes sense.

Feb 19

Calculation of tenths, hundredths and thousandths with Excel

3 Thousandths

Looking at the results of speed skating in Sochi, I found that the gap between the first and second of the 1500m event was only 3 thousandths.

3_milliemes_1

Olympic medal table for the 1500m men ice skating – Sotchi 2014

Decide athletes with thousandth is extremely unusual and this give me the idea to measure the gap between them.

Term of the problem

Computing speed and time has always been my obsession (bad memories of school). However with Excel, it’s easy to calculate the difference between the two athletes, as long as you manage the specific time format.

3_milliemes_2First, we know the distance (1500m) and the time of the athelets. We can begin by creating the following table.

We see immediately the first problem, we have lost our thousandths :(

 

 

Problem with the format of tenths, hundredths, thousandths

In Excel, to add tenths, hundredths or thousandths to your Time format, you just need to add after the format of the second, the figure 0.

3_milliemes_3So, you start by selecting the two cells that contain the time and click on the little arrow to the right of the word number in the Ribbon to display the dialog box cell format.

You can display the same dialog box with the shortcut Ctrl + Shift + &

 

 

In the dialog Cell format, you select in the left menu, the Custom option in the text box, you write your own number format, ie in this case

mm:ss.000

3_milliemes_4

3_milliemes_5And instantly you see the thousandths in your spreadsheet :)

 

 

 

Calculate the number of seconds

3_milliemes_6To perform the calculation, you must convert the time contained in cells B4 and B5 in number of seconds. For this, we can help themselves from MINUTE SECOND functions and to perform this calculation. C4 we can write

=MINUTE(B4)*60+SECOND(B4)

 

 

 

But this calculation, we lose our thousandths. This is not a history format but this time because of the formula there is no formula in Excel of time less than one second. We are not locked either.

 

A unit in Excel, is always one day and never 1 hour or 1 minute.

3_milliemes_7

If you want to see what fraction of a day is 1:45:006, just change the format of the cell and switch to Standard. And the result becomes.

 

 

 

3_milliemes_8So, to calculate the number of seconds and keeping the thousandths, we will write the following formula:

=B4*24*60*60

Where

  • 24 is the number of hours in a day
  • 60 is the number of minutes in an hour
  • 60 is the number of seconds in a minute

And we obtain C4 cell the time in seconds, with thousandths

Distance

3_milliemes_9We are almost at the end of our calculation. We have already done the hardest it remains for us to determine the distance traveled in 1 second by skaters. We simply perform a division between distance and time to get the following result

=$B$1/C4

 

Wow! An average of 14.28 m/s , that’s huuuuuuuge

 

Difference between the 2 athletes

3_milliemes_11It only remains for us to subtract the two values ​​to determine the distance lost by the Dutch skater every second

=D4-D5

 

 

 

 

 

3_milliemes_12And finally multiply this result by the running time

=D6*C4

 

 

 

And we get a difference of 0.0429 m or 4.29 cm

Dec 14

XLS or XLSX file ?

With the release of Excel 2007, the extensions of Excel files (or Office in general) were changed.

Previously, it was possible to save workbooks in xls. Since Excel 2007 you can also save your workbooks in xlsx or xlsm. It is now 6 years since the extensions xlsx and xlsm were created but many people are still saving their Excel file with the xls extension.

This article will present the main reasons why it is strongly advised to convert all your workbooks to xlsx (or xlsm).

Xlsx compresses the file

If you have on your computer an xls file and you save it in xlsx, you will see that the size has been significantly reduced. This is due to the fact that the new Excel file extension is written in XML standard, hence the x at the end of the extension xlsx.

xlsx_1The XML code is lighter than the previous mode of saving and if you want to visualize the content of an xlsx file, you just have to change the extension to zip. Then, you can open this zip file and you display the XML structure of  your Excel file.

 

 

 

Each xml files can be opened. But be careful ! If you edit the xml files, you may destroy the architecture of the XML file and you will no longer be able to open your Excel file (after renaming the file zip file xlsx).

xlsx_2_en

Protection against macros

We have seen that the last x of a xlsx file indicates that the file is compressed and respects the XML standard. The M, of a xlsm file, indicates that the file contains macros.

A macro is a program written by an end-user (and not the developer of Excel) to provide automation (opening files, data comparison, data aggregation across multiple sheets, …) but also a program may cause malfunctions. In addition, a macro can run directly when you open an Excel file, without prompting the user.

With an xls file, you don’t know whether your workbook contains macros or not. Whereas with a xlsx file you are certain that there is no macro. If you do the test to write a small macro and then save your workbook with the xlsx extension, you will lose all your work without the possibility to recover your program.

Xlsx workbooks have no macro, unlike a xlsm file.

Extension of the working area.

xlsx_3In a Xls workbook, the row limit is 65,536 (216) and 256 columns (28) which corresponds to the column IV.

 

 

 

 

 

 

xlsx_4Now with xlsx workbooks (and xlsm), the limits are 1,048,576 rows (220) and 16,384 columns (214) or the column XFD.

 

 

 

 

 

 

Support Tables

The concept of Table appears with the Excel 2007 version. This new concept  improves handling of large amounts of data (Big Data) like for Business Intelligence .

Before Excel 2007, the selection of a range of cells could only be done with references. As in the following image shows you, the formula VLOOKUP reads the information in the range of cells B5:E15.

 xlsx_5_en

The problem with this method is that if your data source expands with the insertion of new lines, you must change the references used in your formulas to integrate the new data.

With the use of a Table, the range of data automatically adapts to the addition of new lines and your formulas are calls directly to named ranges.

xlsx_6_en

Only a Xlsx file can interpret the concept Table and dynamic ranges.

New color and new themes

With the xlsx files, you have a bigger range of color. With a xls file, you can only use 56 colors but with an xlsx file, you have all the combinations possible RGB (red, blue, green).

Theme_4

In an xlsx file, the colors are grouped into themes. With a new theme, you change the general color of your workbook, and the font used.

Embed a workbook in a Web page

As is the case in all the functions’ pages of this site, all the exercises proposed are not images but are real Excel workbooks where you can reproduce the examples explained in each page. This is made ​​possible because the xlsx files are based on XML structure, then it is possible to easily embedded in web pages (as is the case with the following example presented in section IF function ).

Once you see the indication Excel Web App in the status bar, that means it is a workbook embedded in your web page . But, it is not always possible to interact with the workbook, everything depends of the rights given by the author of the workbook. In the following example, you can make calculations directly into the cells, but you can not save your changes (only the owner of the workbook can do it).

To embed an Excel file in a web page, you must save your workbook in your Skydrive. The next page shows you how to perform the integration.

Conclusion

An Excel workbook saved as xlsx has many advantages over the old format xls. It is therefore recommended that you migrate all your xls files to the new format xlsx to benefit in Excel 2007, Excel 2010 and Excel 2013.

Tags:  ,  ,  , 

Jul 19

Change the language of Microsoft Office

In most of companies, Office is in English. But if you don’t feel comfortable with the functions name in English, you can easily switch the language of your Excel application to any other language. You just need to install an new Office Language pack.

Change the language

In this page, you will download the most common Language pack for all your Office applications. Once you have install one of the Language pack, you will find in the same page a tutorial video to see how easy it is to change the language.

 

Mar 10

[Survey] Which shortcut do you use the most ?

The future of Excel is Excel WebApp.

Excel WebApp is simply an Excel workbook embedded in a browser. It is with this technology that you can make all the exercises in this site. The technology is new and a lot of things must be developped. One of them are the keyboard shortcuts.

When you make the exercises of this site, you’ve seen the classic Ctrl + C and Ctrl + V work fine. But if you want to lock a reference with F4, this shortcut doesn’t work. For the moment, only these shortcuts work with Excel WebApp

This is why Microsoft makes a survey to know what is your most favorite shortcuts :)

Survey

 

Apr 10

Function SUMIFS

To complete the function COUNTIFS , the function SUMIFS allows you to add a part of your data based on some criteria.

Because the formula is based on the function COUNTIFS, only the exercises and their results are listed on this page. For the explanation of the construction of the formula, please refer to the page COUNTIFS.

Mar 05

Function COUNTIFS

Since Excel 2007, a new useful feature allows you to count the number of rows that meet multiple criteria (multiple column).

In the following document, we are able to count the number of orders for a specific client (cell K1) for a time period (cell K2 and K3)

You will find in the function COUNTIFS page how to use this function through several exercises. All examples are real Excel spreadsheets and you can make inside the cells, all the changes you want.

Feb 08

Gantt Chart

For the managers, the time management is a priority. A Gantt chart can easily present the delay of the tasks and end of the task. However, you must care of the weekends. So, to add exactly the number of workdays, you must use the WORKDAY function.

Follow this tutorial to see how to build a Gantt chart.

Feb 02

Office Recent Files

I can not resist to share with you this Windows gadget developed by John Walkenbach. This tool adds directly with your other gadgets (like the watch, the weather, …). All your Office files are now visible on your desktop and to open one of these files, you just have to click on its name.

You can download this gadget on the page of the author.

Older posts «