How to Use Excel

How to Use Excel 2011 on a Mac (NOT Numbers)

Part 1 – Data Tables & Graphs

The Basics:

·  Each Excel document is called a Workbook

·  You will be using buttons from different Tabs underneath the initial toolbar at the top.

·  The Tabs are Home tab, Layout tab, Tables, Charts, and so on.

o  Clicking on the tab at the top will take to you that specific toolbar.

o  To add or take off tabs, click on the Wheel Gear on the far right, inline with the tabs, and click on Ribbon Preferences.

·  Each of the squares is called a cell

o  A cell location is given by a letter designating the column it is in and a number saying what row it is in. For example, the upper left hand cell is A1

Making a Data Table:

·  Both numbers and words can be entered into a cell.

·  To enter data, click on the cell you want to enter the data into.

·  If the word entered is longer then the cell has room for:

o  Go up to the column headings (A, B, C, ect.) move the curser so it is between the two columns.

o  The curser will change to a bar with arrows on each side.

o  Click and draw the column so that it is wide enough for the writing.

o  Columns can also be made narrower with the same method.

·  To move around in a spreadsheet:

o  The arrow keys will move you up, down, right, or left.

o  Tab will move you to the right.

o  Return or enter will move you down.

Adding the Data Table to a document (like a lab report):

·  The boxes seen on excel will not transfer to a document. So you will need to draw in a border.

·  To draw a border:

o  Highlight the data you want.

o  Up at the very top, go into the Format Menu.

o  Click on Cells… and go to the border tab.

o  Add a border and lines around each cell.

·  Another way to make a (fancy) table:

o  Highlight your data table.

o  Click on the Tables tab.

o  Under the Table Styles in the Ribbon select the table style you want (be sure you label what is in each column.

·  To add the table to a document:

o  Highlight and copy the table.

o  Find the location in the document the table goes in.

o  Paste the table.

o  The table can be centered by putting the cursor over the table. A box with arrows pointing up, down, right, and left will show up. Click on that button to highlight the table. Then click on the centering option on the Home Tab in the Paragraph section to center the table.

Making a Graph:

·  On the Charts Tab, there is a section titled Insert Charts.

·  Select the type of chart you want. In this class we will always use an “XY (Scatter)” plot without the lines. Click on the Marked Scatter. If you don’t see it, click on All and scroll down until you do (it is a ways down)

·  An empty box will show up and there should be 2 Subtabs directly after the Chart tab (Chart layout and Format).

·  To add Data to your chart:

o  Up at the very top, select the Chart Menu and then Source Data… (NOT Add Data)

o  A window titled Select Data Source should appear. In this window

o  Click the Add button on the left.

§  On the right, there is a textbox for “X values.” Next to that is a button. Select the button (This button looks different on different computers, usually a grid with a red arrow in it)

§  The window should shrink and you should be able to see your table.

§  Click and hold on the first x-value of your data. Drag the cursor to the end of your x-values. (Do not include the title, just numeric values)

·  If you did it correctly, the values for the x-axis have a shimmering dashed line around them.

·  If you did it incorrectly, go to the Edit Series window and delete the values in the textbox. Try again. Be sure you only let go of the click when only the x-values are selected.

·  When the correct data is highlighted, click on the button on the side of the Edit Series textbox. The window will expand once more.

§  Find the “Y” textbox. Click on the button next to it and do the same thing with your y-axis values.

§  If you are putting multiple data sets on your graph, this is a good time to label the data. In the Name textbox type the name of the data. Excel will use this to make a Legend for your graph.

§  If you are adding multiple data sets to your graph, click the Add button and add more data. Otherwise click ok.

·  Adding Labels to your Graph

o  Click on and select your graph. Click the Chart Layout subtab. In the Labels section your can add a title and label your axes.

o  Click on the Chart Title button and select where you want the title. The words “Chart Title” will show up on the graph. This is a HORRIBLE name for a graph. Highlight the words and change them to something descriptive that describes the data in the graph.

o  Repeat for both horizontal and vertical axes. Remember to include (units).

Making a trendline or best fit line for the graph:

·  Highlight the graph (the graph is highlighted when it has several small dots in the corners and in the middle of each side)

·  Open the Chart Layout Tab.

·  In the Analysis section of the Chart Layout Tab, there is a button for Trendline.

·  Click the Trendline button and select Trendline Options.

o  In this class we will almost always use a POWER Trendline.

o  Select Power Trendline (only available through Trendline Options)

o  On the left of the Format Trendline box that appears, select Type and then Power

o  Then go to Options and Select Display Equation on Chart (small button at the bottom of the window)

o  Click OK

·  You know have a Trendline and equation. Click on the equation and move it to where it is easy to see. If the exponent is very small you can also increase the font size (Home Ribbon)

·  If you forgot to click Display Equation On Chart, then double click on your trendline to reopen the Format Trendline box and try again.

·  To add the Graph to your lab report simply copy and paste it just like you did for the data table.