How to Make Dashboards in Google Sheets

A big advantage of using Google Sheets is that the data is readily accessible online and you don’t need to worry about if people are running different versions of it like you would with Excel. One of the areas where it may be lacking is in creating dashboards. Although you can incorporate slicers, they’re not as user-friendly or nice looking as what you would get in Excel. But in this post, I’ll go over how to make dashboards in Google Sheets quickly and easily.

Here is a sample of what my data set looks like. If you want to view the data plus the dashboard I created here, you can check out the Google Sheets file here.

Google Sheets data set.

Step one is to create some pivot tables. Like with Excel, I prefer to create a pivot table for each view that I want. I will set up four pivot tables, categorizing sales by:

  • Store
  • Salesperson
  • Product
  • Date

To keep things simple, you can put each one of those fields in the ROW section while the sales can be in the VALUES section:

Pivot table editor in Google Sheets.

When creating the pivot tables, be sure to un-check the option to Show totals (this is so that they don’t show up in the charts):

Show totals option in Google Sheets for pivot tables.

What you may want to do is create one pivot table and then copy and paste others, and just change the rows. One additional step you will need to do for the pivot table that contains the dates is to also group them by month. To do that, right-click on any of the dates and select Create Pivot Date Group:

Creating a pivot date group in Google Sheets.

Then, from the following menu, select Year-Month:

Different pivot date groups in Google Sheets.

This is how your pivot tables might look like once you are done:

Set of pivot tables in Google Sheets.

Where you put these pivot tables isn’t important. The key is leaving enough space between them so that they don’t potentially overlap should your data get bigger. Otherwise, you will run into errors and have difficulty updating your data. Since my pivot tables won’t get any wider based on the selections I’ll make, there doesn’t need to be any extra columns between any of them.

Now that the pivot tables are set up, the next step is to set up the different charts for each of them. For the sales by store, I will create a pie chart to show the split among the stores:

Pie chart showing sales by store in Google Sheets.

The one thing you will want to pay attention to for each chart is the range. Since your pivot table could expand, it’s a good idea to make the range bigger than it needs to be, even if it will contain blank values. For example, changing this:

Default date range for chart in Google Sheets.

To this:

Expanded data range for chart in Google Sheets.

This will ensure that additional data gets picked up by the chart should your pivot table get bigger. This is also why it is important to ensure you don’t place any other pivot tables below one another. Ideally, you’ll want to keep them side by side rather one on top of the other.

For the pivot table that shows sales by salesperson, I’ll use a bar chart since the names can be long:

Bar chart showing sales by salesperson.

For the product sales, I’ll mix it up and have those as column charts:

Column chart showing sales by product.

And for the sales by date, I will set those up as a line chart:

Line chart showing sales by month.

I will also add a scorecard chart, using any of the pivot tables. For this, I just want to pull the total sales:

Scorecard chart showing total sales.

Now that these charts all set up, it’s just a matter of organizing how you want to see them on your worksheet:

The one thing missing to make this dynamic: slicers. To add slicers to all these pivot tables, click on any of them and click on the Data tab and select the Add a Slicer button:

Adding a slicer to a Google Sheets pivot table.

Then, select the columns you want to filter by:

Selecting the column to use in a slicer.

As long as you are referencing the correct data range, then the slicer will apply to all the pivot tables correctly. And now, if I add a slicer for the stores and only select stores A and B, my dashboard updates as follows:

Dashboard filtered by slicers.

One thing to remember when you are applying changes: don’t forget to click on the green OK button on the bottom, otherwise your selections won’t be applied:

Applying filters for slicers in Google Sheets.

If you liked this post on How to Make Dashboards in Google Sheets, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

google sheets pivot table slicers

How to Make a Pivot Table in Google Sheets with Slicers

Google Sheets has come a long way in being a formidable alternative to Excel. While it may not have all of the same features as Excel, Google is adding to its functionality. Creating a pivot table with slicers is now a possibility in Google Sheets, and below, I’ll show you how you can do that with the online spreadsheeting program.

The basics: creating a pivot table in Google Sheets

