99.1% Of all customers recommend us, we're so confident about our results we publish all reviews and statsView Live Stats View Reviews
Microsoft Excel as a Reporting Tool
Thu 19th February 2009
Microsoft Excel combines reporting functionality such as charting and summarizing tools along with a huge array of formatting capabilities. You can analyse data and present it in a well formatted, professional way, even managing large amounts of data with navigation. Yet much of this simple, user friendly functionality goes unused. For a small amount of effort and time, the way data is presented can be completely transformed. Let's examine a few simple methods.
Pivot tables and charts are of course the data analyst's dream. Pivot tables can take large amounts of data and summarise it. They are flexible in the way they present it and what information they are delivering. As an example, let's take a sales spreadsheet where all the sales are given per office, per sales person. This is the data a manager would want to see, however managers are busy people and they do not want to spend hours wading through the details. They want efficient summaries at a glance, preferably in pretty colours. Give them this along with the data in detail and your manager is no longer rolling his/her eyes at the data you've emailed and is instead inviting you out for lunch.
In order to achieve this, add some pivot tables or charts which will give a simple breakdown of the total sales value or number of sales by office or sales person. It's not static either as the user can easily select the data they want to include or exclude from the table with the automatically populated drop down list. A pivot pie chart can show a manager which sales office is bringing in the most cash. Both objects can be extensively formatted, even using company branded colours and logos for extra visual brownie points.
Once you've got all your data organized on different tabs with appropriate charts, navigation can be used so the user can easily find their way around your workbook. Nobody likes getting an email on a Friday afternoon to see a huge 14 tabs of data ready for them to plough through at their leisure. Instead, realize the difference it makes when they open the same workbook to be presented with a menu page, branded in company colours and sporting the company logo with a selection of buttons allowing them to easily pick what data they want to see.
A simple macro can be recorded to take you from the menu tab to the other tabs. Each macro can be assigned to a separate button on the menu page. A manager now opens the workbook and chooses the data he/she wants to see first. On each tab they go to is a button which directs them back to the menu page. This is about the user never having to search for the data they want regardless of how well you named your tabs. Now you've been out for a second lunch in which you are discussing future promotion prospects and are well on your way to becoming the managers pet.
Once you have added the navigation and summarization, it can't hurt to take a look at the detailed data to see if it can be presented in a way that helps people identify what they are looking for more efficiently. For example, you can format an entire column with condition formatting from the format menu. You could change the font colour of any sales value greater then
Original article appears here:
London's widest choice in
dates, venues, and prices
On-site / Closed company:
Alliance Boots Management Services Limited
Alloga Commercial Assistant
PowerPoint Intermediate Advanced
Our trainer was very good and clearly knew all there was to know about the subject; overall I was very impressed and extremely pleased with the training we received.
Proposals Researcher And Coordinator
I enjoyed the course immensely, which was in itself surprising.
The course was very thorough and interesting and fast paced due to the amount of content, although levels of participants were quite wide ranging. Therefore some of the more advanced aspects were a little challenging for me personally. It may have been more useful for me to have attended a beginners course although I learned some uses for Excel which I may be able to apply going forward.