#1 – Scrolling Tables
Charts are a great way to aggregate data. But when you need to show the supporting table of data next to the chart, the size differences can be drastic.
Tables can be hundreds of rows deep while the chart may only span a dozen or so rows.
Scrolling down the table allows us to easily lose focus of the chart making comparisons difficult.
To combat this problem, we can turn our table into a scrolling Table Chart that will allow us to maintain a small footprint for the table while providing the ability to scroll through the data without moving down the screen.
- Select a cell in the original data and press CTRL-A to select all the data.
- Select Insert -> Chart.
The offered chart will not be what we need, so we need to change the Chart Type to Table Chart.
This turns the offered column chart into a proper scrolling table chart.
We can adjust the size of this chart and place it wherever we need.
The final touch would be to move the source data to a separate sheet and likely hide that sheet from the user.
BONUS FEATURE – In-Chart Column Sorting
One of the features of the Table Chart is the ability to click a heading in the chart and sort a column in ascending or descending order.
This will sort the data in the Table Chart without altering the sort order in the source data. This means you could have several Table Charts pointing to the same source data but displaying different sorting strategies.
#2 – Aggregate Chart Values
One of the more frustrating things about creating charts is that most charts want to plot each data point even when the same data point occurs multiple times. For example, this chart has repeating Departments that get plotted separately for each occurrence.
If we only want to display each Department once and have all sales for that department aggregated, we can select the chart and click “Aggregate” in the Chart Editor – Setup options panel.
We now have a chart where “Salary” is aggregated by “Department”.
Featured Course
Google Sheets – The Comprehensive Masterclass
#3 – Publish to the Web
With Google Sheets, you can easily create a webpage out of your spreadsheet or embed your spreadsheet in an existing website.
To do this, select File then click “Publish to the Web”.
You can decide whether to publish the entire document or just a specific sheet to the Web.
After you click “Publish” you will be given a link that you can copy and paste into a Web browser.
You can also obtain an embed code to use in an existing web page.
By publishing to the Web, anyone can have access to the spreadsheet whether they are logged into their Google account or not.
If they have a Web browser and an Internet connection, they can see your data.
One of the great things about this feature is that if you change the source data, anyone viewing it in a Web browser will receive the changes. No more emailing updated copies of the spreadsheets to everyone so everyone has the latest information. We can maintain a “single version of the truth”.
If you need to retract the published content, select “Published Content & Settings” and click “Stop Publishing”.
#4 – Column Stats
The Column Stats feature provides an overview of the data in each column of a table. This overview includes information such as:
- Number of unique items
- Number of instances of each unique item
- Total rows
- Number of empty cells
- Common aggregations (sum, average, median, max, min)
To activate the Column Stats feature, click on the table and select Data -> Column Stats.
This provides a panel on the right with statistical information regarding the selected column in the table.
#5 – Open-Ended Cell References
This is a great feature when selecting ranges of cells where you need to account for future data.
Typically, if you needed to sum a range of cells, you may have created a formula like the following:
=SUM(D4:D22)The problem is that when you add data below the last row (in this case, row 22) the SUM function would only look to the last defined cell and no further.
Common solutions to this problem include:
- Select more rows than you need when creating the formula (ex: =SUM(D4:D500))
- Select the entire column (=SUM(D:D))
- Manually update the formula each time the data range changes
Each of these options has negative aspects that are not to our benefit.
The proper solution is to create an open-ended reference. This is where you define the start of the data (ex: D4) but let the computer determine the end of the range dynamically.
We write the formula in a way that defines the column but not the row for the closing reference.
=SUM(D4:D)This tells Google Sheets to figure out the end of the range each time the formula is recalculated.
#6 – Date from Calendar
When you type a date into a cell, you can go back and double-click the cell to bring up the Calendar picker tool.
If you want this feature applied to empty cells (in anticipation of accepting dates), you can use Data Validation to force the acceptance of only dates to the cell(s).
Select the cell(s) and click Data -> Data Validation.
Set the Criteria to “Date” and consider setting the “On invalid data” option to “Reject input”.
Now you can double-click one of the selected empty cells to reveal the Calendar picker tool. This will expedite data entry as well as ensure non-date data can be entered into the cells.
#7 – Checkboxes
If you maintain lists in Google Sheets and want to have a system of checkboxes to keep track of “incomplete vs. completed” items, this is easily accomplished.
Select the cell(s)you wish to add a checkbox to and click Insert -> Checkbox.
You can alter the color of the checkbox(s) by changing the cell font color.
You can activate/deactivate the checkbox by clicking directly with the mouse or select the cell and use the spacebar.
The cell holding the checkbox will contain a “True/False” posture depending on the checkbox state. These postures can be used to enhance other logical operations in the spreadsheet, such as displaying messages, applying Conditional Formatting, or triggering calculations (ex: calculate a discount if “True”).
#8 – Pivot Tables
To quickly analyze tabular data, select Data -> Pivot Table, then select your output location, either a new sheet or somewhere on the existing sheet.
You will be delivered an empty shell to accept the logic that will form the Pivot Table.
Sheets presents you with a list of suggested logical constructs by which a “quick and dirty” pivot table can be made.
If you want something tailored more to your needs, you can click the “Add” buttons to the right of the “Rows / Columns / Values / Filters” buttons to select a table column to be applied to that logic zone.
In the Rows and Columns zones, you can set the sorting method (ascending or descending) as well as by topic or by aggregation.
In the Values zone, you can change the aggregation method (SUM, AVERAGE, COUNT, MAX, MIN, etc.) as well as the display mode (by decimal or by percentage).
#9 – Web Functions
Google Sheets contains many useful functions for dealing with Web data.
These functions include:
- Importing data from webpages and data feeds
- Linking to external sources
- Importing XML data
- Checking the structural integrity of URLs
To access the Web functions, click Insert -> Function -> Web to see a list of the available functions.
Using the IMPORTHTML Function
To import data from a webpage, we can use the IMPORTHTML function.
With the IMPORTHTML function, you can easily import tables and lists from web pages.
The requirements for IMPORTHTML are the webpage URL, the type of object to be imported (“table” or “list”), and the index number indicating the table or list number on the webpage.
The syntax for IMPORTHTML is as follows:
It is not always obvious as to whether objects on a webpage are built as tables, lists, or other types of web parts. It’s also not obvious as to what index number is assigned to which table on a webpage. You may need to experiment with these options to figure out the correct combination.
An example of the IMPORTHTML function is as follows:
=IMPORTHTML("https://www.officeholidays.com/countries/austria/2021","table",1)
Advantage: Because this is a direct query to the webpage, if the webpage is updated, the spreadsheet will update to reflect the changes.
Disadvantage: Aside from the trial-and-error nature of the formula construction, if the object being referenced on the webpage moves to a different location, causing the index number to change, incorrect information will be retrieved.
#10 – Filter Functions
Google Sheets comes equipped with hundreds of functions to help you analyze your data.
These functions are accessible by clicking Insert -> Function and drilling into the needed category.
Some of my most favored functions are the Filter functions. These include FILTER, SORT, SORTN, and UNIQUE.
The UNIQUE Function
Suppose you have a list of Departments and you want to derive a list of unique items from that list.
You could write a formula like the following:
=UNIQUE(C2:C20)
The SORT Function
If we want to sort the results of the above UNIQUE function’s output in descending order, we can nest the UNIQUE function inside a SORT function like so.
=SORT(UNIQUE(C2:C20), 1, False)
The FILTER Function
If you have a table of data and you want to filter that table by some criteria, you can write a formula like the following that uses the FILTER function.
Suppose we want to generate a list of people who work in the “Finance” department.
A formula to do this would look something like the following:
=FILTER(A2:D20, C2:C20=”Finance”)You can also make this more dynamic by pointing the filter to a cell containing the department name. This way it could be changed by the user.
=FILTER(A2:D20, C2:C20=F2)
If the item in cell F2 were to change to “Sales”, the list of employees and their associated data would dynamically update.
“Do you have a favorite?”
Did you know about all these features? Do you have a favorite among them or possibly a favorite that wasn’t showcased?
Let us know in the comments and you might see it spotlighted in an upcoming post.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
