Create an analytical dashboard for your Google Sheets

Photo by Editor | Chat GPT

. Introduction

The Google Sheets Dashboard is a powerful way to imagine the progress of the project, compare tasks and identify something serious that you need. You can easily prefer your work, track barriers and take action in real time. Even better, when you connect Google Sheets dashboards with resources like Google Analytics, Sales Force, or Gut Hub, your dashboards can effectively refresh as data is modified or changed.

In this article, I will adopt a way to create a basic dashboard using Google Sheets.

. Why consider a dashboard for your Google Sheets?

A Google Sheets Dashboard is a very useful tool that will help you see and track data in several different platforms. By just clicking on a bottle, you can get data from different spreadsheets to display data in charts, tables and graphs so we can analyze our business or plan.

Even you can create warnings that are automatically sent, so you can monitor changes in the dashboards and observe that if trends are being developed that may require corrective measures.

Google Sheets contain some easy steps to create a functional and visually attractive dashboard. Now let’s take a look at them.

. 1. Importing and organizing your data

Start your data in a clear, tabler format. To get started, we will use flower sales dummy datastas. To get it l you, you can copy This sheet template With a already included database (and solution!).

If you want to use another data source, you can easily upload the CSV file or connect the Google Sheet to a specific data source using its connector. Once the data is in our environment, there are some points to keep in mind:

• Make sure your data is clean and permanent (eg, no empty cells or mixed formats)
• Use data verification to create a drop -down for category or status
• If you intend to track trends over time, add timstamp column

. 2. Prepare a chart

Once your data is ready, highlight the section you want to analyze and enter and then chart. Google Sheets will initially insert the chart on your sheet, as an empty canvas.

You will work with two main tabs to create your chart:

• Setup: Where you choose the chart type (such as, line, bar, pie) and describe the data range. Make sure you add all the necessary columns representing your axis or variable.
• Custom: Where you adjust the appearance, label, color and layout to meet your style or reporting needs. Use this section to prepare your own chart for explanation, ability to read and effect.

Keep in mind: The number of columns you add in your data range will determine how many dimensions your chart (such as, X and Y axis).

Screenshot of Google Sheets

!! 2.1 score cards or gauge charts

Score cards are excellent to highlight key performance indicators (KPI) or single -value matrix, such as total revenue or average discount.

One to make you, you can first enter a new chart and choose the type of score card chart.

Then go to the setup tab, specify the order total column to display the data range, the total sale.

After that, a basic score card will appear immediately. Next, then you can customize it in the Customs tab:

• Adjust the scale. We will choose millions for this example.
• Include a former or suffix.
• Set up a title, such as “Sales Revenue”, and align both the title and the Matriculation Value in the center.

This is how the final score card can be seen:

You can repeat this process to reveal the other KPI:

1. Total units sold by summarizing the quantity column
2. Average discount, by calculating the average discount column

یہ اسکور کارڈز آپ کی سب سے اہم میٹرکس کا ایک تیز ، AAAAAAAAAAAAAAAAAAAAA-AAAAAAAAAAAAAAAAAAAAA-AAAAAAAAAAAAAAAAAAAA۔

!! 2.2 line charts

Line charts are ideal for tracking trends and changes over time. To make a:

• Insert a new chart and select the type of line chart
• In the setup tab, select two data limits, for an X -axis (such as time or date column) and for a Wii axis (such as total orders or sales revenue)

This setup allows you to imagine how your matriculation is developed over time. In order to make the chart easier to interpret, add a temporary evolution of tax such as descriptive titles. Once the formation, your chart should clearly show the trend of revenue in your selected period. Here is my last result:

!! 2.3 times and column charts

Bar and column charts are the best options for comparing values in different categories. In this example, we want to identify which product category offers high average waiver:

• Insert a new chart and select the bar chart type.
• In the setup tab, select two data limits. One for a category axis (product category). Another one for the Value Axis (using discount column, average deposit)
• Customize the chart by assigning a different color to each time to enhance the visual explanation.

!! 2.4 times and column charts

Pie charts are useful for displaying ratio and understanding how different parts play an important role in the whole. In this case, we will imagine the distribution of revenue through the product category:

1. Insert a new chart and select the type of pie chart.
2. In the setup tab, select the appropriate data limits. For a category (such as product category) for another values (such as total income of total)
3. Customize the chart by assigning a different color to each EACH for the ability to read better.

This will create a clear, proportional error in the category, which will help you to see which sections of the highest income.

Now we have all our charts! We are about to receive the final version of our dashboard. Therefore, the next natural move allows users to choose which data is to be seen, which causes it.

. 3. Adding slices to filter data

Flexible and dynamic access to your dashboard increases the amount of DATA data, you can benefit from slices. Slicks are interactive filters, which allow users to create their own specific filters based on some columns.

First, go to the data and add the Slacear option.

Then select your entire dataset as a slight range and choose the column you want to filter the slicer (such as the region, the product category, or the date).

Based on the selected values of the user, the slicer will automatically filter all the dashboards, charts and concepts connected.

In our example, we will add two activist sliers that allow us to create filtered dashboards, one for order type and the other for product category, get more easily discovered data set.

. 4. Make a dashboard with more than one chart

Now that you have made your charts and made the activist slices, we will collect everything for a last dashboard. To do this, we have to do:

• Make another sheet for your dashboard
• Place a clear title in the upper part of the new sheet to summarize the contents of the dashboard
• Sort and group your concepts in the same or related parts
• Align your chart and slice together to maintain a reasonable clean and professional dashboard

With just a few sequence opportunities, you will convert a combination of charts into easy dashboard, ready to read and offer, ready to make a decision -making and offer.

. Conclusion

Google Sheets offers a versatile and leisure method of building dashboards that help you track performance, find trends, and make data -driven decisions without leaving your browser.

By following this guide, you have learned how to effectively create and import your data. Make a variety of charts for different insights. Add interactiveness through silicians. And design a polished, professional dashboard layout.

Whether you are managing projects, tracking sales, or monitoring KPI, Google Sheets Dashboards can become real -time insight to your going tool, coding is not required.

Jozep Ferrer Barcelona is an analytical engineer. He graduated in physics engineering and is currently working in the data science field applied to humanitarian movement. He is a part -time content creator focused on data science and technology. Joseph writes everything on AI, covering the application of the explosion at the field.