To create a pivot table in Google Sheets involves about the same steps as it does in Excel: compiling and organizing your data set, and then creating the pivot table. Here’s a quick look of my sample data that I have ready to use:

spreadsheet data in google sheets

Then, on the Data menu, select the option to create Pivot Table:

creating a pivot table in google sheets

The next step is selecting where you to put your pivot table:

Menu to select where to create the new pivot table.

The default, a new worksheet, will often work the best. Although the layout looks a little different, the process remains the same with a blank pivot table being your starting point:

new pivot table created in google sheets

On the right-hand side of the page, you’ll see options to put fields into columns and rows, which is what you’re used to with Excel. Again, the main difference is the layout but the logic remains the same:

adding fields to a pivot table in google sheets

When clicking on the Add button, you’ll see options as to which fields to add, even having the option for a calculated field as well:

Adding a field to the values section of a pivot table on google sheets.

In my example, I’m going to select Total Sales so that I can summarize my data based on sales:

sales total pivot table

Next up, let’s add fields for both the row and column sections of the pivot table. If you have a lot of dates in your data set, you’ll want to put the field into the Row section. Otherwise, Google Sheets may give you an error where there are too many columns.

Even if you want to use dates in the column section, you’ll better of first putting it under Rows. Then, right-click on one of the dates and select Create pivot date group:

Google Sheets create pivot date group

From here, you can group your dates so that you don’t have too many entries. In my example, I’m going to use Month as my breakdown. After that, I can move the Date field back into the Column section:

Google sheets pivot table with column and row data

The problem here is that even if you have multiple years, it’ll group it into the same month. For example, I have one entry for Dec. 31, 2018, and it has not separated that out from the 2019 values. In order to fix this, I need to change the grouping from Month to Year-Month. Then my pivot table looks as follows:

Google Sheets pivot table with year month breakdown

Now the data from December 2018 is broken out. Next up, I’ll add another field for the Row section. Here, I’ll add the Store field. And now my pivot table is looking more like what I’d expect it to:

Google Sheets pivot table row and column data filled in.

Next, let’s also add the Salesperson field as well so that we have more of a breakdown:

Google Sheets pivot table with multiple columns and rows.

One of the things that stands out right away is that in Google Sheets the layout of the pivot table is much more intuitive. One of the annoyances of pivot tables in Excel is they’re not in a tabular format by default. With Google Sheets, it’s not something you need to worry about.

It still doesn’t have the repeating rows for the Store field, but that’s a quick fix: simply click the option to Repeat row labels:

Repeat row labels option in Google Sheets is easily accessible within the field settings.

And then, voila:

google shets pivot table with multiple columns and rows

Just like with a regular pivot table you can also drill down into the individual cells to the detail. In Google Sheets, it also gives you a specific name as to what cell you’ve drilled down on, making it easier to refer back to when looking at many different tabs:

Google Sheets new tab name for drill down pivot table results.

Adding slicers to the pivot table

You can also add slicers to your pivot table to make it easier to make changes to it and update it on-the-fly. To add a slicer, just click on the Data tab while you’re on the pivot table and click on Slicer:

adding a slicer to a pivot table in google sheets

Then that will generate the slicer, where you’ll be prompted to select a column to filter by:

Click on the filter icon and then on the right-hand side you’ll see the option to select a field from a drop-down list. In this example, I’m going to select Salesperson:

adding a slicer to a pivot table in google sheets

Then, on the slicer you can filter by the values in the column:

selecting the values to filter in a slicer in google sheets

If I hit the clear button and select only Rep A, Rep B, and Rep C, this is what my pivot table now looks like:

google sheets pivot table filtered by a slicer

The slicer shows the number of items selected and as you can see, it only has the sales reps that I selected in the data. You can add more slicers for other columns but the process remains the same. The big difference you can see from Excel is that your selections are how you’d make the selections in a normal filter; you don’t have buttons for each slicer option the way you do in Excel.

There are changes that you can make to the font and color of the slicer but other than that, visually, there aren’t many changes to make. So if you’re looking to replicate a similar Excel-type dashboard in Google Sheets with many options available for how slicers look then you may be disappointed here. However, in terms of functionality, the slicers work in much the same way that they do in Excel.

