Excel - six need-to-know tips.
When it comes to Excel, there isn’t much middle ground.
You have people who absolutely love it and will sing the praises of spreadsheets all day; everyone else detests it. In truth, Excel can be an incredibly powerful tool for combing through information and finding exactly what you need – provided you know how to use it correctly. It’s that last part that trips people up. But, fortunately, Excel isn’t nearly as complicated as you’re likely making it out to be.
In fact, there are plenty of helpful tricks and tools you can utilise—whether you’re a total newbie or an established expert. Here are six things that will certainly help you in Excel:
1. Sorting data
Typically, spreadsheets are useful for storing and sorting a whole bunch of information – such as a contact list for 200 people that you want to invite to an event. You may need to sort those people accordingly. Perhaps you want them listed in alphabetical order by last name or maybe you want to group them together by company. Excel makes it easy to comb through your entire data set and sort everything into a clean and easy to read spreadsheet.
Here’s how you do it:
- Highlight the entire data set you want to sort (not just one column!) by either dragging your cursor across all of the cells or clicking the triangle in the upper left of your spreadsheet to select the entire thing.
- Hit the “Data” tab.
- Click the “Sort” button.
- Select how you want to sort your data.
- Hit “OK.”
Then, your data will be sorted accordingly.
It’s important that you select the entire data set you want to sort and not just one column. That way, your rows will stay intact - in this case, the correct address will stay with the appropriate person. If you just selected the first column, without selected everything else (CTR/CMD+A) or expanding the section, Excel would’ve sorted only that one column alphabetically, making the addresses a mismatched mess.
2. Remove duplicates
It’s inevitable…when you’re working with a large dataset, there are bound to be a few duplicates that sneak their way in. Rather than getting frustrated by scrolling through the entire spreadsheet and looking for them yourself, Excel can do all the work for you and remove duplicates.
Here’s how you do it:
- Highlight the entire data set.
- Hit the “Data” tab.
- Click the “Remove Duplicates” button.
- Select what columns you want Excel to find duplicates in.
- Hit “OK.”
Be careful that you choose enough qualifiers to weed out the true duplicates. Use enough information so that you’re removing rows that are true identical copies of each other and don’t simply share just one similar value.
3. Basic math functions
Excel can handle all sorts of math functions for you. All you need to do is enter a few simple formulas.
Here are some basic formulas you’ll want to know:
- Addition: Type “SUM=” in a blank cell where you want the total to appear, click the cells you want to add together, and then hit “Enter.”
- Subtraction: Type “=” in a blank cell where you want the difference to appear, click the cell you want to subtract from, type “-”, click the cell you want to subtract, and then hit enter.
- Multiplication: Type “=” in a blank cell where you want the total to appear, click the cell for a number you want to multiply, type “*”, click the cell for the other number you want to multiply, and then hit enter.
- Division: Type “=” in a blank cell where you want the remainder to appear, click the cell for the number you want to divide, type “/”, click the cell for the number you want to divide by, and then hit enter.
If you want to drag the same mathematical formula across a row - after entering the formula into one cell, click that cell where the total appeared, click the little green box or + that appears in the lower right-hand corner and then drag the cell across the rest of the row where you need that formula to be applied.
4. Freeze panes
There’s nothing worse than scrolling through a huge spreadsheet that requires you to continuously go back up to the top to see what your column headers are. You can make your column headers and your row numbers visible, no matter how far down the spreadsheet you go. You can do this by using Excel’s “freeze panes” feature.
Here’s how you do it:
- Click on the row underneath your column headers.
- Click on the “View” tab.
- Click the “Freeze Panes” button.
Scroll down and across your spreadsheet and you’ll see that the information you need is always right there within view.
5. Insert current date
Put your cursor in the cell where you want the date to appear, type the shortcut Ctrl + ; and Excel will automatically fill in today’s date for you.
Remember that dates entered using this function are static, meaning they won’t change as your spreadsheet ages.
6. Make the same change across worksheets
When you’re working with multiple tabs, you can select the appropriate sheets in your workbook where multiple changes should appear. Make the change once, and it’ll be applied across all of the sheets you selected.
Here’s how you do it:
- Hold the “Control or Command” key on your keyboard.
- Select the appropriate tabs of your workbook.
- Make the necessary change to one cell.
- Check to make sure it applied across all of your worksheets.
I know that Excel can feel a little intimidating. But, once you start playing around, you’ll begin to become more and more comfortable and quickly begin to realise just how much easier it can make things for you.
Let us know if we can help further. TWC We've got IT covered.