1. Setting up your data
Now that you understand how to use a formula of reference to extract data, and how to chart a basic column chart with raw data, it's time to look at how to do this more efficiently.
To get the best results for your dashboards, you need to smarten up your data to get the most out of it. Doing this is not essential, but it is best practice to ensure your data is clean and in the correct format before creating your dashboard.
2. Getting things ready
Setting up your data in a table or list can keep relevant data together and provides a way to manage your data effectively. It will also ensure that you get the most out of the spreadsheets various formulas and controls.
Spreadsheets like structure. Once set up correctly, you will not only save yourself time, you will ensure maximum flexibility of your data both now and in the future. You never know when you may want different information from the same dataset.
3. Formatting your data matters
To format your table or list, first, use the main toolbar to bold or italicize the headings so they differ from the rest of your data.
Resist the urge to highlight and format entire columns and rows. This creates unnecessary formatting in cells that do not require them and adds to your workbook size. If you want to italicize data in a column, for example, use italics on only the data you need.
4. Professionalism and Consistency
Your cells must be formatted correctly for the data type you are using. This could mean selecting the desired date format, formatting your numbers, and making sure your decimal places are consistent. We will look at this in more detail in Chapter 3.
The most common formatting options are found on the main toolbar. However, if you want to see the whole range, you will have to click on the Format menu. Dates are formatted there too, under Number, as dates are just the numbers of the days since the first of January, 1900. This is also why you can use them in calculations.
Don't forget to remove any blank columns or rows, either via the Edit menu or by highlighting the column or row and right-clicking. Ensure you have sorted your data, as many spreadsheets functions require this, such as VLOOKUP.
5. Using colors
When selecting colors, keep in mind that not everyone will print in color. Do you need to use your company colors? Do you need to use cell shading?
Will you need to print copies of your dashboard or is it intended to only be viewed on a screen? If you decide to print copies, try shading the cells using greys, or use a white font on a black cell background.
Colors look good on screen, but overdone formatting is not professional. Less is more.
6. Let's practice!
Now you know how to improve the efficiency of your data. Let's put this into practice and clean up your dataset, so it is optimized and ready to use to create your dashboard.