A good start, but Google Sheets is still lacking

Google Sheets still has a ways to go in being a real replacement for Excel. While it does have some unique functions that Excel doesn’t, adding pivot tables and slicers is a significant step forward.

However, one area that still needs more improvement is charting. For instance, creating a chart from the pivot table is not an easy task and doesn’t look like Google Sheets is designed yet to create easy-to-use pivot charts. And until that happens, there’s still going to be a big gap between the type of dashboard you can create with Google Sheets and what you can make in Excel.

The good news is that Google Sheets has made a lot of progress and it’ll likely be even better in the future.

If you liked this post on How to Make a Pivot Table in Google Sheets with Slicers, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

google sheets start page

Use Google Sheets to Track Stock Prices and News

google sheets start page

One of the advantages of using Google Sheets over Excel is that it is easier to access live, dynamic data that you can access from any device that can install the app.

Pulling Stock Quotes

A great feature of Google Sheets is that you can easily pull stock prices (delayed) from Google Finance. There is a unique function called GOOGLEFINANCE that can pull any of the following stock details including price (including open, high, low), volume, even the last time it traded. If I wanted to pull Alphabet’s stock price I could use the following formula:


That will pull me the most recent stock price. If I wanted to see the percent change since the last day’s close I would just change price to changepct:


If you access the help you will see a list of more options:
But you can go even further than that, pulling multiple dates at a time. For example, if I wanted all the closing prices since the start of the year I would enter the following formula:

google sheets help

=GOOGLEFINANCE(“GOOG”,”price”,”Jan 1, 2017″,today())

In Google Sheets it automatically creates a table of values for you and you don’t have to worry about making an array like you would in Excel. The result of the above formula looks like this:

google sheets googlefinance function stock prices

I only entered the formula in cell A1 and it produced the list of results. You can also select an interval if you don’t want every day in the range to show a total.

Getting News Feeds Using RSS

Another unique function of Google Sheets is you can pull news feeds from your favorite news site using the IMPORTFEED function. The key thing is you need to find the rss feed of the news site you want. Finding this is as easy as typing the name of the news feed you want and rss after it. For example, the the list of all of CBC’s rss feeds are found on http://www.cbc.ca/rss/index.html. I can use the top stories rss feed of http://rss.cbc.ca/lineup/topstories.xml for my feed.

My formula in looks as follows:

 =IMPORTFEED(“http://rss.cbc.ca/lineup/topstories.xml”,”items title”)

By using “items title” it will only pull the title of the story, which is a bit neater and easier to look at as the titles do not take up as much space as the descriptions as well. If I selected “items” then I would get five columns of data – title, author, link, date, and the description.

Instead, what I can do is in the next column over enter the same formula and select “items url” which will now have the story and the related link next to each other. This way I can pick and choose what I want. This is how it would look:

google sheets importfeed news

I have shrunk down column A since I didn’t want the whole url to show.

I now have all the pieces to make a start page using nothing more than a spreadsheet:

google sheets startpage

Perhaps it doesn’t rival MSN or Google’s home page but it works for me. I’ve made the formulas for the stock calculations relevant to the cells in column B so I can change the ticker symbol as I want to. The main benefit with using this is 1) you don’t need to open a browser to get stock quotes or news, and 2) you can easily access this information from your phone, all you need is the Google Sheets app installed.

 But wait, that’s not all!

Translating Text

I’m not sure why Google felt the need to, but you can even use their translator function as well inside of Google Sheets using the GOOGLETRANSLATE function.

What I could do is translate these news articles. You need to know the two character code for the language, to get that you can find it on this website:


So what I am going to do is translate the news headlines I pulled earlier and translate them into Chinese. Google has two language codes for Chinese – Simplified, and Traditional. I’ll go with simplified, which is zh-CN.

My formula looks like this:


My output looks like this:

google sheets googletranslate translate
So now you can pull news stories from your favorite news site (just figure out the rss link) and you can translate it into whatever language you want. Unfortunately I can’t tell the accuracy of the translation, ‘Simplified’ Chinese didn’t make it any simpler for me. I still can only make out CSIS from all of that translated text.