13 Excel skills every beginner should learn first

With monthly sales in B2:B6, click the cell under the list and type =SUM(B2:B6) to get the total. Use =AVERAGE(B2:B6) for the average monthly sales.

To find your best and weakest month, use =MAX(B2:B6) and =MIN(B2:B6). In the video the max is 225 (April) and the min is 150.

To see every formula in the sheet instead of the results, press Ctrl + ` `` (the grave accent key, left of 1). Press it again to switch back.

Go to File > Save. Excel suggests OneDrive by default because Office is signed in to your Microsoft account. Under Other locations, click Browse to save to any folder on your computer instead.

To make your computer the default, go to File > Options > Save and check Save to Computer by default. You can also change the default local file location (it starts as the Documents folder).

Select the data, then on the Home tab click Format as Table and pick a style. Keep My table has headers checked if your first row is a header row, then click OK.

The Table Design tab appears when you click inside the table. From there you can:

• Rename the table in the Table Name box
• Check Total Row to add a total under the numbers
• Click Insert Slicer, pick a column such as Region, and click OK

Click a slicer button (for example Central) to show only those rows. The total row updates to match the filtered rows.

Go to Home > Sort & Filter > Filter. Arrows appear on each column header.

• To sort, click the arrow on Purchase Amount and choose Sort Smallest to Largest.
• To filter by value, click the arrow on Product, uncheck Product B and click OK to see only Product A buyers.
• For a custom filter, click the arrow on Purchase Amount, choose Number Filters > Greater Than, enter 10 and click OK.

Number Filters also offers Less Than, Between, Equals and more. Use Clear Filter on a column to show everything again.

Select your data and go to Insert > PivotTable. Choose New Worksheet and click OK.

In the field list, drag Region to Rows, Product to Columns and Sales Amount to Values. You now see total sales for each product in each region.

Use the Row Labels arrow to filter to one or more regions or to sort A to Z. To show averages instead of totals, click Sum of Sales Amount in the Values area, open Value Field Settings, choose Average and click OK. Choose Count to see how many sales each group has.

Select the Sales column and go to Home > Conditional Formatting:

• Top/Bottom Rules > Top 10 Items highlights the ten highest sales.
• For the Profit column, choose Data Bars and pick a style. Negative profits show in red and positive in green. Widen the column so the bars are easy to read.
• For the Quantity column, choose Highlight Cells Rules > Greater Than, enter 5 and pick a red border format.

To find an employee's salary from their ID, type the ID (for example 105) in an empty cell. In the result cell, type =VLOOKUP(, then:

• Click the cell with the ID (the lookup value)
• Select columns A:E as the table array (employee ID through salary)
• Enter 5 as the column index, because salary is the fifth column
• Enter FALSE for an exact match

The finished formula looks like =VLOOKUP(H2,A:E,5,FALSE) where H2 is the cell holding the ID. Change the ID to 112 and the salary updates.

With actual sales in column D and the sales target in column C, type =IF(D2>=C2,"Met","Not Met") and drag it down the rows.

For more than two outcomes, nest a second IF inside the first: =IF(D2>=C2,"Excellent",IF(D2>=C2*0.8,"Good","Needs Improvement")). This returns Excellent when the target is met, Good when at least 80% of it is met, and Needs Improvement otherwise. Close one bracket for each IF.

Select the Department column and go to Data > Data Validation. Under Allow, choose List and enter HR,Marketing,IT,Sales. Click OK. Each cell now has a dropdown, and typing anything else shows an error.

For the Salary column, open Data Validation again, choose Decimal, set the condition to less than and enter 20000. Now 21000 is rejected and smaller numbers are accepted.

Go to File > Print to preview the page, choose the printer and set the number of copies. If a column is cut off, go back and use the Page Layout tab:

• Margins: Normal, Wide, Narrow or Custom Margins
• Size: pick your paper size (A4 in the video; Letter is the usual US size)
• Orientation: Portrait or Landscape

If the data still does not fit, go to View > Page Break Preview and drag the blue page line to the right so all columns sit on page 1. Check File > Print again.

To join first and last names, use =CONCATENATE(A2," ",B2). The " " adds the space between the names; without it they run together.

Use =LEFT(A2,1) to pull the first letter of the first name (an initial). =RIGHT(A2,1) takes the last character instead. Change the number to take more characters. Drag each formula down to fill the column.

Go to View > Freeze Panes and choose Freeze Top Row. The header stays visible as you scroll down a long list.

If you have many columns, choose Freeze First Column so the first column stays in view as you scroll right. Freeze Panes freezes both rows and columns above and left of the selected cell.

Right-click a cell and choose New Comment, type your comment and click the Post button. People you share the file with see it when they hover over the cell, and they can reply in a thread. Each comment and reply shows who wrote it.

For a simple note, right-click and choose New Note. Notes also appear on hover, but nobody can reply to a note.