During the military, I didn’t use any computer programs that would be relevant to my future career. College was my first real introduction to programs that would be integral to life in the corporate arena. With a business major, I had a few courses that taught basic software knowledge in Microsoft products like Excel, PowerPoint, Visio and Access. There were two major issues. 1. I didn’t take these tools seriously enough (the majority of the blame falls on me). 2. I don’t believe our professors put enough emphasis on how important these tools were for our career.
For now, let’s look at Excel. Know enough to be dangerous. If you get assigned to project that requires more detailed knowledge, you can learn on the job (Google, YouTube, nearby experts, etc.) In fact, I urge you to not fill your head with details of every function and capability these programs offer. Use your time more efficiently, learn enough to be dangerous and do deep dives as needed.
As an analyst, I would often run into situations where I needed to utilize functions, graphs, or other capabilities of Excel that I wasn’t familiar with. I would go straight to google and ask the question. So far, I have been able to find a step-by-step “how to” guide or YouTube video for every unique scenario I ask about. The best part is (in addition to learning new things), people become impressed and start thinking you’re some kind of Excel guru (ha!). Here are some tips to simplify Excel…
Have a goal for your spreadsheet
Every document serves a purpose. Know what you are trying to accomplish every time you build a new spreadsheet. Corporations often have hundreds of spreadsheets floating around. Many of those spreadsheets are either way too complicated or lack updated information. Unless you are using Excel for quick ad-hoc analysis that only you will see, I suggest you put some thought into it.
What analysis do you need to perform? What is the goal of bringing this data together? Who else will being using or seeing it? Do we really need another spreadsheet out there (might be another one already out there that just needs an update)? Answer those questions first.
Mind your audience
Make the assumption that the people opening your document are either very busy or have limited skills/experience using Excel. Make it easy for the user to see the analysis and navigate the document if needed. Use a cover page on the first tab with a brief description of the document and its purpose.
Bring key results or metrics from the data to the front tab/s. The point is, don’t let a giant spreadsheet with all the raw data be the first thing they see upon opening the document, it is overwhelming. Present the data in either table or graph format, whichever highlights your analysis and makes it obvious for the user.
Less is more
When you start gathering data in a spreadsheet, it is easy to keep adding more columns. We keep thinking of more things we would love to analyze and track over time. This can be somewhat of a trap. Try to stay focused on the goal of the spreadsheet and resist the temptation to make it more complex. More columns mean more data, and more data means more time updating and potential accuracy concerns.
More data also can be more overwhelming and confusing for the user. Keeping your spreadsheets focused on the goal is important. Each column, in general, should be NEEDED to support the goal of the spreadsheet. If you run across a column that is just nice to have, you can likely delete it. Every time you or someone else thinks of adding more information, give it the same “nice to have” vs “need to have” test.
If they need to navigate and use the raw data, make it as easy as possible. If you have a spreadsheet with multiple columns and rows, do your best to organize it in a way the user would likely need it. Prioritize the important columns and either place them in the beginning or highlight with another color (try to avoid using more than 1 or 2 colors, nobody likes a confusing rainbow spreadsheet).
Sort the data so it looks uniform and groups information in a way that makes sense. The point is, it should look clean and organized. Finally, turn the filter on each column and allow them to easily find what they need (assume they don’t know how to turn on the filter).
Know your options
Assuming you are not just sending out the raw data, you will likely need to show the analysis/major findings in a way that people understand. Learn how to create pivot tables, graphs and tables that are simple and easy to read. Don’t try to get fancy. No need to add colors or cool designs that don’t add value. Only include the data that is absolutely needed to support your analysis.
Once again, weigh the “nice to have” vs. “need to have” when building tables and graphs. Label things appropriately so every number is understood. Finally, make it all look neat and organized. Take pride in your work. Bring the highlighted analysis to the front of the document (cover page or next tab). The raw data should be the last tab.
Good luck and remember, keep it simple stupid.