Excel Skills Written Instructions (Mac)

Excel Skill #1: How to Create a Simple Graph

(Mac Version)

1. Select your data (including the titles of your rows and columns) by clicking and dragging across

the cells your data is in OR by holding down the shift key and using the arrow keys to select the

appropriate cells.

2. Head to the Charts tab OR go to Insert > Chart from the top navigation. From here, you’ll be able

to choose from a variety of different charts and graphs. (For our example, we chose the 2-D

clustered column graph.)

3. If you don’t like the way Excel initially displays your data, you may want to swap your rows and

columns. You can do this by right clicking (or by holding the control key and clicking) on your

graph, choosing Select Data, and then clicking Switch Row/Column.

4. To add a title to your graph, make sure your graph is selected, head up to the Chart Layout tab,

click Chart Title, and then choose where you want the title to appear. (For our example, we

chose Title Above Chart.) You can then click inside the text box that appears and replace “Chart

Title” with the title of your choosing.

5. To label your graph’s x- and y-axes, select Axis Titles from the Chart Layout tab, and then select

how you’d like your Horizontal Axis Title and Vertical Axis Title to appear. (For our example, we

chose Title Below Axis for our horizontal axis and Rotated Title for our vertical axis.) You can

then click inside the text boxes and replace “Axis Title” with the titles of your choosing.

6. If you’d like to reorder how your data is displayed in the graph (for example, maybe you want to

have your values in order from largest to smallest), select the columns that your data is stored in,

head to the Data tab, and click Filter. Next, head back to one of your columns, click on the little

upside-down triangle that just appeared next to your column title, and choose from a number of

filtering options. (For our example, we chose Descending so the values would be ordered from

largest to smallest.)

Click here to learn more about creating charts in Excel >>

Excel Skill #2: How to Create a Pivot Table

(Mac Version)

1. Click anywhere inside your data array, navigate to the Tables tab, and click New. (Alternatively,

you can go to Insert > Table via the top navigation).

2. Select the Summarize with PivotTable option from the Tables tab and then click OK on the pop-

up menu.

3. You can now organize your data points by dragging and dropping specific fields (which are

labeled according to the names of your columns) into different areas. For this example, we want

to organize our data by post title. To do this, click and drag the “Title” field to the Row Labels

area.

4. Now you can summarize all of the metrics you want to calculate by adding fields to the Values

area. For this example, click and drag “Views,” “Links,” and “Comments” to the Values area.

5. The sum of a particular field will be calculated by default, but you can easily change this to

average, maximum, minimum, etc. depending on what you want to calculate. To do this, just click

on the small

next to each value and select the option you want. (For this example, we chose

Average). Click OK once you’ve made your selection and your pivot table will be updated

accordingly.

Click here to learn more about creating pivot tables in Excel >>

Excel Skill #3: How to Do a VLOOKUP

(Mac Version)

1. Take the pivot table you made in the last video, copy everything from “Title” down to the last row

of data before “Grand Total”, and paste the values into a new sheet. (Tip: You can hold down the

shift key and use the keyboard shortcut “Fn-Down Arrow” to quickly extend your selection down

the page. You can also use the shortcuts “Command-C” and “Command-V” to copy and paste

respectively.)

2. For this example, “Title” is our unique identifier, and our objective is to match each title to its

author. To do this, start by going to the first empty cell in the first row (cell E1, in this case) and

label it “Author.” Next, select the first cell in your newly created “Author” column (cell E2) and click

on the formula button (

) up in the top navigation.

3. Double click on VLOOKUP in the Formula Builder window. (Note: You can simply type “V” in the

function search field to find VLOOKUP, or you search for it in manually – it’s under the Lookup &

Reference category.)

4. When you see the lookup_value field appear, click on cell A2. Alternatively, you can type “A2”

into the empty lookup_value field. This is the value that you’re trying to find a match for.

5. Next, Excel will ask you for the table_array, which is the place where Excel can find your data.

For this example, your data is in the “Pages” tab of your workbook. So, head to the “Pages” tab

and select the columns you want Excel to sort through. For this example, you can select columns

B through K. An easy way to select these columns is by clicking on the “B” above the first row

(which selects the entire B column), holding shift, and using your arrow keys to select all of the

other columns, including K. Alternatively, you can type “Pages!B:K” into the empty table_array

field.

6. Next, Excel will ask for a col_index_number, which is the number of the column in your table

array where a matching value can be returned. Since “Author” – the value we’re searching for --

is the 10th column from the left in our table array, type “10” into the empty col_index_number

field. (Note: This might seem counterintuitive, since Excel labels columns using letters, not

numbers. But trust us: for col_index_number, you want to use the number of the column.)

7. Finally, when range_lookup appears, type “FALSE.” “FALSE” indicates that you want an exact

match, as opposed to an approximate match. (Note: The finished formula will read

=VLOOKUP(A2,Pages!B:K,10,FALSE) in the formula bar.)

8. Hit enter, and your first author will appear. To carry the function down the entire column, select

the cell that has your first author in it (E2) and double-click on the little box in the bottom corner.

Click here to learn more about using the VLOOKUP function in Excel >>

Excel Skill #4: How to Use the IF Function

(Mac Version)

1. For this example, we’re going to use the IF function to organize the number of landing pages

customers have created into the ranges “10 or less,” “11 to 50,” and “51 to 100.” To start, select the

first empty cell in the first row of data (in this case, cell C3, right beneath “Range”), and then click

the formula button (

2. Double click on the IF function, which you can find by typing “IF” into the search field or by

searching for it under the Logical category.

3. Hop into the formula editor up top. (Note: If you wanted to make a very simplistic IF statement,

you could use the Formula Builder window, which is what we used to perform our VLOOKUP.

However, since – in this case -- we want to organize values into multiple ranges, we’ll need to

build a “nested” IF statement, which means it has multiple conditions.)

4. Set the first condition of the nested IF statement, which says that if the value in cell B3 (our first

cell with data) is less than 11, it gets designated as “10 or less.” To express this, type the bolded

text below into the formula editor:

=IF(B3<11,"10 or less"

5. Next, set the second condition, which says if the value in cell B3 is less than 51, it gets designated

as “11 to 50.” To do this, add the bolded text below to your formula:

=IF(B3<11,"10 or less",IF(B3<51,"11 to 50"

6. Finally, set the final condition, which says if the value in cell B3 is less than 100, it gets designated

as “51 to 100.” To do this, add the bolded text below to your formula:

=IF(B3<11,"10 or less",IF(B3<51,"11 to 50",IF(B3<100,"51 to 100"

7. We now need to close the parentheses for all three conditions, so add those closed parentheses

to the end of the formula. The final formula will look like this:

=IF(B3<11,"10 or less",IF(B3<51,"11 to 50",IF(B3<100,"51 to 100")))

8. Hit OK and your first range will appear in cell C3. Double click the little box in the corner and

ranges will appear for all of your entries.

Click here to learn more about using the IF function in Excel >>