Excel works very well for dashboard reporting. But there are a wide variety of techniques you can use, and they all can’t be explained in one short article. Even so,
Excel works very well for dashboard reporting. But there are a wide variety of techniques you can use, and they all can’t be explained in one short article. Even so, here are some tips and techniques to get you headed in the right direction.
Make use of Small Charts for Dashboard Reporting
Compare the charts you see in typical management reports to the charts you see in business magazines. In management reports, charts are frequently the size of a post card; in magazines, charts are frequently the size of a postage stamp. By making your charts small you typically make them more useful. Readers can view them at a glance, and easily can compare one chart with another. If we had used big charts, as many companies use for management reporting, we would have needed at least six pages to contain the same information presented on one dashboard page.
Use a Reporting Template
Your first one-page dashboard reporting probably will take several days to create. Don’t waste that investment. Set the report up as a reporting template.
By template, we do not mean that you should save your workbook as an Excel template file…an XLT file. Instead, you should design your report workbook so you can update it easily from month to month, department to department, and so on.
The key step is to design your workbook so that it contains no data, only formulas and key values…values including the month to report, the department, the division, and so on.
The easiest way to do this is to use an Excel-friendly OLAP product. This would allow Excel formulas to return specific values from cubes of data on your computer or a server.
With your formulas set up properly, you’ll find it very easy to shift your report from month to month or division to division. Just enter the month or division in the appropriate cells, and then recalculate.
As a marketer, you might always make use of Excel to analyze data, pull together reports and project future activity. Salespeople use it much the same way. You need to provide daily, monthly, and quarterly reports for yourself, your team—and often for your superiors.
An often overlooked tool in Excel is the Pivot Table. we know, it sounds boring, but as the end of Q4 inches ever closer—and 2015 comes up in your rear-view mirror, today is an excellent day to learn about this great assist.
A Pivot Table allows you to summarize data as averages, sums, or counts in Excel from data that is stored in another Spreadsheet, or table. It is great for quickly building reports because you can sort and visualize the data quickly.
For example, I have put together a spreadsheet, which you can copy, and paste into Excel, or use in Google Docs if you would prefer (just click File > Make a Copy). The spreadsheet contains data with a mock company’s customer purchase information. For this example, we want to sort the data to see how much revenue we’ve made from each company. Since companies purchase at different dates, a pivot table will help us to consolidate this data to allow us to see total buys per company, as well as to compare purchases across companies, for quick analysis.
An Example Is Worth a Million Cells
First, let’s get the basics out of the way. The Pivot table allows you to take a table with a lot of data in it, and rearrange the table so that you only look at what’s important to you. Pivot table is a great way to manage the data from your reports. Learning how to use pivot tables will speed up the rate at which you can product sales reports, and at which you can analyze and close more deals faster.
Let’s admit that as salespeople, the sales data we collect isn’t always perfect. But in trying to make sense of all the pieces of info we gather, Excel’s Text-to-column function is a big help. It helps you sort and categorize data in a way that helps you make sense of the fragmented pieces of seemingly random tidbits you jot down—and fill in some of the gaps.
For example, let’s say a form on your website collects a prospective client’s email, name, phone number, but not their company’s name. Text to column can help you to use the person’s email address to separate out their domain and hopefully lead you to figure out what company they work for. Once you’ve tracked this information down, you can categorize and sort your users to see how many of them come from a specific company. This can help you gauge interest that a company has, among other things with the available sales data.
It might sound complex, but it’s simple to get started with text-to-column. Essentially it’s a process of elimination, since you’re separating out contents of a cell by defining a “delimiter.” If you’re trying to separate out the domain at the end of an email address, the delimiter would be “@”. You can define it as any character of course.
Depending on your company/product, you can assume that in most cases the domains in the Company column are the companies that people work for. You will of course find people using email addresses like @yahoo.com, @hotmail.com, @gmail.com and @aol.com, in those cases, try putting the email address into a Google search and that may yield a LinkedIn profile which will likely show you where they’re working.
Back to Excel, once you’ve generated your new data set from text-to-column, you can also create a pivot table and add the “Company” to “Row Labels” and “Email” to the “Values” table. This will show you immediately the number of sign ups you have gotten from each domain, which will make you more informed as you approach them to close the deal.