Data visualization is the guarantee of the simplicity of perception. It is hard to make quick conclusions if there is a lot of information, and it is organized as the canvas of monotonous cells. How these metrics have changed during the year? How significant is the backlog in terms? These and other moments are better to show via diagrams and graphs. Especially if the report needs to be shown to the management or a client.
Each department in Ringostat uses spreadsheets daily. The visualization of our dashboards sets instantly, so the employees don’t waist 10-15 minutes to get the data. In this article, we will share hacks that were especially useful for us and will be helpful for everyone who works with data.
Evident, however, important: freeze rows
This thing is simple and basic, but it is necessary to use it with a significant amount of data. Otherwise, while scrolling a spreadsheet, you might confuse the metric that you are looking at right now.
It is possible to freeze the first or first few rows. You can also freeze the column if the required metrics are placed horizontally. Open the View tab — click on Freeze and choose needed rows.
Chart
We won’t consider making charts in detail as it is rather simple and described in the Google manual. Nevertheless, we will show how to make them the most illustrative possible.
Examples of charts will be shown in the case of a document with relevant data. You can find it via the link, copy, and discover formulas to understand how it works. On the third sheet, you will find a “database” that transfers data to the first one.
Color scheme selection
A graph or chart can be created with any spectrum. Select the required area and go to Insert — Chart. In the appeared block choose Customize — Series, there you can set the color. The same thing if you need to change the color in the existing chart.
To change the background of the chart, you need to open the same section and choose Chart style on the top — Background color. The whole document or a single cell can be colored with the fill.
Color alteration
The information is perceived easier when the colors of the cells are alternated. To do so, you need to choose the required range or column, open Format — Alternating colors, and click on the required colors.
Charts creation by clicking on the checkbox
The checkbox is a square area. There is a bullet that appears when you click on it. For example, it may be a tick. You can create a chart depending on the activated checkboxes.
Let’s set an example. There are the following metrics in the document mentioned above:
- MQL — all inbound leads for which the deals haven’t been opened yet;
- SQL — leads that were considered as qualified by a sales department: the visitor’s request was relevant, it wasn’t a SPAM, a sales rep could contact a person, etc.;
- Won — won deals;
- Lost — lost deals;
- Spam — junk leads.
Let’s say, we want to monitor how changed the number of MQL within a year and compare it with the number of SQL and won deals. To do so, we make only three clicks and receive a ready-made chart:
How can this be done? Select the needed range, column or a cell. Click on Insert — Checkbox and the whole area transforms into a checkbox.
You need to use formulas so the charts will be crated as you click on the checkbox. If you move the chart in the example spreadsheet, you will see the following below:
When you click on one of the checkboxes, the relevant cell in the area marked by red, changes on TRUE. Data are copied from the set array, and that’s how the chart is built.
The row of numbers on the bottom contains the formula mentioning the row, its status, and array. For example:
=IF(A3=TRUE;ARRAYFORMULA(C3:O3))
We recommend the article “Basic tips for comfy work with Google Sheets” to better understand how to work with Google Sheets formulas.
Visualization by a timeline
Let’s say you need to create a chart based on the data taken for a specific period. This can be done by choosing the 2018 or 2019 year from the drop-down list in the example spreadsheet. Checkboxes, as at the example above, also have to be selected.
To set this choose Data — Data validation — List of items or List from a range and enter cells. While entering a formula, we put the cell number but not the value or text.
Percentage and absolute value on the same chart
It’s especially comfy to monitor the dynamics by different values on the same chart and not switching between tabs. В нашем примере из выпадающего списка на визуализации можно выбрать:
- Leads Amount;
- Conversion from MQL to SQL, from SQL to Won deals, from MQL to Won deals.
Choose the row where will be a drop-down list. Like in the example above, choose Data — Data validation — List of items and enter parameters.
The formula is the same “hidden” under the chart, as in the previous examples.
=IF(A10=«Leads Amount»;ARRAYFORMULA(TO_PURE_NUMBER($C$18:$O$23));
(IF(A10=«Conversion»;ARRAYFORMULA(TO_PERCENT($C$27:$O$30));«0»)))
IMPORTANT: Data has to be filled automatically. The formula won’t work if you put a number or a percentage or a number. You need to enter the transformation to the absolute value or a percentage in the formula itself — for example, to_pure_number, to_percent.
Rules of data formatting
Cells coloring
It is possible to make cells colored depending on the value that is entered in them. Conditions may be the opposite. For example, the more leads, the better. In this case, cells have to be colored in green. However, it is gravely when there are a lot of lost deals and junk leads. This is better to be painted in red.
Select the value of the array that doesn’t have to be colored Format — Conditional formatting. In our case, we have set two rules for MQL and SQL:
- red is for values that are less than D2 — the value by a first month;
- green is for values that are equal or greater.
Formula automatically changes and expands as time goes by. The logic is opposite to color cells by lost deals and junk leads.
The array won’t work if you delete the row. It is not automatic. Change the formula while deleting rows.
Coloring depending on the critical value
The logic is close to the one in the example below; however, there are three rules. For example, there is a document where is shown the last publication on the blog. If an article was published on the blog yesterday, the cell would be green. If it was published 5 days ago, the cell would be yellow. In case if nothing was posted for the whole week, the cell will be red.
To do so, it is required to add one more rule in Format — Conditional formatting. Thus, for example, values between 2 and 5 days will be colored in yellow.
Conclusions
These settings may seem complicated, especially if you are a marketer or business owner and interested in the accurate data on the cost recovery of advertisements. In such a case, end-to-end analytics will be useful for you. It already has ready-made reports on the most critical metrics that are driven automatically.
Investments in advertisements, revenue by each channel, and cost per proper call are among them. You receive everything automatically and don’t need to customize dashboards. Import of costs can also be manually imported to the end-to-end analytics if it is required.
