How to Use Excel: 14 Simple Excel Shortcuts, Tips & Tricks
Sometimes, Excel seems too good to be true. All I have to do is enter a formula, and pretty much anything I’d ever need to do manually can be done automatically. Need to merge two sheets with similar data? Excel can do it. Need to do simple math? Excel can do it. Need to combine information in multiple cells? Excel can do it.
If you encounter a situation where you need to manually update your data, you’re probably missing out on a formula that can do it for you. Before spending hours and hours counting cells or coping and pasting data, look for a quick fix on Excel — you’ll likely find one.
In the spirit of working more efficiently and avoiding tedious, manual work, here are a few Excel tricks to get you started with how to use Excel. (And to all the Harry Potter fans out there … you’re welcome in advance.)
How to Use Excel
1) Pivot Tables
Pivot Tables are used to reorganize data in a spreadsheet. They won’t change the data that you have, but they can sum up values and compare different information in your spreadsheet, depending on what you’d like them to do.
Let’s take a look at an example. Let’s say I want to take a look at how many people are in each house at Hogwarts. You may be thinking that I don’t have too much data, but for longer data sets, this will come in handy.
To create the Pivot Table, I go to Data > Pivot Table. Excel will automatically populate your Pivot Table, but you can always change around the order of the data. Then, you have four options to choose from.
- Report Filter: This allows you to only look at certain rows in your dataset. For example, if I wanted to create a filter by house, I could choose to only include students in Gryffindor instead of all students.
- Column Labels: These could be your headers in the dataset.
- Row Labels: These could be your rows in the dataset. Both Row and Clumn labels can contain data from your columns (e.g. First Name can be dragged to either the Row or Column label — it just depends on how you want to see the data.)
- Value: This section allows you to look at your data differently. Instead of just pulling in any numeric value, you can sum, count, average, max, min, count numbers, or do a few other manipulations with your data. In fact, by default, when you drag a field to Value, it always does a count.
Since I want to count the number of students in each house, I’ll go to the Pivot Table and drag the House column to both the Row Labels and the Values. This will sum up the number of students associated with each house.
Still feeling lost? Check out our dedicated blog post about creating pivot tables here.