Reference and Style Guide to Microsoft
Excel
v. 1.1
Excel is a spreadsheet program that is extremely useful for data analysis. The basics are fairly simple to learn, but it has many powerful features that allow for sophisticated manipulation of data.
Table of Contents
Chapter 1: The basics
Getting Acquainted 2
Selecting Cells 3
Chapter 2: Creating Data Tables
Adding Data 4
Writing Equations 6
Copying Equations 7
Checklist for Data Tables 8
Chapter 3: Creating Graphs
Plotting Data Using Chart Wizard 7
Cleaning Up Background, Axes, And Legends 12
Checklist for Graphs 15
Chapter 4: Graph Options
Adding Trendlines 16
Adding Error Bars 17
Adding Headers and Footers 18
Annotating Plots Using Excel’s Drawing Features 19
Chapter 5: Using your Work
Previewing Your Work 20
Exporting your Data Table or Graph 21
Special thanks to the
Lakeside Science Department for their input and to my good friend, Drew Burns of
Chapter 1: THE
BASICS
Getting Acquainted
An Excel spreadsheet is divided into cells. Each cell has a unique reference formed by column location (labeled by letter) and row location (labeled by number). Further, there are a number of locations on screen of particular importance. Please note: your screen may not look exactly like this one, but it will contain many similar buttons.
Selecting Cells
There are several different cursor types:
\
Chapter 2: CREATING DATA TABLES
Adding data
Adding data is straight forward. Select a cell and start typing. However, it is important to enter your data in an organized manner.
Organizing Your Data: You should organize your data logically so that they are easy to view, manipulate and understand. Use Column A to provide information about your spreadsheet. List the title, the data, your name, and your partners. Use Column B to list your constants. Use Columns C, D, E, etc. for your data. For each of these columns, use Row 1 to label the column and include units.
WARNING: Do not include units with each individual data point. Excel will not know what to do the information.
Making it Fit: Occasionally you will enter data that does not fit inside the cell. You can adjust individual cell size by going to the FORMAT à ROWS or FORMAT à COLUMNS menu. If you wish to adjust the width of an entire column, you can simply move the cursor between the column labels and drag the border. If you move the cursor between the column labels and double click, the column adjusts to the minimum size needed to display the contents of each cell.
Formatting Your Data: Formatting your data aids in legibility and, in some cases, is essential for presenting information correctly. You can modify the format of your data either by A) selecting individual cells and going to the FORMAT à CELLS menu or B) selecting the column or row and going to the FORMAT à CELLS menu.
In the default format, Excel assumes numbers represent data and text represents comments. Occasionally, however, you need to specifically state what type of data is present in a column. From the FORMAT à CELLS menu, choose the appropriate tab. For most data, select the NUMBER tab and choose the category NUMBER. A set of options will appear including the number of decimal places displayed.
Keep formatting simple. Use the default font, size, and color unless there is a reason to change it.
HINT: Occasionally you will enter numerical data and Excel will replace your data with dates. This is a formatting issue. Change the data category to NUMBER.
Writing Equations
Writing equations is an important feature to master. Combined with the drag-and-copy cursor, it makes creating tables of information easy.
To write an equation in a particular cell, select the cell and type “=” followed by the equation. You can use the standard mathematical operations such as +, -, *, /, ^, etc. In addition, you can click the function button and will be prompted to write your equation.
Make sure when writing equations that you (A) select the correct address and (B) make the address absolute or relative as appropriate. Additionally, make sure you use parentheses as needed as Excel follows order of operations.
Copying Equations
Use the drag-and-copy cursor to reproduce equations. Variables in the equation must have the “addressing” (relative, absolute, or a combination of the two) to ensure that the values picked up in the new equation do in fact refer to the desired cells.
Checklist for Data Tables
q Column A contains title for data, date, your name, teacher’s name.
q Column B contains constants to be used in the spreadsheet.
q The first row of all data columns contains a title with units.
q All columns have widths that allow you to see data/text.
q All equations refer to correct cells.
Chapter 3: CREATING GRAPHS
Plotting Data Using Chart Wizard
Click on Chart Wizard Excel will lead you through a 4 step process.
Step 1: Choose the Chart Type The most frequently used graphs are Column graphs and XY (Scatter) graphs. When making a column graph, choose the “clustered columns” sub-type when comparing frequency of data. When making a scatterplot graph, choose the “scatter” sub-type when plotting experimental data or choose “smooth curve” sub-type when plotting results of a mathematical model.
Step 1 for Column
Graphs
Step 1 for Scatterplot Graphs
Step 2: Choose the Source Data If you selected your data before starting Chart Wizard, there is very little to do in this step. A small version of your graph will appear. Look carefully at the graph to make sure Excel is plotting your data correctly.
If you need to change the data that appear on the graph, click on the Series tab. Here you have the opportunity to change the data series on each axis, to add a new data series, and to label each data series.
Step 2: Choosing your source data (continued)
Having selected the series tab, you can modify your source data.
Step 3: Configure Chart Options Here you will label and organize the presentation of your graph. There are three tabs you need to manipulate: Titles, Gridlines, and Legend.
Step 4: Select Chart Location In most cases, it is preferable to create a new plot in a new sheet as opposed to imbedding the plot as an object in a worksheet. Make sure to give each new sheet a clear, understandable name.
Note: After you create a graph, you often find that you have some outlying points. These points can be removed by simply clearing the cells. Excel is smart enough that it will adjust the graph automatically.
Correcting My Graph
Most features of the graph can be changed by simply double clicking on the feature of interest. You can also select the graph and then go to the Chart menu and choose the Chart Wizard step (Chart Type, Source Data, Chart Options, Chart Location) that you wish to correct.
Cleaning up Backgrounds, Axes, and Legends
After creating a plot, it is important to “neaten up” the graph. Double-clicking on any feature of an Excel plot will bring up a dialogue box that will enable you to modify the appearance of the plot. You should click on the Background (Format Plot Area), the data points (Format Data Series), and the axes (Format Axis).
|
|
|
Checklist for Creating Graphs.
q Graphs refer to correct data sets.
q Graph has a descriptive title.
q Graph has axes labeled with appropriate units.
q Graph labels are present/not present as appropriate.
q Gridlines are turned off.
q Graph does not have a gray background.
q Graph is on a separate sheet.
q If there is a trendline, its equation and R2 value are displayed (see Chapter 5, Adding Trendlines).
q Graph has an annotation explaining its purpose (see Chapter 5, Annotating Plots).
q If the graph has error bars, the bars reflect the correct type (percentage or custom) and refer to the correct cells (see Chapter 5, Adding Error Bars).
Chapter 4: Advanced Graph Features
Adding Trendlines
To add a trendline to a plot, select the data series by clicking once on one of the data points. Then go to Chart à Trendline menu to get started. Choose the appropriate trendline from the options available in the Add Trendline dialogue box. Note: Logarithmic, Power, and Exponential trendlines are only available if you do not have a (0,0) data point.
Adding Error Bars
Excel can add error bars to any column graph or XY (scatter) graph. To do so, click on the data point on your graph. Then go to Format àSelected Data Series and select the error bars tab. Both the X and Y error bars will be available for scatterplot graphs.
You have several options for error amount. We commonly use percent error or custom error.
Adding Headers and Footers
If you intend to print out a plot as part of a lab report, be sure to add headers and footers to the plot. Go to the View à Header and Footer menu. The headers should include your name, the instructor’s name, the course title and period, and the title of your lab and date.
Annotating Plots Using Excel’s Drawing Features
When Excel opens, often there is a Drawing toolbar along the bottom. If the toolbar is not present, select View à Toolbars à Drawing to bring it up. This toolbar has a number of useful items that can used to annotate plots.
Annotation comments can help explain important parts of the plot and point out an interesting feature of the data. The three main features to use are Autoshapes à Callouts, Autoshapes à Lines, and Text Box.