Excel is a great tool to utilize when trying to strategize, build, or work on your business. But the truth is, if you don’t know the quick shortcuts, building an Excel document can be an all day project. And let’s face it, even I don't like to stare at Excel spreadsheets all day, and I’m an accountant. With Kashoo, you can export any of your data to Excel so these tips will help you slice and dice your data any way you want.
To help boost your productivity, and keep you out of the spreadsheets, here are 7 Little-Known Excel Tricks you can start implementing today to knock out your business’ to-do list.
Ready. Set. Go.
Trick #1 - Switch From Different Workbooks (Alt + Tab)
If you want to jump from different open Excel workbooks, Alt + Tab becomes your best friend. Just open two Excel workbooks, name them differently, and hold down the Alt key and then the Tab key. You will see your screen jump from one workbook to the next.
If you want to get even fancier, hold down the Alt key and the Tab key to show you every window that is open, and you can click through (using the Tab key) each opened program. This will save you a few extra minutes each hour when you don’t have to close a program, scroll to the bottom and click on the next program. Just Alt +Tab it!
Trick #2 - Paste Values (Alt + E + S + V)
Yes, this is an Excel trick that will come in handy if you constantly are trying to Paste Values in your workbook. Copy the cells you wish to paste values for, click on where you want to paste those values, hold down the Alt key then the E key, the S key, and then the V key, and you will see your cells pasted (values only).
I know this sounds a little intense so here is a Video from “Excel is Fun” to show you exactly how to use this shortcut to paste values.
Trick #3 - Show Duplicate Values (Conditional Formatting)
Let’s say you have a large spreadsheet with a list of names and email addresses on them (essentially your customer list). But what if you want to know if your customer list has been duplicated in any way (22 customers named John Smith). The best way to figure this out is to set a conditional formatting for duplicate values.
Ok, I know that might sound a little foreign if you have never used conditional formatting before so let me break it down even further. Conditional formatting is a feature in Excel, which allows you to apply a format to a cell or a range of cells based on certain criteria. Hence, creating the opportunity for you to set a duplicate values format on your cells.
Here is a tutorial on Duplicate Values from “Teach Excel”.
Trick #4 - Subtotal Function
The Subtotal function in Excel can be an amazing feature because it will save you a ton of time or wind up costing you the entire day if you don’t do it correctly.
With Excel’s Subtotal function, you have the option to take a large spreadsheet and break it down by sections (i.e. Subtotal the values by Date or Year) to help give you a better picture on analyzing your data. But, and I mean BUT, be careful when trying to insert your Subtotal function because it might crash your Excel and ruin any unsaved work that you have yet to complete.
Instead of trying to describe it or out of fear that it will crash your computer, check out this easy to follow Subtotal function Excel tutorial by Excel Ninja.
Trick #5 - Filter by Color (Conditional Formatting)
Going back to Trick #3, you can use conditional formatting for other features besides finding duplicate values. Let’s say you want to highlight all values over $500, and you want to show them at the top of your spreadsheet. This can be done using the Conditional Formatting Feature (Greater Than Rule), plus the awesome filter feature in Excel that allows you to filter by color.
To get started, head over to the column or cells you wish to highlight. Then hit Conditional Formatting -> Cells Greater Than -> Enter $500. When you hit enter, you will see the list of cells you highlighted, now have all cells over $500 highlighted in a pink color. From here, all that is left is to add a filter on your cells (click the Filter button located at the top right hand corner of your Excel ribbon) and you will see Filter by Color offered as an option for you now.
Here is a video by “Business Management Daily” showing you how to filter by color.
Trick #6 - Shortcuts for Cell Selections
If you want a quick way to highlight certain cells or move throughout your workbook faster, here are brief shortcuts that will come in handy when navigating throughout Excel:
- SHIFT + ARROW keys: Extends the selection from one cell
- SHIFT + CTRL + ARROW keys: Extends the selection to the last cell that contains data in the same row or column.
- SHIFT + CTRL + 8 keys: Takes you to the top of the column.
Trick #7 - VLOOKUP
I have to be honest here. I used to HATE VLOOKUP. I could never figure out the formula and it seemed to find every way for it not to work for what I was trying to accomplish. Then, a coworker of mine sat with me and showed me the simple trick to using VLookup. Know The Formula.
Before I give you the secret to using VLOOKUP, I need to explain what it is and how it could help you become an Excel Master. Using VLOOKUP is similar to looking up a person’s name in a telephone book to get a telephone number. VLOOKUP looks at a value in one column, and finds its corresponding value on the same row in another column.
The formula for VLOOKUP is as follows:
=VLOOKUP("Gift basket", A2:B16, 2, FALSE)
=VLOOKUP("What you want to lookup", the table you want to find it at, location of the information you want to pull from, if you want an exact match or not)
I might have made you scratch your head here, but it’s not as difficult as you may think. Check out this video from VitaminCM on how to perform a VLOOKUP in Excel.
Have any other time saving Excel tricks in your arsenal? Let us know: firstname.lastname@example.org.