By Does coffee flowing at all hours of the day, meetings on top of meetings and not enough time to go to the bathroom (let alone get your work done) sound familiar to you? If yes, then these simple Excel tips are going to be your best friend. They may even free up enough time for an extra bathroom break!
1. Grouping Feature in Pivot Tables
The grouping feature is one of Excel’s B-List celebrities— most people have heard of it, but it never makes the headlines. It’s about time for its 15 minutes of fame. Data that needs to be rolled up into weekly, monthly or yearly summaries can be conjured up in 10 seconds. Just create a pivot table with rows summarized by date. From there, you can right click on one of the date cells to group by day, month or year.
Unfortunately, if you’re using Google Spreadsheets, this grouping feature isn’t available. Another option is to add a column in your raw data that filters out month or whatever you want to summarize by (see example below using formula =TEXT(H2, “yyyy-mm”)), and then create a pivot table using month, instead of day, for the rows.
2. Data Validation
People who like spreadsheets generally like things to look clean and organized. It’s a special kind of OCD.
Luckily, Excel caters to these types of people. Data Validation is one of the best tools to keep your spreadsheet looking clean and your well-meaning but messy co-workers in line. Essentially, it allows you to create rules for a set of cells. Here are a few examples of rules you can set up:
- Create a drop down menu in a set of cells to limit inputs to a set list ie. only allow products currently being sold to be chosen in a detailed sales summary
- Limit the inputs in a set of cells to only include a certain number value ie. only include positive numbers, or only include numbers with one decimal
- Limit text length in cells to keep reports concise
The best thing about data validation is the ability to edit the error message. Creativity makes spreadsheets more fun.
3. Conditional Formatting
This function isn’t just to make cells look pretty, although that is a definite bonus. Conditional formatting can highlight duplicates, outliers and make it easier to filter. It can even change boring old TRUE and FALSE returns into snazzy check marks and Xs. There’s something about a BIG RED X that jumps out at you that FALSE just can’t manage, despite the all-caps.
4. Keyboard Shortcuts
Here are a few to get you started, and if you’re one of those people who hate to touch their mouse at all, here is a blog from Excel Jets to help you speed through your excel sheets.
CTRL + SHIFT + ↓ - If shortcuts scare you, this is the perfect one to start with. It will save you hours of dragging your mouse down pages to highlight data, and cursing when you inevitably scroll too far or bounce back to the top. Remove the shift, and this will take you to the end of your data without highlighting. So, when you’re lost in the jungle of 50,000 rows, this can save a lot of time.
CTRL + HOME - This is easy to remember when you’re lost in that data jungle (again) because it takes you out of the jungle, or “home”. It will take you straight back to cell A1, safe and sound.
CTRL/SHIFT + SPACE - This fancy shortcut highlights the column, row or sheet you are on. CTRL + SPACE for columns, SHIFT + SPACE for rows and CTRL + SHIFT + SPACE for the sheet.
(Hint: C is for column AND ctrl - how you remember which is which)
5. Save Time on Functions
This blew my mind when I first learned about it. So, prepare yourself, and maybe take a seat if you’re standing up. Okay, ready? When you write a function you don’t have to close the parenthesis. I know, pretty crazy stuff! You wouldn’t think this would save much time, but over a week or so, I guarantee you’ll have an extra 10 minutes on your hands. At least you will if you live in Excel like I do.
Bonus!
Another thing to add to your book of excel tips—not quite as mind blowing, but still cool—lies in the advanced excel options (File - Options - Advanced Options). Not a place you probably frequent often, but it has some good features. For example, when you’re doing a lot of work on a column or row heavy sheet, changing your enter, move selection settings can save some navigation time. All this does is change where your cursor goes after you hit enter. So, if you’re moving from left to right in a sheet, change it so the setting is “after pressing enter, move selection [right]”.
With these excel tips, you’ll be saving so much time that you’ll actually have a few extra minutes to watch that funny cat video. Congratulations! Now take that extra bathroom break, you’ve earned it.
Have any more time-saving, super savvy, or just plain fun excel tips? Let me know in the comments below!