Microsoft Excel is a popular office application used by countless businesses around the world. Unfortunately, many employees are not using it to its full potential. If you’re struggling with Excel, these tips will help improve your spreadsheet skills.
SUM and AutoSum
SUM is a function that allows you to add individual ranges, cell references, values, or all three. To do this, follow these steps:
- Select a cell next to the values you want to add.
- Type =SUM(.
- Select or highlight the cells you want to add.
- 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. Here’s a short video with examples of how to use SUM and AutoSum.
COUNT and COUNTA
The COUNT function lets you determine how many cells in a spreadsheet contain numbers. To use this function, 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.
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
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. Using a pivot table, 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).
Here are the steps for creating a pivot table:
- Select the data on the spreadsheet you want to include in the table.
- On the Excel ribbon go to Insert > Tables > Pivot table.
- 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.
- Once you’re done editing, click OK.
- 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.
Filter
Filtering allows 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:
- Highlight the data set you want to filter. This should include the headers of the chosen columns.
- On the Excel ribbon, go to Data > Filter. A dropdown arrow will appear on the headers of the column you’ve selected.
- 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.
Mastering these features will significantly improve your Excel skills and your productivity. To find out more about Microsoft Excel, give our experts a call today.
