How to Make a Dashboard in Excel

1. Open an Excel workbook containing data you want to manipulate. Create a new worksheet, inserted at the front of the workbook, and call it 'Dashboard.'
2. Click 'File' and then 'Options.' Click 'Customize Ribbon.' Tick the checkbox next to 'Developer' and click 'OK.' This enables your form control. Forms can be a vital component in an Excel dashboard.
3. Insert form items by clicking 'Developer,' 'Insert' and selecting a form option. With forms, such as a combo box, you can 'Format Control' and choose an output cell. That cell, in the case of a combo box, will show a number representing your selected option. For a combo box displaying the days of the week, for example, the output cell (hidden) will contain a value between '1' and '7.' This number can then be used by a graph or macro to display data only for that day.
4. Create a linked cell to link to other locations. This is useful if, for example, you have a totaled set of figures on the second worksheet and want to create a graph on page one but also show those totals. Type out 'Total' and 'Subtotal,' for example, on the first page, and then click the cell to the right of 'Total.' Type an equal sign ('='), then click the second worksheet, select the 'Total' cell figure and press 'Enter' to create a link to that cell. You can change the data on that page and it will still update on the first page.
5. Use color formatting to improve the look of your dashboard. This is useful not just for block color but also for conditional formatting. For instance, select a table of data and click 'Home,' 'Conditional Formatting,' 'Color Scales' and then 'More Rules.' You can select a different color scale and edit the figures so that high figures, for example, are displayed as red and low figures as green. Use these basic techniques to start creating your dashboard.