Excel has some amazing report tools including charts and pivot tables. These tools are quick and easy to use, and produce some spectacular reports. However, if your data is not structured cleanly, these tools will stumble. When clients ask us to create reports on their Excel data, the first (and often, longest) task is to clean up the data so that it’s in a consistent state for reporting. Since Excel is not a database tool, it allows you to enter your data in a way that may be easy to read, but not easy for the charting/pivoting tools to interpret. Below you’ll find some suggestions for dealing with this problem.
First, consider that you may need to change how you think about your Excel sheets. When most people are entering data into Excel, they are thinking about a single report that they will produce from that data, and they enter their data in that format. However, if you set out to create a database instead of a report, you’ll end up creating a worksheet that then allows you to quickly create a variety of great-looking and useful reports. The techniques that make an Excel sheet easy on the eyes are also the techniques that cause a host of problems with the charting and pivoting tools. So, start out by entering data in a clean, organized format, and worry about generating reports later.
Next, consider the format of your data sheet. By carefully designing your data sheet, you’ll open the door to many of Excel’s best features, including charts, pivot tables, outlining, subtotaling, and more. Consider these tips:
Include headings in row 1
Your headings allow the database tools to identify the columns that you’ll want to chart or pivot. Keep your headings short but descriptive. They don’t absolutely have to go in row 1, but they must be in the row immediately above your first row of data.
Don’t use 2 (or more) rows of headings. There are lots of features that you can use to format the data in your headings – word wrap, row height, etc. Use those features and keep all headings in a single row. If you use more than one row, the tools may have a hard time understanding which rows are headings and which are data.
Don’t skip any rows
Blank rows confuse these tools. They expect your data to be contiguous, without any blank rows. Don’t skip a row after your headings.
Don’t include sub-heading rows within your data. All your headings should be on the first row.
Put totals at the bottom or in the right-most column
Don’t include subtotals on your data sheet. Subtotal rows are one of the worst factors that confuse the data tools. After you’ve got your data sheet set up, you’ll create all sorts of reports that include totals and subtotals, so leave them off for now.
It’s OK to include grand totals on your data in the bottom row or in the right-most column. When you use the Data tools, you can simply exclude these rows or columns.
Or, better yet, don’t include any totals. Again, your reports are the best place for totals.
Be repetitive
People often avoid repetitive data to enhance readability. If, for example, you have several rows of data for Product XYZ, you might include the “XYZ” label only on the first row of data. People reading your sheet will understand that all rows under that row pertain to the same product. However, the database tools won’t. So be repetitive, and repeat the product (and all other relevant information) on each line.
Here are some examples of what to do, and what not to do.
DO THIS:
NOT THIS:
Now that you've got a clean data sheet, you’re ready to create your reports. Create charts and pivot tables on separate worksheets, so that the data sheet stays neat and clean. For other types of reports, consider copying the data from your data sheet to a separate worksheet, and then using the outlining or subtotaling features to get the reports you need. You want to keep your data sheet free of these features, so that you’ve got a good base to use for additional reports.
Using an Excel sheet for both database and reporting purposes can pose a challenge. But by keeping your data sheet separate from your reports, you’ll be able to take advantage of the powerful analysis tools available in Excel. Maintaining neat and clean data will ensure that you can quickly and easily create a variety of reports to meet ever changing requirements.
North Port Solutions is here to help with your data and reporting needs. Let us help you take full advantage of the power of Excel. Call 978-660-8833 or email info@northportsoftware.com for a free estimate on your project.
About the Author
Kimberlee Martin is the owner of North Port Solutions and has 30 years of business experience. She's worked with several programming languages and database tools over the years, with her favorites being Microsoft Excel VBA, SQL Server, and Visual C#. Her passion is helping small businesses gain insights into their business with effective reporting and data management.
Contact Kim at kmartin@northportsoftware.com.
© North Port Solutions, LLC, 2017. All rights reserved.
Image Credits © North Port Solutions, LLC, 2017. All rights reserved.