We all know there is a right way and a wrong way to complete a task. We’ll be talking about doing something the right way—but faster. (If you wanted to learn about doing something wrong slowly, stay tuned for our new blog series, Office Sacks, due to come out April 1, 2014.)
Balancing your organization’s budget or analyzing revenue is no joke, which is why accountants and bookkeepers put in extra hours during month’s end to compile the reports. These reports are very useful in researching and discovering large variances. Here are some useful Excel tips that will help you summarize your findings.
PivotTable
PivotTables are interactive tables that can help you summarize a large amount of data with various aspects (an expense report containing dates, vendors, expenses, etc.). This allows you to analyze the data to make comparisons or detect patterns and relationships in various aspects of the data.
Yes. Our expense report is mostly caffeine.
To create the PivotTable:
- Click the Insert tab
- Select PivotTable to bring up the Create PivotTable dialogue box. You can select the data on the existing worksheet, or even use data from another workbook, or Excel file.
After creating the PivotTable, you can decide which information to show to analyze the data:
With a PivotTable on the coffee expense, we can answer:
- How much was spent
- Overall.
- Per month.
- Per day (useful if I expensed multiple coffee runs).
- Per vendor.
- Which vendor
- Was visited most often overall.
- Was the most popular by month.
- We spent the most on.
You can make a lot of conclusions and summaries from your data with just a few clicks!
Group
You can also package your data and findings into easily digestible amounts rather than overloading others with a workbook packed with worksheets loaded with cells upon cells of numbers. You do this by grouping rows (or columns) together, which can then be collapsed to hide extraneous data.
Preparing for April Fools took a toll on us in March.
While our coffee expense report isn’t too cluttered, imagine if we were a warehouse that had to keep track of the movement of each product unit. The report would be huge. But executives don’t want huge reports—they want an overview.
To group information,
- Highlight consecutive rows (or columns) of similar level data.
- Click the Data tab.
- Click the Group button.
This is how the final result will look for our coffee data:
The CEO wants to see a high-level Q1 total:
But the CFO wants to see the monthly breakdown:
And finally, your manager wants to see how many coffee runs were needed in March to pull off the best April Fools prank in company history.
These two tips will go a long way in making your reports easier to analyze so that you can come to conclusions that are easier to understand.
