Data visualization in spreadsheets
Data visualization has become the number one way to showcase analyse data. Rather than looking at a huge amount of data analyzed in a spreadsheet, it is much easier for the audience to understand a visual representation of the data. This data visualization is called dashboard. So why use Spreadsheets? Because of its familiarity, flexibility and the tools available to analyze data, Spreadsheet is the most widely used Business Intelligence (or B.I.) tool in the world.
Following are the things you need to consider, things you need to know and some basic knowledge about charts and their customization. So, let’s begin.
Dashboard is selective data you want to use as validation. Now, how to create a basic dashboard, before you begin to think about what information you need? Who is your Audience? How are they viewing it?.
Some more things to see are the Logos, Scenarios, Colors, and usage of 3-D objects, how much detail is enough, type of charts.
Different Type of Charts
Creating A Basic Dashboard
A simple dashboard has user friendly functions and controls to show only the data you want to report and uses the right visualization to showcase this data. A dashboard will allow you to pull data from the main data set, using VLOOKUP, formulas of reference, drilling data further using data validation. When creating a dashboard you need to ensure to include the right tools to grab audience attention.
Getting your data right for the dashboard
Before creating a Chart think about what data you are going to use, what it is and where it is located, is it in the same workbook, pulled from the net, copied, or imported from other sources. Depending on where it comes from the format may need a little bit change, or a lot. It’s best practice to get your data in order first, as this is the base of all your visualization.
What to Show In Dashboard
A smart dashboard shows only the data you need others to see. You should not display the entire data set as it draws the audience’s attention away from the main message you want to showcase because the stage is too busy. A best way to create a dashboard is to keep the data set in separate sheets and selectively pull the data you want to create a chart.
A formula of reference is the best tool, it shows the value of one shell in another shell or another shell in different sheets
Formulas Of Reference
It is a basic calculation that picks up a value from another cell.
=Sheet2!A1- “Point to Sheet 2 cell A1 and return what is in cell A1”
The Column Chart
Once you have extracted your data from the data set, you can easily create a column chart. A column chart is the best way to visually compare values over time or comparing data from different categories.
Setting Up your Data
To get the best result 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 a good practice to ensure your data is clean and in the correct format before creating your dashboard.
Setting Up your data in a table or list can keep relevant data together and provide a way to manage your data effectively. It will also ensure that you get the most out of the spreadsheet’s various formulas and control.
Formatting your data matters
To format your table or list first use the name toolbar to “Bold or italicize the headings” so they differ from the rest of your data. Resist the urge to highlight the entire column and rows that create unnecessary formatting to cells that do not require them. If you have to italicize a cell in a column, for example, use italics on only the data you need.
Professionalism and Consistency
Your cell 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.
The most common formatting options are found in 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 numbers as dates are just a number of pdays. Don’t forget to remove any blank columns or rows, ensure you sorted your data as many spreadsheet functions require this such as “VLOOKUP”.
Using Colors
When selecting colors to keep in mind that there is not everyone that will print in colors, 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 it intended to be seen on a visual screen. If you desire to print copies try shading the cells or use white color text over black shaded cells. Colors are good on screen but overdone formatting is not professional “LESS IS MORE”
Some More Things About Creating and Formatting Charts.
You should have a table, list and data in different sheets than the dashboard.
You can easily select different ranges that are non-contiguous or not adjacent to each other by highlighting the cell(s) holding down the control key and selecting any other cell(s) you want in your range. Once you select your data you can create your chart. When you create your chart sheet will display the type of chart it suggests for the type of data set you have selected.
Changing the Chart type is easy to start by double clicking the chart that will bring up the chart editor menu to the right of your screen, it’s the first option on the data tab.
Formatting The Chart
Make sure that your chart and data sets have heading. Remember you want your chart to stand out. Use the chart editor’s customize tab to easily edit other options including the charts Style, Background, Chart and Axis Titles, Series, Legend, Horizontal and Vertical Axis and Guidelines.
There are obviously a lot of tricks, methods and techniques that are impossible to cover in this blog. This blog is to create a basic Idea about Data Visualization.
For more such posts, do follow out our Publication :
https://medium.com/tek-society
Also, Do clap. It encourages me to write better!
Thank you for your patience.
