Category Archives: Excel

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.

image

Consolidate Cross-Tab Data in minutes

Cross-tab data is where you have row headings as well as column headings. Consolidating (combining) multiple blocks of such data is a laborious process. Usually the rows and columns do not match across data blocks. You need to do a lot of copy-paste to bring them in the correct order before you can add a formula. Using a 20 year old unknown but powerful feature of Excel you can Consolidate Cross-Tab Data in minutes. You just select the data blocks and let Excel do the dirty work of matching row and column names.

Additional Resources : Consolidate Cross-Tab Data

I have already covered this topic as a step-by-step article.  Now I have created a video (9 min). You can download the sample file used in the video and try it yourself.

Continue reading Consolidate Cross-Tab Data in minutes

Pivot Report Sharing

Here is a fast paced, 8 minute video which will revolutionize Pivot Report Sharing. Using OneDrive or SharePoint, control the visibility and provide full interactivity. Reports render instantly even with large files and low bandwidth.

Pivot Report Sharing articles

New, Improved way of delivering Excel reports: Part 1
New, Improved way of delivering Excel reports: Part 2
New, Improved way of publishing Excel reports: Part 3

Excel risk Mitigation

This article is for CFOs, CEOs and other members of Senior Leadership Team. It requires just 10 minutes to read. But it will reduce your personal as well as organizational Operational Risk significantly.  Notice the green mark  below.
This is the biggest Excel risk.

Excel Risk - Demo

If you see even a single green mark, you should send the file back.
Ask the sender to handle all the green marks and send the revised file to you.
Of course, they may not know what you are talking about.
Ask them to read these 5 articles: Knowledge Pack: Green Marks in Excel

The rule is, Excel sheet with Green Marks should not be sent to anybody. It is the sender’s responsibility to remove all errors – not the recipient’s.

Continue reading Excel risk Mitigation

Pivot Tables and File Size

This is a sneak preview of my upcoming Udemy course on Pivot Tables. This video explains the  relationship between Pivot Tables and File Size. Watch this 6 minutes video and learn from it.

Apply this knowledge to your existing pivots – especially those with large file sizes and see the difference. Make sure you try it out on a copy. Never learn using Live files.

Continue reading Pivot Tables and File Size