Skip to main content
Library homepage
 
Chemistry LibreTexts

Appendix 3: Tips on Google Sheets

  • Page ID
    303345
  • \( \newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \) \( \newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}} \)\(\newcommand{\id}{\mathrm{id}}\) \( \newcommand{\Span}{\mathrm{span}}\) \( \newcommand{\kernel}{\mathrm{null}\,}\) \( \newcommand{\range}{\mathrm{range}\,}\) \( \newcommand{\RealPart}{\mathrm{Re}}\) \( \newcommand{\ImaginaryPart}{\mathrm{Im}}\) \( \newcommand{\Argument}{\mathrm{Arg}}\) \( \newcommand{\norm}[1]{\| #1 \|}\) \( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\) \( \newcommand{\Span}{\mathrm{span}}\) \(\newcommand{\id}{\mathrm{id}}\) \( \newcommand{\Span}{\mathrm{span}}\) \( \newcommand{\kernel}{\mathrm{null}\,}\) \( \newcommand{\range}{\mathrm{range}\,}\) \( \newcommand{\RealPart}{\mathrm{Re}}\) \( \newcommand{\ImaginaryPart}{\mathrm{Im}}\) \( \newcommand{\Argument}{\mathrm{Arg}}\) \( \newcommand{\norm}[1]{\| #1 \|}\) \( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\) \( \newcommand{\Span}{\mathrm{span}}\)\(\newcommand{\AA}{\unicode[.8,0]{x212B}}\)

    Navigating Google Sheets

    google sheet menu, row (horizontal), column(vertical), and cell labeled
    Figure \(\PageIndex{1}\): Parts of a Google Sheet labeled (Poirot)

    Make a Google Sheet

    First thing we will be doing is making a google sheet. There's multiple ways to access sheets but we will start with the way that is easiest to keep organized.

    Video \(\PageIndex{1}\): 0:11 video on making a Google Sheet in Drive. (https://youtu.be/TAYVbgreE8s)

    Step 1. Open your google drive

    Step 2. Click +New in the top left corner which will open a drop down menu

    Step 3. Select Google Sheets which will open your sheet in a new tab

    Copy a Google Sheet

    Making a copy of a sheet is a very useful tool. You can use previous sheets as a template for new data or to have an original copy to save when sharing a sheet.

    Video \(\PageIndex{2}\): 0:38 video on copying and naming a Google Sheet (https://youtu.be/MIPYCvl9-Fw)

    1. Open the sheet you want to make a copy of
    2. In the menu, click File and then Make a copy.
    3. This will open the box where you can name your new sheet. (For students I recommend including your name in the title to help whoever is grading your sheet stay organized)
    4. Use folder to select where to save your new copy
    5. Click on OK to make your copy which will open in a new tab

    Google Sheets: Tabs

    Tabs (Sheets) are a great way to organize data within one spreadsheet.

    clipboard_e6168efb7df9deb568f4bd1a54ef80e36.png

    Figure \(\PageIndex{2}\): Sheets and New Sheet

     

    Video \(\PageIndex{3}\): 2:48 Video tutorial on tabs. (https://youtu.be/WTUd8rZ2WHE)

    Make new Tab (Sheet) in Spreadsheet

    TimeStamp: Video 3 0:08

    1.  Click the plus button on the bottom left corner of the screen
    2. Right click and select Rename

    It is best practice to name sheets as all one word. This will make things a lot easier later on when referencing sheets later on. You can use and underscore (_) to replace a space.

    Duplicate a Tab

    TimeStamp: Video 3 0:37

    1. Right click on the tab you wish to make a copy of
    2. Select Duplicate to make a copy
    3. Right Click the new tab and select Rename

    Organizing Tabs

    To move a tab click on the tab and drag it to rearrange. TimeStamp: Video 3: 1:03

    To hide a sheet Right click on the tab then select Hide Sheet. This will hide the sheet from view. TimeStamp: Video 3: 1:15

    To unhide a sheet go to the menu and select View. Go down to Hidden Sheets to select the sheet you want to unhide. TimeStamp: Video 3: 1:23

    To protect a sheet (meaning choosing who can edit a tab) Right click on the tab then select Protect sheet. This will open a menu to the right, click Set Permissions to choose who can edit this sheet then press done. TimeStamp: Video 3: 1:33

    You can combine Protect sheet and Hide sheet to prevent anyone from being able to open a sheet TimeStamp: Video 3: 2:15

    Format

    Numbers

    To numbers go to the menu and click on Format>Number

    Plain Text: Any text or string, cannot do mathematical functions on plain text

    Number: strings of digits, the default is to have no trailing zeros use increase and decrease decimal places buttons to change this

    Scientific: Has number in scientific notation. So for example \(1.75 \times 10^{2} \) will be written as 1.75E+02 (you only have to enter 1.75e2)

    Percent: Will format a fraction as a Percent so 0.01 will be multiplied by 100 to be 1%

    Decimal Places

    You can change the number of decimal places that the cell displays.

    The buttons for this are below Data on the Menu. You can Increase the decimal places or Decrease the decimal places. Make sure to pay attention to rounding and that it is following the correct rounding rules for your project.

    clipboard_e81f94825eb1c9b04c6b0abc6a1f76229.png
    Figure \(\PageIndex{3}\): The buttons for Decrease and Increase Decimal Places is circled in red (Poirot)

     

    Video \(\PageIndex{4}\): 0:34 Video tutorial on changing the number of decimal places. (https://youtu.be/sEyMuHi6nkg)

     

     

    Robert E. Belford (University of Arkansas Little Rock; Department of Chemistry). The breadth, depth and veracity of this work is the responsibility of Robert E. Belford, rebelford@ualr.edu. You should contact him if you have any concerns. This material has both original contributions, and content built upon prior contributions of the LibreTexts Community and other resources, including but not limited to:

    • Liliane Poirot

    Formulas

    Google Sheets has built in tools to be able to do many things in sheets called formulas. There is a full list of formulas by google sheets. We're going to start off with some of the basics then later on go more in depth.
     

    Using a formula

    To start using a formula first select the cell you want to use

    Type an equals sign = to start using a formula in the cell you want to use

    Start to type the formula you wish to use and select it from the drop down menu

    You can type a number or use a cell reference

    you can either type the reference (For example A2) or click the cell you are wanting to reference

    Finally press enter

    Formulas

    Simple mathematical functions

    You can do simple calculations using formulas. Start all formulas by typing =

    Addition +

    Subtraction -

    Multiplication *

    Division /

    Exponent ^

    Parentheses ()

    So for example if we can use the sheet to calculate the gravitational force between two objects


    Video \(\PageIndex{1}\): 1:40 Video example of how to do basic calculations in sheets ()

     

    clipboard_ed866acc95783293c67936911cabd92ae.png
    Figure \(\PageIndex{1}\): Calculating the gravitational force of two objects. Formula used : =D2*((A2*B2)/C2^2) (Poirot)

     

    Logarithms

    Logarithms are used in many different calculations. There is an overview on logarithms in Chapter ? that explains how logs work. In this section we are going to focus on how to use them in sheets.

    LOG10

    Returns the the logarithm of a number, base 10. =LOG10(value)

    This is probably what you will use most often when talking about logarithms. This function will give the logarithm of a number using base 10.

    LOG

    Returns the the logarithm of a number given a base. =LOG(value, [base])

    This is used less often but can be used to return a logarithm using any other base. This function will assume base 10 unless told otherwise

    LN

    Returns the the logarithm of a number, base e (Euler's number). =LN(value)

    This function gives the natural log of a number. You can also do the natural log using LOG by entering the base as "EXP(1)"

    Making formula log and ln and 1/T

     

    Video \(\PageIndex{2}\): 2:11 Video tutorial on log, ln and 1/T. (https://youtu.be/q-gALwjPj9I)

     

    Workbooks

    Video \(\PageIndex{1}\): 2:47 video on dashboards in a workbook  developed by Lilian Poirot (https://youtu.be/37GQkl-Ao_Y)

    Workbooks are a way of organizing your google sheet. A workbook has a tab for each section of data and a dashboard to summarize the results. This helps to be able to understand your data at a glance, while also keeping it all in the same place.

    Dashboard

    The Dashboard You can see at a glance the important information from your workbook. This is an especially powerful tool when used with a live stream of data to your sheet. All the calculations and data can be behind the scenes in tabs, with your dashboard giving all the info you need at a glance. 

    clipboard_ebe58f793c63c8c10c14c459cc55ca453.png
    Figure \(\PageIndex{1}\): An example of a dashboard used for monitoring Indoor Air Quality. (Copyright; author via source)

    Tabs

    The basics of tabs is covered in Google Sheets Basics 4.1-3. Each Tab (also called sheet) can have a section of data or calculations. This is where the real work is being done in the sheet. One of the benefits of using tabs over having everything in the same tab is this allows you to build for future data. If everything is in one tab you most likely will put several things in the same column or row. This works for that project but if you want to add more data theres no place to add it.

    Building workbook template from other sheet

    You may thing of a sheet as something you use once for a project, then it will sit in your drive to never be used again. But even if a certain project is over doesn't mean you're done with that sheet! It can be used to build a template to be used in another project.

    Set up a sheet to be a template:

    Google Basics 4.1-3 goes in depth on tabs but for a quick overview is at  TimeStamp: Video 2: 0:15

    To copy and paste data, click and drag to highlight the range. You can right click then select copy or press ctrl+c on your keyboard. Click the cell the first cell where you want to move the data and paste. (right click paste or ctrl+v) You can paste without formatting (fonts or colors, etc) by using ctrl+shift+v or right click, paste special, paste values only  TimeStamp: Video 2: 0:22

    You can set the Header by clicking and dragging the gray bar in the top right corner. This freezes the first row so it stays at the top as you scroll through data. It also tells the sheet that the first row is our labels, not data. TimeStamp: Video 2: 0:40

    Tips for building for future data

    Keep one type of data or calculation per column.

    In Figure 2 there are two headers in multiple columns. While this works for some projects where a compact view is most important, to add more data you would have to add more rows and rearrange the whole sheet. Also any charts used would have to be altered for the new range.

    Figure 3 shows a sheet where everything has a unique column. This also has the benefit of being able to label the first row as header. This is useful for charts because it will automatically label this row as the labels. Also when set up this way you can select the entire column as the range for the chart so any new data will automatically be graphed

    clipboard_ebd4da102c6772798c85eb9a6c39a0311.png
    Figure \(\PageIndex{2}\): Example of a stacked column sheet, can't add more data without changing entire sheet
    clipboard_edd383c457ed62759f6edc8a9b51930c7.png
    Figure \(\PageIndex{3}\): Example of sheet that allows to add any amount of data.

    Fill Down Square

    The fill down square is the blue square you see in the bottom right corner of your select cell. You can click and drag this to copy and paste faster.

    A single click then drag will quickly autofill the column based on the pre-existing pattern. Drag to highlight all of the cells you want to repeat the data or formula.

    If you have data in an adjacent column you can double click the blue square instead of clicking and dragging and it will fill in the cells that have something next to it.

     

    Video \(\PageIndex{2}\): 4:43 video on how build a template from another sheet developed by Liliane Poirot (https://youtu.be/STpyDTTLS38)

     

    Conditional Formatting

    Conditional formatting is one of my favorite tools in google sheets. You can set the sheet up to change text or background color of a cell based on a set of conditions. This makes a google sheet easy to read at a glance. Once you understand the basics of using conditional formatting its fairly easy to find a lot of new ways to use it.

    Video \(\PageIndex{3}\): 4:58 video on conditional formatting developed by Liliane Poirot (https://youtu.be/J9Xmp20jEZM)

    Access Conditional Formatting

    1. Highlight the cells or column you want to format
    2. On the menu select format then conditional formatting
    3. This will open the toolbar to the right of your screen

    Using Conditional Formatting Toolbar

    There are two types of "rules" in conditional formatting

    Single Color

    This rule is used to highlight all cells that follow the condition you set.

    1. First check or set the range you want to apply the rule to under Apply to range. (To set an entire column don't include numbers, for example B:B)
    2. Next we set the format rules. The dropdown for "Format cells if..." has lots of options for rules to set and ways to make custom formulas.
    3. Formatting style is how the cell will change if it follows the rule. You can highlight the cell by clicking the paint can and change the font color by clicking the A drop down.
      1. Choose colors that will be readable and accessible for anyone using your sheet. Carnegie Museum's guidelines is a great overview to get you started.
    4. Press Done to save your rule or click add another rule to make another rule on the same range

    Color Scale

    This rule can be used to visually compare a set of cells. You can apply a gradient of colors to a range. This is very useful for large amounts of data or where you want to track changes.

    1. First check or set the range you want to apply the rule to under Apply to range. (To set an entire column don't include numbers, for example B:B)
    2. Next we set the format rules, click the colored box under preview to choose from the default color scales.
    3. The default settings (Min value for Minpoint and Max value for Maxpoint) will automatically detect the min and max of your data and assign the color scale based on the range.
    4. You can change the colors from the default by clicking the paint buckets next to the point boxes.
    5. The first two rows of options let you do a gradient of two colors. After selecting your colors sheets will automatically generate the other three middle colors. The last row of options lets you make a gradient of three colors.
      1. One criticism I have for google is that the default colors for the three colors are not accessible. The US Standards Website reports that approximately 4.5% of the total population have some kind of color insensitivity. Red/green color blindness is the most common form color insensitivity. That is a large amount of people who would be unable to read your sheet! Blue/Orange is generally a good choice. Venngage is another great resource with visuals on what colors look like under different forms of color blindness and some sample color palettes to use. 
    6. Press Done to save your rule or click add another rule to make another rule on the same range

    Charts

    Charts are very useful in google sheets.

    Video \(\PageIndex{2}\): 3:49 video developed by Liliane Poirot on inserting and editing a chart (https://youtu.be/201tlRiHmw0)

    Insert a Chart

    To insert a chart first highlight the cells you want in your chart

    If you highlight the columns this will use all data in those columns which is useful if you add more later

    Next click Insert then Chart

    Edit a Chart

    To edit a chart click the three dots in the right hand corner of the chart, this will open the chart tool bar to the right.

    Setup lets you choose the type of chart and the other basic set up for the chart. Figure 1 below explains the different options in the setup menu.

    clipboard_e98196cfe2273b2d35ffeaaeeff892d49.png
    Figure \(\PageIndex{1}\): Labeled Chart Editor Toolbar (Poirot)

    You can also customize a chart using the customize tab. This lets you change the look of a chart.

    Chart & Axis Titles

    You can change the title of the chart or the x and y axis of a chart. If you have Use row 1 as headers checked the sheet should automatically use your first row as the title for the axis.

    To change the title you can go to the chart toolbar then customize and select Chart & Axis Titles.

    You can switch between the Chart Title, the Horizontal Axis (x), and the Vertical Axis (y) and change the title text.

    Alternatively you can change the text by double clicking on the title you want to change

    clipboard_e46fd2bf131e40b75ea99bd2946fd3df5.png
    Figure \(\PageIndex{2}\):Changing the title of a chart or axis. (Poirot)

     

    Equation of a Chart

    You can add a trendline to get the equation for your graph. To add a trendline go to the chart toolbar then customize and select series.

    Scroll down and check off trendline. Use type to select the type of chart then go to label and select Use Equation.

    Type Description Equation
    Linear For data that closely follows a straight line. \(y = mx+b \)
    Exponential For data that rises and falls proportional to its current value. \(y = A*e^{Bx} \)
    Polynomial For data that varies \( ax^{n} + bx^{(n-1)} + … + zx^{0} \)
    Logarithmic For data that rises or falls at a fast rate and then flattens out. \( y = A*ln(x) + B\)
    Power series For data that rises or falls proportional to its current value at the same rate \( y = A*x^{b} \)

     

    clipboard_ea1f07595b149e6c0003e4eca2a0ba751.png
    Figure \(\PageIndex{3}\): Add a trendline and equation. (Poirot)

     

     

    Robert E. Belford (University of Arkansas Little Rock; Department of Chemistry). The breadth, depth and veracity of this work is the responsibility of Robert E. Belford, rebelford@ualr.edu. You should contact him if you have any concerns. This material has both original contributions, and content built upon prior contributions of the LibreTexts Community and other resources, including but not limited to:

    • Liliane Poirot

    Appendix 3: Tips on Google Sheets is shared under a not declared license and was authored, remixed, and/or curated by LibreTexts.

    • Was this article helpful?