Finance and Accounting departments use Excel extensively. Advanced Excel is still the most sought after topic for training programs globally. We conducted this live event on 27th Feb 2021. This blog post is a summary and collateral for the event video. You will find short, crisp and immediately usable best practices to improve efficiency. Operational efficiency as well as Analytical Efficiency. In addition, I have also included best practices and recommendations for the IT team.
Estimated reading time: 8 minutes
Watch the video of the entire session : 1 hour 24 minutes
Eliminate Operational Inefficiency
Learn how to check if your process is inefficient
While working, keep checking if one or more of the following is true. If yes, your process is inefficient.
It also means there must be a better, smarter, faster way. Find it. Then standardize it.
- Is it repetitive?
- Am I helping Excel?
- Hands busy but brain idle!
The 15-minute rule
If any activity of data manipulation takes more than 15 minutes of manual work – it is inefficient.
Effort and data size should not be proportional
Even if data size increases, your effort should NOT increase. That means the process is efficient.
Use 64-bit version of Office
If you handle lot of data and have slow files, ask IT to install the 64 bit version of Office. Also give more RAM – 8 to 16 GB.
Use Dark mode to reduce eyestrain
File – Options – General – Theme – Dark Gray
Use the Good Data Checklist to find cleanup tasks
Apply to each column of raw data.
Make sure you have Power Query
How to install Power Query? Excel 2016 onwards – It is already there – Data tab – Get and Transform.
For Older versions download the Power Query Add-in
Use Power Query (or Get and Transform) for Data Cleanup
Power Query lets you decide which columns you want, do cleanup on a small sample and then import. Next time, just refresh. That is how Power Query completely changes the cleanup effort and accuracy. Data Imported from Power Query is automatically converted to a table.
For raw (input) data, always use Tables
Even if you have typed / imported data into Excel, convert it to a Table.
Tables do not increase file size. Automatically update dependent formulas and more.
Learn the 12 benefits of using Tables.
Review all existing data cleanup and improve it
Even if the existing method is working, there is a good chance that Power Query will have a better, smarter, and faster way of doing it. Lot of macros written for data juggling become unnecessary due to the flexible feature set of Power Query.
Power Query Import Sources
Many data sources are supported (free of cost) in Power Query. Go through the entire list. It is possible that what you are exporting manually as a CSV or report can directly connect to Power Query. This is an instant and significant benefit.
Large data – import to Data Model
Power Query allows you to decide where to store the data. Excel sheet storage has row limit. Choose Data Model instead. That way, you do not have an upper limit. File size will reduce and performance will improve – on the same PC.
Use Relationships instead of VLOOKUP
For converting codes to descriptions, create a relationship between the two tables in the data model. Also, in some cases where VLOOKUP cannot be eliminated, use XLOOKUP.
Import and Clean-up
Use Power Query. Put one-time effort to automate the process. Next time onwards, when data grows, it just requires a refresh. Immediate + sustained benefit.
Excel Green Marks (error checking)
Handle each error one-by-one. Trust Excel data only when no pending errors. As soon as you handle all errors, convert raw data to Table. Now onwards, Table will manage automatic update of dependents. Use Formula – Error Checking to find errors. Detailed articles here.
Excel error – memory problem
Read this article for details.
Cross-check all links
Check links every time you open a file – Data – Edit Links – Select all – Check Status.
Be very suspicious of links to local drive files.
Re-establish links manually whenever you move any file to a different place / cloud (OneDrive, Teams, SharePoint, etc.)
Use the Inquire Add-in
Available from Excel 2013 onwards. Useful for spreadsheet analysis, comparing files, documenting links and removing excessive formatting.
Manage Analytical Inefficiency
Use newer functions
Read this article about new Excel functions. This includes a detailed video, cheat sheet and sample file.
Watch these videos by Vijay for Dynamic Dropdown, Filter function and more.
Design / capture data in a flexible manner
Do not hard code ranges. Use Tables. Use Dynamic array formulas.
Use Excel Ideas for every piece of data you have
It just requires one click. But it may give you some new and useful information from the same old data – which you have missed all along. Make it a standard operating procedure. This feature is available in the Microsoft 365 version only.
Use 3D Map and Power BI Maps
Always view your location related data on a map. Even if the finance team is usually not concerned with maps, it will give you useful insights which will help you manage your business / work better. It may also give you more ideas for improvement. Insert – 3D map is available from Excel 2013 onwards.
Learn how to create a 3D map. More detailed articles here.
Keep Power BI desktop handy for every meeting
Usually for meetings / reviews we take PowerPoint presentations which have data / charts copy pasted from Excel. That is fine. In addition, import the same raw data into Power BI desktop. Carry it with you. Even if you have no visuals created, you can use Q&A to answer queries and analyze data DURING the meeting. This will make decision making faster and smarter.
IT Best Practices
Install 64-bit version
It does not cost extra money. Uninstall the 32-bit version and install 64-bit version. Only the 64-bit version can use more than 4 GB of memory. With 32-bit Excel, any memory more than 4 GB is a total waste.
Macro compatibility is no excuse for stopping upgrade
You can install the Office Pro Plus (the cloud version – now called Microsoft 365 version) ALONG WITH any older version (including 2003). The old add-ins, apps, macros which require older version will continue to run. For rest of the regular work, use the latest version. NO time wasted in testing app compatibility, macro discovery and everyone benefits.
Only allow signed macros
All this malware trouble is because we have never used the correct Macro setting – Allow only Signed Macros. Signed using Authenticode. You buy the certificate for the organization and sign valid macros. All other macros will stop working – which also means, all the Office file attachment viruses cannot run (even if you do not have any other anti-virus running).
Before you ask, you want to save the cost of buying that certificate. No problem. Teach every user to sign their own macros using SELFSIGN.EXE which is available in Office installation folder. Of course, the macros signed like this will work only on that PC and only in that login. But you saved money!
Add-ins
Make sure these add-ins are available for all users. Power Map (3D map), Power Query (2013 or before. Built-in to Office 2016 and above), Inquire.
DO NOT enable Power View. Microsoft has discontinued it.
Invest in the cloud version
Do not try to save money by buying the perpetual license. Office 365 (now called Microsoft 365) has immense value. Typically, it costs between 600 to 2000 INR (10 to 22 USD) per user per month depending upon whether it is Home, Business or Enterprise license. This includes twenty-two different products – Excel is just one of them.
How to justify the ROI of Office purchase
The cloud version has many exclusive features which will never be available on the desktop only version. These include Excel Ideas (automatic reports), Q&A, PowerPoint Design Ideas, Word Editor, Stock Images and Videos, and many more things.
Even if you just follow few of the Excel best practices from this document and calculate the time users are saving daily, you can recover the cost of the license in just 2 or 3 days!
Latest version – refresh frequency – Monthly
Even if you have Microsoft 365 version, the default frequency of update is 6 months. Change it to a monthly frequency. It improves security as well as productivity. Microsoft adds new features, performance improvements and bug fixes every week.
You can use Power BI for Free
No. I am not promoting piracy. Read my article How to use Power BI for Free.
Proactively give Power BI Desktop to everyone
Power BI is by users for users. Give it to them, educate them and leave them alone. This will improve data cleanup and replace Excel files floating around. It will also teach better visualization and more in-depth analytics to all users. This will later help you justify the cost of Power BI license for the creators of reports. License is necessary for Sharing the report with others. For viewing reports shared by others, the free license is adequate.
Stop giving data as CSVs. Deliver certified datasets.
Yes, this requires Pro version of Power BI for the people who create and publish the certified datasets. Users can continue to use Excel to consume the centrally managed raw data.
GOOD data checklist
Check input data against these 11 rules.
| 1 | Each column must have a heading (not data) | Heading cannot contain data. For example, Jan, Feb are NOT headings |
| 2 | No blank headings | Excel table automatically inserts generic column headings |
| 3 | No duplicate headings | Excel table automatically renames duplicate headings |
| 4 | No formulas in headings | Why? Because in Tables, heading name can be used in calculated column formulas. |
| 5 | No merged cells | This is INPUT data. Why do we need merged cells here? Do all that in the Output (report) |
| 6 | No grand or sub-totals | Same as above. |
| 7 | NO formatting instead of data | Why? Because analysis cannot be done based on formatting. |
| 8 | One column, One Meaning | Be very careful. Most common problem. More columns mean better analysis. |
| 9 | One column, one data type | Check by looking at alignment. Very important for dates. |
| 10 | Data grows vertically (not horizontally) | More data should be appended at the bottom of existing data. |
| 11 | Related Data must be kept in a single sheet (or data model) | If Good data comes from separate locations, append them to one table. Add extra column for the location.DO NOT keep them in separate sheets or files. Why? Because you cannot analyze sheet names or file. You can only data that is in a column. |
Resources
| Web Site | YouTube | |
| Dr Nitin Paranjape | Efficiency365 Excel articles | Efficiency365 Channel |
| Vijay Agarwal | Champ Excel | Champ Excel Channel |
If you have any queries, do post them as comments. We will be happy to help you.
***
One Response
Collaborative teaching mode! Awesome content Doc.