Category: Spreadsheets

Everything you ever wanted to know about Sparklines in Google Sheets

Sparklines are small, lightweight charts, typically without axes, which exist inside a single cell in your spreadsheets. They’re a wonderful, quick way to visualize your data, without needing the complexity of a full-blown chart.

Google sheets sparkline bar chart

  1. Introduction
  2. Sparkline Examples
  3. Sparkline Syntax
  4. Line Sparklines
  5. Column Sparklines
  6. Bar Sparklines
  7. Winloss Sparklines
  8. Option/value pairs in cells
  9. More advanced examples
  10. Further reading

Introduction

Sparklines were first created by interface designer Peter Zelchenko around 1998. The term “sparkline” was coined by statistician and data visualization legend Edward Tufte.

Grab the data and solution file for this tutorial:
Click here to get your own copy >>

Continue reading Everything you ever wanted to know about Sparklines in Google Sheets

Excel-fficiency: 10 tips for boosting productivity in Excel

Status bar formulas

1. Shortcuts

Let’s start with keyboard shortcuts. It’s one of the single best investments of time you can make to further your spreadsheet skills. It’s all about reducing your reliance on the mouse and instead harnessing the awesome efficiency of navigating spreadsheets from the keyboard.

Try these for starters:
Continue reading Excel-fficiency: 10 tips for boosting productivity in Excel

Excel tutorial: Building a dynamic, animated dashboard for U.S. political data

Excel dynamic dashboard screenshot

Who doesn’t love a dynamic, animated Excel dashboard?

Here’s one I’ve been working on recently, a data visualization of historical U.S. political data, showing party trends, state populations and sitting presidents over time:

Excel Dynamic Dashboard - US Political data

In the following post, I delve into the details of how I created this dashboard. It’s not a full cell-by-cell account of how I did it, because that would require an article at least twice as long, but rather a look at the various steps and thought processes along the way.

If it appears a little ragged, that’s because it probably is! Most likely because I’m writing this bleary eyed at 1am, between feeds and diaper changes of my 6 week old son. 😉

Continue reading Excel tutorial: Building a dynamic, animated dashboard for U.S. political data

Using Google Sheets as a basic web scraper

Want to build a web scraper in Google Sheets? Turns out, basic web scraping, automatically grabbing data from websites, is possible right in your Google Sheet, without needing to write any code.

You can extract specific information from a website and show it in your Google Sheet using some of Sheets’ special formulas.

For example, recently I needed to find out the authors for a long list of blog posts from a Google Analytics report, to identify the star authors pulling in the page views. It would have been extremely tedious to open each link and manually enter each author’s name. Thankfully, there are some techniques available in Google Sheets to do this for us. Continue reading Using Google Sheets as a basic web scraper

10 Techniques for building a Google Sheets Dashboard

You’re probably familiar with using Google Sheets to organize and analyze your data. But did you know you can build a dynamic Google Sheets dashboard to really understand your data?

With a handful of powerful techniques, you can add some pizzazz and dynamism to the presentation of your data. Here are ten tricks to try next time you’re building a Google Sheets dashboard.

Continue reading 10 Techniques for building a Google Sheets Dashboard