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:
- Select the data range.
- Press Ctrl G or F5 to open the Go To dialog.
- Click the Special button.
- Choose the Blanks option.
- Now, all the empty cells in the highlighted area will be selected.
- Just type a spacebar. This will go automatically into the active cell.
- DO NOT press ENTER. DO NOT disturb the selection.
- Press CTRL ENTER.
- That’s it. Now all the spill-over items will be truncated.
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.
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
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.
Here is a set of Practical usage guidelines for some of these new options.
Continue reading Pivot performance improvement options
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.
I had both these problems. I am using Office Insider – Fast track for Office Pro Plus. I tried all kinds of things but nothing worked. Full repair, complete uninstall and reinstall, reapply Windows patches, reinstalled .net framework, escalated to MS. No use.
The solution was very simple. I went to Excel – Customize Quick Access Toolbar. At the bottom right corner I chose – Reset – Reset All Customizations. That’s it. Both problems solved.
I myself do not know why it worked. But it did. If you have the same issues, try this one.
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