Create an attractive data dashboard in Excel
What you need: Windows 7, 8.1 or 10; Excel 2010 onwards Time required: 20 minutes
Adashboard is a great way to visualise the data in your Excel spreadsheets, so you can track and analyse the information they contain. Dashboards can include charts, graphs and maps, all of which are updated automatically when you edit the spreadsheets they’re based on.
A dashboard can help you keep track of any project that involves combining data from multiple Excel files – from a simple household budget to your savings and pension fund, for example. We’re using the latest version in Excel 365, but the steps will be similar for older editions.
STEP 1 Open a spreadsheet file you want to include in your dashboard. Here, we’re using a very basic monthly budget sheet, with columns for incoming and outgoing funds, as well as a column showing the difference. First, you need to format your spreadsheet as a table. Click and drag to select all the cells in the sheet, then click Insert, Table 1 . In the Create Table box that appears, leave the ‘Where is the data for your table?’ field as it is 2 , and make sure there’s a tick next to ‘My table has headers’ 3 . Then click OK 4 and OK again.
STEP 2 Click the Table Design tab on the Ribbon (if it isn’t already selected), then under Table Names 1 , type in a name for your table – Budget, for example – making sure you don’t include any spaces. Next, on the Tools section of the Table Design tab, click ‘Summarize with Pivot Table’ 2 . Make sure the name of your new table appears in the Table/ Range box 3 and that the New Worksheet option is selected 4 , then click OK 5 .
STEP 3 A new worksheet will open. Double-click the tabs at the bottom of each sheet 1 and rename them so they’re easy to identify. Now we’re going to create the first chart for our dashboard. Using the Pivottable Fields panel on the right 2 , select the data you want to include in the chart. For example, we’re creating a simple monthly totals chart, so we’ve ticked the boxes next to the Month, Outgoing and Incoming columns 3 .
STEP 4 Click your Pivot Table, then the Pivottable
Analyze tab 1 and select Pivotchart. The ‘Insert Chart’ window will open. From the options on the left, select a type of chart that’s suitable for your data. We’ve selected Bar 2 , for instance. On the right at the top, you’ll see further options to customise the way your chart looks 3 , with a preview of what it will look like below. When you’re happy with your choices, click OK 4 .
STEP 5 Now you need to create more charts and choose how you want the dashboard to represent the data. Create a copy of your Pivot Table by holding down Ctrl, then clicking the tab for your Pivot Table’s worksheet 1 and dragging it to the right. Delete the chart in the new copy of your Pivot Table worksheet, then repeat steps 3 and 4 – this time selecting different data from the Pivottable Fields panel, a different chart style or both. Keep doing this until you have all the charts and graphs you need.
STEP 6 Now it’s time to assemble your graphs into a dashboard. Click the + sign 1 next to the tabs at the bottom of the screen to add a new worksheet, and name it dashboard. Next, open one of the tabs that contains a Pivot Chart, click the chart, then press Ctrl+c to copy it. Then open your dashboard tab, click an empty space in the worksheet and press Ctrl+v to paste your chart. Do this with all your charts, arranging, redesigning and resizing them as you wish. Be as creative as you like.
STEP 7 As a finishing touch, you can add extra tools such as filters and timelines to help you analyse your data. To add a ‘filter by’ function, for example, click one of your charts, then Pivotchart Analyze 1 . Now select Insert Slicer 2 . In the box that appears 3 , tick a box to select the data you want to filter by and click OK 4 . This adds a new panel to your dashboard that lets you filter the data displayed in the chart. In our case, we can now filter the chart by month 5 .