Calculate with Formulas and Functions

By Jack Dougherty, last updated March 16, 2016

TO DO

  • when possible, start text by posing a common problem, and how this method can solve it
  • redo visuals: Google Sheets with better examples
  • see other notes inserted below

Simple formulas can save you lots of time. The big advantage of spreadsheet tools is the ability to insert simple formulas to calculate numbers, or combine columns of text, for entire rows and columns.

Write a simple formula

In most spreadsheets, begin writing a simple formula with an equal sign, and refer to specific cells and functions, such as:

  • = A2 + B2 + C2

Write formulas with built-in functions

TO DO rewrite to show how this is same as above

  • = Sum(A2:C2)

TO DO rewrite to show common numerical and textual functions

  • = Average(A2:C2)

Copy and paste, or drag formulas

If you've inserted a formula into one row, how can you quickly do the same calculation across all rows?

Spreadsheets can magically automate calculations across rows or columns. In most cases, you can copy and paste a formula into new cells. Sometimes you can click-and-drag the lower-right corner of a formula cell (which may appear as a cross-hair) to automate calculations.

Copy and Paste > Special > Values to replace formulas with data

After inserting calculations in a spreadsheet, sometimes dynamic formulas must be replaced with static data before the results can be visualized. One solution is to select and copy a column (or the entire sheet), then paste > special > values to replace the formula with numerical results.

Remember that if you need to check or run the calculations again at a later point, click (or right-click) the tab to save a copy to the spreadsheet as a backup.

Create a column of consecutive numbers

To quickly create a column of consecutive numbers, such as unique ID numbers, in most spreadsheet tools:

  • Insert the number 1 into a cell and press Return
  • Click the cell and float the cursor over the bottom-right corner, where it will change into a cross-hair symbol
  • On a Mac, hold down the Option key and drag the cross-hair down to create consecutive numbers
  • TO DO insert equivalent commands for Windows, Chromebook

Data Visualization For All is copyrighted by Jack Dougherty and contributors and distributed under a Creative Commons Attribution-NonCommercial 4.0 International License. You may freely share and modify this content for non-commercial purposes, with a source credit to http://DataVizForAll.org.
Improve this book: Donate to DataViz students and add comments or revisions.

results matching ""

    No results matching ""