Tag Archives: Excel

Transforming Shadow Analytics

This content is relevant only if you are a CIO (or IT decision maker). Here is the video of the session I conducted at CIO Power List event on 4th May, 2018, at Conrad, Pune. Shadow Analytics has been around ever since “shadows” – also called end users – are around. Everyone knows about. Some people tried to eliminate it. Nobody succeeded.

This 30 minute video explains how to use Shadow Analytics as an opportunity to empower rather than restrict users and improve effective utilization of data.

Demos Included

The demos included in this Shadow Analytics video are:
Flash Fill, Insights, Explain the increase and Q&A.

Insights option in Insert menu of Excel

What is Shadow Analytics?

It is all kinds of data capture, clean-up, manipulation and report generation performed by end users without IT intervention.

If you generate a report from a business system (which is built or managed by IT), it is alright. But if you copy paste data from multiple such reports into Excel and then generate a new report, it becomes “Shadow Analytics”.

As you can imagine, it is difficult to eliminate it. Irrespective of how much time and effort you have spent on creating the most flexible ad-hoc reporting systems, it is impossible to provide every possible variation that users want. Therefore, Shadow Analytics has always been there and is likely to survive in the foreseeable future.

Problems associated with Shadow Analytics

Primarily two problems. It is extremely error prone and time consuming. There are lots of related problems. The root cause is that data is handled in a casual manner without regard for its recency and in a completely undocumented manner.

This can lead to wrong decisions, delayed decisions, increased operational risk and enormous wastage of precious time.


It is impossible to handle and correct the data sources and deliver data to users in a manner which is so easy that they stop doing the manual capture and clean-up altogether.

Once clean, accurate and updated data is available as input, creating reports can be done by end users in a more informed and productive manner.

Left hug Computer Right hug

Remove cell spill-over from Excel data

If a cell has no data in it, the data from the cell on the left side spills over it. This can lead to confusion while reading and understanding the data. How to remove this cell spill-over?  Very simple. Just three steps:


  1. Select the data range.
  2. Press Ctrl G or F5 to open the Go To dialog.
  3. Click the Special button.
  4. Choose the Blanks option.
  5. Now, all the empty cells in the highlighted area will be selected.
  6. Just type a spacebar. This will go automatically into the active cell.
  7. DO NOT press ENTER. DO NOT disturb the selection.
  8. Press CTRL ENTER.
  9. That’s it. Now all the spill-over items will be truncated.


Excel Data Model : Simplifying usage

Excel Data Model is a database that is built-in to Excel. It has been around since 2010. Using it increases the capacity of Excel to handle millions of rows, it reduces file size significantly and eliminates VLOOKUP for code to description mapping. These new options in Excel 2016 and above simplify the usage of Excel Data Model and improve performance for large data operations. Go to File – Options – Data tab.

Excel Data Model usage options

Options in section 1 and section 2 are already covered in my previous blog articles. Section 3 helps us incorporate Data Model more easily into day-to-day Excel data management activities.

Continue reading Excel Data Model : Simplifying usage

Pivot performance improvement options

If you use Office 365 Pro Plus and Excel 2016, you should see a new tab under File – Options. The Data tab. This has many useful features which help improve Pivot performance. In addition, it also helps you with incorporating Data Models more easily into your Excel data management process.

If you do not see the Data tab in File – Options, you are not using the the right version of Excel. Escalate to your IT team to set it right. 

Data tab for Pivot performance adjustment

Here is a set of Practical usage guidelines for some of these new options.

Continue reading Pivot performance improvement options

IFS function: Goodbye nested IFs

With Excel 2016 and Office 365 Pro plus, a less confusing and powerful function was introduced – IFS function. It eliminates the confusion generated by multiple nested IFs for complex calculations. In any case, if you are using multiple nested IF statements, check whether VLOOKUP can solve the issue or use Pivot Table grouping.

Nested IFs look like this:


The same formula using IFS function is much simpler to type and understand :


IFS function has just ONE set of brackets, irrespective of the number of conditions used. It also offers the last argument for default return value – in case all other conditions did not produce any output.  Try using it.

The function will return an error if used in versions before Excel 2016. Be careful.

Excel Function Warning

When you type  something in a cell after an = sign in Excel, a list of functions is shown.  Functions have an icon next to them. For some functions, the icon shows a Yellow triangle with an exclamation mark. That is the Excel Function Warning I am talking about.

No warning No Excel Function Warning                         Warning Excel Function Warning for STDEV function

What exactly is the Excel Function Warning?

It basically means: “Do not use this function. We have a new and more accurate function available now”.  If you use only newer versions of Excel, you should use the newer functions.

Standard Deviation

Here is an example. The older STDEV function now has two separate functions STDEV.S and STDEV.P – S means sample and P means population. The mathematical formula used behind the scenes by these functions is different. As mathematics advances further, the logic used also undergoes improvement. The newer functions utilize the latest algorithms to increase accuracy of your results.

Best practice

  • Notice if any of the your formulas have one or more Excel Function Warning icon.
  • Go to the help file of that function.
  • The newer, better or more appropriate function will be listed there.
  • Learn the new function(s). Understand what has changed and why.
  • Change these across your files to ensure more accurate results.
  • Use the new function when you create new workbooks.