Here is a quick overview of how we can analyze data more effectively using new Excel tools. You can also refer to the collection of 51 articles about data analytics for a more detailed coverage. Data Analytics: Knowledge Pack.
|This is a very common and dangerous inaccuracy.
Big operational risk!
Check all your dates imports after reading this article.
Estimated reading time 15 min
Older version of Excel could display only 52 colors. New version (2007 onwards) can display 16 million colors. Yes, that sounds like an overkill, but Excel uses this extra capability in a brilliant way. It provides us with a very easy and useful method of using color to understand information more easily. Read on to find out how…
Very common problem. Very easy solution. Deserves a separate article. This alone can save hundreds of thousands of person years of drudgery globally every day.
This article shows the problem and the solution in a concise way. Quick and Easy.
This article shows you simple and immediately usable methods of understanding whether the data you have is good or bad.
This article has intentionally been kept short and to the point so that it is practically usable as a set of best practices.
A more detailed version of this article is being written by an expert in the field, which will be also be published as a guest post soon.
If I have to teach ONE thing to the world, it would be Excel Tables.
I recently created this video for a Microsoft Webcast.
Excel Tables were introduced in Office 2007.
This video explains the importance of using Excel Tables.
Outdated formulas in Excel are a big operational risk
Tables eliminate that risk proactively.
Additional Benefits of Excel Tables
This in itself can become a series. But here is a short description of the benefits
Auto-update of formulas and references
All dependent formulas are auto-updated across sheets. If you have a linked file, the file must be open for the auto-update to work.
Automatic formatting of tabular data
If you have manual formatting applied, the Table formatting cannot override it. To override, right click on the desired format and choose Apply and Clear Formatting option.
Automatic copying of calculated column formulas
Any formula added in new columns in the table is auto-copied across the table. The formula is copied all the way to the end of the table even if the left column has empty cells.
This saves you the trouble of using time consuming DRAG or error prone DOUBLE CLICK for copying formulas.
If you don’t like this behavior then use the SmartTag to UNDO the auto-copy action.
Automatic copying of calculations when more data is added
This is very useful. If more data is added, calculated columns are auto-filled.
To take advantage of this feature, make sure that you add calculated columns at the end of the raw data. This way, when you get more raw data, you can simply paste it at the bottom of the Table.
Automatic display of headings
Headings are shown in place of regular column headings. It also offers Auto-Filter dropdown (2010 onwards). This eliminates the need for Split and Freeze Pane.
Automatic copying of formatting, conditional formatting and validations
All attributes of cells are auto-copied when you add more rows in a Table.
Ability to use Table and Column names in formulas
Ability to integrate with SharePoint and Visio
You can upload an Excel table to a SharePoint site. It automatically creates a list, creates relevant columns, uploads the data to SharePoint and finally creates a one way (SharePoint to Excel) link with the data.
In Visio you can create a visualization based upon a Visio based Pivot Table functionality.