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 Warning
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.
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.
- 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.
Yes. This long-awaited feature is finally here with Office 365 Pro Plus (May 17). This is a short article showing the steps. Detailed video coming soon. Ability to Change Pivot defaults is very useful. You no longer need to customize each Pivot manually.
Continue reading Change Pivot defaults
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
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
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.
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
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
Here is the list of articles I have written about Show Values As in Pivot table. This is a very important set of features but are mostly ignored.
Continue reading Show Values As: Knowledge Pack