Favorite topics

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

Vlookup is used to search for information in a table built vertically (your table must be in ABC order). It searches for a value in the leftmost column of a table , and then returns a value in the same row from a column number you specify in the formula.

=VLOOKUP(A6,A10:B23,2,TRUE)

In this example, A6 is the criteria cell (holds the data you are looking up in the table), A10:B23 is the table (where you want to look up information), 2 is the 2nd column in the table (where you want to find specific data in the table), TRUE means that we do not want an exact match.

=VLOOKUP(A6,A10:B23,2,FALSE)

For an exact match when looking up text or specific numbers, use the forth argument "False."

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.