# Entering and Viewing Data in Excell 2011 (Mac)

Entering/Viewing Data

Entering fractions, inserting Vlookup tables, plotting a "best fit" line, sort data, and view formulas

## Entering Fractions

Have you ever entered a fraction in an Excel cell and had it appear as a date? This is not a bug. The problem occurs simply because, in some cases, Excel has no way of knowing whether you want to enter a date or a fraction. For example, if you enter a whole number plus a fraction (1 5/8, 2 1/4) Excel knows you want a fraction. But, if you enter 3/64, Excel will think you want to enter March 1964. If you enter 3/6, Excel will display March 6.

To make sure Excel sees your entry as a fraction, you will need to format the cell.

1) Select the cell or cells that you want to format

2) Go to the Home tab

3) Go to the Number group

4) Click on the dropdown menu and choose "Fraction"

In the cell(s) you just formatted type in your fraction.

## Inserting Vlookup Tables

## Inserting a Line of Best Fit

After creating a scatter chart in Microsoft Excel, a best fit line can be found as follows:

1) Be sure you are on the worksheet which contains the chart you wish to work with.

2) Move the mouse cursor to any data point and press the left mouse button. All of the data points should now be highlighted.

3) Now, while the mouse cursor is still on any one of the highlighted data points, right click (or ctrl + click) on "Add Trendline" from the menu that appears.

4) From within the "Format Trendline" window, in the "Type" section, click on the box with the type of fit you want (e.g. Linear).

5) Click in the checkbox next to "Display Equation on Chart" and the checkbox next to "Display R- squared Value on Chart". Do not click on the checkbox next to "Set Intercept = 0".

6) Click OK

A line, an equation, and an R-squared value should appear on the graph.

## Sorting a Worksheet

1) Select your worksheet by hitting Ctrl-A on your keyboard

2) Go to the "Data" tab

3) Go to the "Sort & Filter" group

4) Click on the drop down arrow next to "Sort"

5) Either pick one of the options and be done, or click on "Custom Sort"

6) If your worksheet has a header row check the box next to My data has headers

7) Choose which column you want to sort by clicking on the drop down arrow next to Sort by

8) Choose if you want to sort A to Z or Z to A

9) Click OK

### If you would like to sort on two items, for example last name and then by first name:

1) Follow steps 1-7 above

2) Click on the "+" sign (Add Level)

3) Add your sort criteria

4) Click OK

## Viewing All Formulas in a Workbook Sheet

When you are handed someone else's workbook to work on the first thing you need to determine is what cells in the spreadsheet contain numbers and what cells contain formulas. Since the result of a formula shows up as a number, it's hard to tell.

1) Press Ctrl + ` (key located in the top left corner of your keyboard). This will open up your columns so that you can view your formulas.

2) Print the workbook to have the documentation

3) Press Ctrl + ` again to return it back to normal.