Reporting & Analysis
Sep 7, 2020
TLDR
Google Sheets has introduced updates like Dark Mode, Smart Fill for data entry, Smart Cleanup for data cleaning, advanced chart formatting, customizable themes, slicer filters, and scorecard charts. Collaboration is now possible in G Suite without Google accounts, and BigQuery integration provides access to massive datasets. Connected Sheets offer automation and mobile support.
Lately, Google has been pumping out updates thick and fast for its Google Sheets customers. While Google has been producing some good quality upgrades - perhaps in an effort to guard its share of the spreadsheet market - a lot of it goes 'under the radar' rather than being publicised that much. In this article, we will explore some of these new features, and how you can benefit from them.
Dark Mode
As seen on the new Android roll-out, dark mode allows for a partially inverted colour scheme which is kinder on the eyes at night. This is part of a developer trend toward increased user interface customisation across Google's range of products, perhaps with a view to capturing the attention of less experienced users. That said, this is definitely going to be a late-night remedy for the overworked professionals out there!
Available on all G Suite plans.
Smart Fill
Following on from the predictive text functions seen in Gmail and Docs, smart fill "detects and learns patterns between columns to intelligently autocomplete data". You'll see predictions for new cell entries, as well as auto-fill options for new rows and columns. We think AI will be revolutionary for spread-sheeting over the coming years.
Available on all G Suite plans.
Smart Cleanup
After importing data, Smart Cleanup takes an educated guess at time-saving cleanup operations. For instance, removing duplicates, aligning date formatting and correctly parsing Comma Separated Values.
Furthermore, once you're done with cleaning up, Smart Cleanup's Review Feature offers concise 'pre-analysis' summaries of patterns in the table. Being able to see instant visualisations and charts is really useful for creating reports quickly. It also enables the user to see a visual snapshot of data making it easier to catch errors at a glance.
Oh, and it has it's own cute sidebar, just like our favourite plugin...
Image courtesy 9to5google.com
Available on all G Suite plans.
New Chart Text and Number Formatting
You can now select pretty much any chart text element (including recent additions of legend and specific data labels) to update style and content as preferred. The update also includes advanced number formatting for multiple labels/axes, including conditional colour formatting. These extra layers of customisation help create reports that aren't just informative, but visually appealing!
Available for all G Sheets users.
Add a theme to your spreadsheet
To make sheets more interactive, Google rolled out themes, slicer filters and scorecard call-outs in August. Themes allows you to largely customise the look and feel of your spreadsheet, with a range of preset and custom designs available.
Go to 'Format' → 'Theme' to access the right-hand panel to choose the right design for you. We think this is excellent for adding some life into your spreadsheet - it can help a report really stand out, or appear much more professional by aligning with your enterprise's colour scheme.
Available on all G Suite plans.
Add a slicer filter to your data
Slicers are a long overdue addition to G Sheets. They can practically turn a spreadsheet into a catalogue, by providing drop-down fields for you to toggle categories on display. Similarly, you can filter what's on display by specific conditions and values.
Available on all G Suite plans.
Call out key metrics with scorecard charts
When Google announced this update, they suggested it could be used to emphasise KPI's - but really, the option to illustrate your data with a dedicated info-graphic slot could help you draw attention to any key statistics, leading to a generally more engaging report.
Available on all G Suite plans.
Image courtesy 9to5google.com
Visitor collaboration (Suite)
You can now share and collaborate in G Suite with people who don't have a Google account. This works by assigning them a login pin-code, a security feature that allows you to verify them on their behalf.
Available for Business, Enterprise, Education, Nonprofits and Enterprise Essentials G Suite plans. (Not available on G Suite Basic plans)
Connected Sheets (BigQuery)
This milestone integration with Google's BigQuery Data Warehouses provides access to petabytes of data—billions of rows—without leaving your spreadsheet. Since no knowledge of SQL is required, complex queries need no longer be the preserve of data specialists.
Previously available in beta, the BigQuery SQL integration update will replace 'Sheets Data Connector', a feature that previously allowed you to connect other sheets as well as BigQuery sources in a more limited capacity.
This feature allows you to generate 'background' insights from BigQuery's plethora of public datasets. Whether its working out behavioural averages or creating a 'control' set to compare your analysis to, the massive warehouse of data presents countless opportunities for valuable insights.
Available to Enterprise, Education and Enterprise Essentials G Suite plans. (Not available on Basic, Business or Nonprofits plans)
Improve your DTC game. Sign up for weekly tips.
New APIs, Connection, and Mobile Capabilities for Connected Sheets
After the Connected Sheets roll-out, a subsequent update added two things: automation and mobile app compatibility. The automation package allows you to run macros for your Connected Sheets operations, and for developers it includes an API platform, allowing you to create personalised data retrieval scripts. As for mobile app support, this means you can pull BigQuery data from your phone - albeit in a slightly more limited capacity.
Available to Enterprise, Education and Enterprise Essentials G Suite plans
Guest Author
About the Author