# Appendix 3: Tips on Google Sheets


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 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

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

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

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.

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 =

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 ()

#### 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.

### 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

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.

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

#### 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}$$

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.