Data Analysis and Visualisation Tools
This book covers some really exciting ways to work with Excel. It’s for anyone who wants to acquire a solid foundation of how to use charts and pivots, with suggestions on how to build on that knowledge. There are many ways to analyse data; the focus of this book is to show you how to use pre-existing Excel functionality to do quick dynamic analysis and build on that in order to present results with clarity and graphic succinctness.
We start with the classic Excel chart; it’s so easy to create a chart in Excel, but a little more thought needs to go into creating a good Excel chart. We begin by creating a basic chart from scratch; next we look at what charts to use when, then formatting best practice, followed up with how to add trend lines, target lines and a secondary axis. We finish this section by looking at how to create dynamic charts and I’ll go through an example of how to create a simple Gantt chart to give you a taste of how you can creatively use this functionality.
Next there is a short section to cover sparkline and heatmap functionality; which can provide some great in-cell visualisation.
In the second main section in this book I show you how to quickly and easily create a simple pivot table. Pivot tables are very powerful and quite frankly not enough people use them. It’s amazing how much time you could save on routine data analysis and reporting with a few clicks of a pivot table. In this book we look at the basic pivot structure and how your data needs to be formatted in order to get the most out of it. Then we look at how to add layers and filters onto your pivot table to make it even more useful. Next we look at the pivot table format and some bonus functionality that a lot of people don’t know about. Then we look at the power of pivot charts; bringing our chart and pivot knowledge together in a powerful duet. We’ll end this section by looking at the fairly recent introduction of slicers and how they can make your pivot table more user-friendly and interactive.
Dashboards and Infographics
Finally we’ll also touch on dashboards and infographics to help give you some ideas on report presentation and get the creative juices flowing before we finish.