How to improve your Excel skills

SUM and AutoSum Function in Excel

SUM in Microsoft Excel, is a function that allows you to add individual ranges, cell references, values, or all three. This is a very basic excel skill to have while allowing you to save a huge amount of time. To add this to your list of excel skills, follow these steps:

1. Select a cell next to the values you want to add.
2. Type =SUM(.
3. Select or highlight the cells you want to add.
4. Type ) then press Enter.

Another way to add numbers in a row or column is to click on the AutoSum button on the Home tab, select the cells you want to add, then press the Enter key.

COUNT and COUNTA Function in Excel

What is the COUNT function in Excel?

This intermediate Excel function lets you determine how many cells in a spreadsheet contain numbers.

Here’s how to use the COUNT function in Excel:

Click on an empty cell and type in the formula =COUNT(B1: B50).

In this example, Excel will check column B, rows 1 to 50, for any numbers. If there are 25 cells with numbers, Excel will return a value of 25. The COUNT function only looks at numbers and will ignore text. If you use COUNT in a column full of text, it will return a value of zero.

COUNTA vs COUNT function in Excel

The COUNTA function is similar to COUNT, but it looks at cells containing text. So if you want to count how many cells in a column contain text, simply type this formula into an empty cell: =COUNTA(P1: P25). If there are five cells with text under that range, Excel will return a result of 5.

Pivot tables in Excel

What are pivot tables?

A pivot table lets you summarize large amounts of information, analyze related totals, and present detailed reports in a simplified and user-friendly way.

For example, you want to determine how many sales each person in your team has made during the past six months. By creating a pivot table in Excel, you can easily display all the data grouped together under different categories (a task that would be time-consuming if you were just using functions and formulas).

This kind of advanced Excel skill is very sought after by recruiters so we strongly advise learning how to use pivot tables.

How to create a pivot table in Excel

Here are the steps for creating a pivot table:

1. Select the data on the spreadsheet you want to include in the table.
2. On the Excel ribbon go to Insert > Tables > Pivot table.
3. A Create PivotTable dialog box will appear, with the data range you selected. You can manually edit the data range as you see fit. This dialog box will also give you the option to create the pivot table in a new or in an existing worksheet.
4. Once you’re done editing, click OK.
5. After clicking OK, a blank table will appear along with a PivotTable Fields dialog box. In this box, you can select which fields in the data range you selected earlier will appear in the table.

You can check out this short video if you want to learn more about creating a pivot table.

Filtering in Excel

Filtering is a basic Excel skill allowing you to temporarily hide irrelevant data so you can focus on the ones you need. This feature is useful if you’re working on a spreadsheet with a large amount of data. For instance, you want to know how many students in Section 3 of your class got a midterm score of 90 and above. You can have Excel filter out the students in Section 3 who scored below 90, leaving you with a list of those who got a score of 90 and above.

Here are the steps to use filtering in Excel:

1. Highlight the data set you want to filter. This should include the headers of the chosen columns.
2. On the Excel ribbon, go to Data > Filter. A dropdown arrow will appear on the headers of the column you’ve selected.
3. You can use the dropdown menu to filter the data by text or number depending on the conditions you set.

Check out this video clip about filtering data on a spreadsheet.