Do you have executives who want a “dashboard” to monitor key business metrics? Do you or your team members spend lots of time doing one off data analysis exercises to answer management questions? Do you work in a place where fancy web based business intelligence tools are out of budget, out of scope, or stuck in a never ending implementation rollout? You already have a tool to help you out -- MS Excel.
Excel is the most commonly used business intelligence tool. It is used to analyze data and to create charts, to format tables and graphs that summarize conclusions from that data. In a professional environment, basic skills are nearly universal. And Excel can handle reasonably large sets of data when it is set up properly. Even someone who is not all that database or statistically savvy can answer key business questions and share their justifications without a sophisticated and expensive Business Intelligence toolset. Plus some of us actually enjoy using Excel, even if we don’t admit it at parties.
Here are my tips for creating Excel workbooks that act like a Business Intelligence Tool. Results will update as you expand or refresh the dataset and it can be used by others to analyze similar business decisions.
- Remember the goal. Be clear about the business questions you are answering with this analysis. What is the purpose? Yes, Excel is easy to use and you can probably analyze the data a myriad of ways, but you will be more focused and create an easier to use tool if you are deliberate up front about what story you want the data to tell. If the BI tool were so sophisticated that someone had to program your report, then you would be very clear about the requirements. Save yourself the time and energy of reworking it the night before the presentation. Clarify the requirements for yourself up front. Then extract your dataset from its source.
- Name your datasets. If you select the area that has your dataset in it, and then insert a name for it, like "WW Order Data". Then when you want to analyze the next quarter's data the same way, you just need to name that dataset the same way. You don't have to manage the changes to absolute and relative cell addressing that occur when you add and delete sections of data. Your formulas and pivot tables will work. (Plus you can also point to external data sources & refresh constantly - but that is definitely an advanced move.)
- Dive into pivot tables. If you plan to refresh your analysis as new data comes in, or plan to share it with others, then use pivot tables to create the data tables. If you have never used pivot tables, take a deep breath, then use the Excel tutorials available on the Microsoft.com website. After 20 minutes of practice, you'll be slicing & dicing your data like a pro. I use a core set of pivot tables as the foundation that are pointed to by all the tables, charts, and graphs. I update the data, the tables, charts & graphs are updated too. Set them up correctly to begin with, using your named dataset, and then test it. Start with a subset of data. Test that you have any references that need adjustment by deleting a chunk of rows in the middle of your dataset to see if the pivot table still works on refresh. Leave blank columns next to and below the pivot table so if the pivot table is larger for a different data set, the pivot table won't overwrite other things on your sheet.
- Clean up your data. Clean data makes for much more success during analysis. So check that there aren't a lot of similar names for the same company or same product. Create an extra column for your "short company name" to use in analysis if necessary. Leading or trailing spaces can create odd answers. Names that start with a number (like 3Com) are problematic so the "short name" column lets me rename it "TCom" while I am analyzing and then replace it with the "original name" in the final report. I have had mixed success with using the apostrophe (ex: '3Com) method of making sure that text with numbers in it is read properly. I use AutoFilters and then Sort & filter in a variety of ways to find discrepancies and clean up the data using find & replace. Data issues will show up in your pivot tables too so look at those summary tables to see if the totals and categories are correct. Do you need to assign some blanks to an "other" or "unknown" category in order to have things add up 100%? Or are some things double counted? Combing through the data in a variety of ways to clean it up will improve credibility for the results later when you are using the data in front of a colleague or when other people use it to slice & dice it.
- Validate hierarchical data. If you have data that is naturally hierarchical (ie: its pieces add up to something), then check to be sure it really is adding up, or make some modifications so that it can be. For example, if you are analyzing some data that includes slices by sales team and by geography, think about how these might overlap or add up. Do your major account teams span regions? Should their customers “count again” in the individual geography numbers? If so, have you captured the customers’ geographic location separate from its sales team designation so that you can roll up BMW’s plant in Boeblingen into Germany and into Europe, but also categorize it as part of the Automotive Major accounts team. A table that shows European sales by country sales team could include BMW in Germany, but a Worldwide table by sales team that include major accounts would double count BMW. Do you plan to do footnotes to explain why your WW number seems to add up to 115%? Or will you create two different datasets - one for regional analysis and one for WW analysis so that BMW is only counted once and the components of WW sales ad up to 100%? You may not need to "normalize" the entire database (as some database experts might declare) but simple decisions about how to handle sums can improve your credibility when you present your analysis.
- Managing Memory No offense intended, but MS Excel can become a bit of a memory hog with large datasets and then things slow down. You can reduce it by creating new pivot tables based on existing ones when possible. It is easier to remember to do this if you give meaningful names to your core pivot tables rather than the default PivotTable27. Pivot tables based on the same core pivot table don’t use nearly as much memory. Exit other applications that you aren’t using right now. The fifteen presentations that you downloaded from email this morning could be exacerbating your memory problem too. And if things become increasingly sluggish, shut down and restart. The memory leaks are cleared up on a reboot and you’ll spend less time watching the ball spin while you wait for columns to sort.
- Color check your charts & graphs - Excel has some great options for formatting these. I find them easier to read when I take the time to create a common style for the charts & graphs and use them consistently within the same report. Then cross-check what happens when you cut & paste that into Word or Powerpoint (often the final destination for your intense analysis is a single slide). Do you need to modify the chart style in your PPT so that the color scheme is readable and professional? Sometimes when I copy a chart from excel into PPT, I get color changes that result in a Pinkalicious combo of pinks, reds and purples rather than the cool & sophisticated palette I had expected. Once you get the chart styles set up properly, then you can modify data or the way you analyze the data over & over and the chart will still complement your corporate template.
- Use hide sheets & passwords. When you send out your spreadsheet to other users, most of them will never bother to unhide those sheets. That keeps the focused on the pages you want them to use. Plus they won't modify (and potentially break) your underlying pivot tables or dataset. And if you have a more sophisticated user, they will find it themselves. Even when you are sending it around internally, it never hurts to add another layer of protection. Turn on the password if you have sensitive data that might inadvertently go out on laptops or email.
- Conditional formatting: Creating a red, yellow, green shading for projects on track or in trouble? Use conditional formatting to describe the rules that determine what color to shade the cells. Then you can stop re-formatting for every update meeting.
- Leave a trail. Keep notes for yourself so that it really is re-useable next month or next quarter when you refresh the analysis. Create a sheet in the workbook just for yourself to track what needs to be done to update data next time. Or what breaks & has to be fixed manually when you refresh your pivot tables. My data cleanup steps are key. I even make notes about which way I cut & paste the data into the final deliverables so that next time I don’t have to play around to get the size right.
May you find all sorts of new insights as you mine your information about your customers, product quality, survey results, lead generation programs or order history.
Final advice - resist the urge to talk about your rekindled love of Excel at the holiday party.
If you have suggestions, corrections or questions, please write me a note in the comments. Or share your inputs on this topic with a broader audience at focus.com.