Those who say they know everything in Excel knows nothing
- Confucius (probably)
I, like countless data crunchers, spend an unhealthy amount of time staring at Microsoft Excel. But much like the late night infomercial suggests, there must be a better way! Slowly, but surely my knowledge of Excel has expanded over the years and I've picked up a few tips and tricks along the way to share with all you lovely folks.
1. Invest 1 week of your time using Excel without your mouse
a) This may sound like a cop out in an article dedicated to Excel tips, but in relying mostly on the keyboard to perform Excel tasks, you will have made the wisest investment to save you time in the future. It's a painful process with a lot of Googling and it pays off. Excel has a million shortcuts and it's significantly faster to use these keyboard shortcuts over clicking and dragging the mouse around.
2. Quick Access Toolbar
a) Ever notice the shortcut bar in the top right-hand corner of Excel? The standard Excel comes with Save, undo and redo, but this highly customizable bar can be your best friend when it comes to shortcuts!
b) You can select your custom shortcuts by clicking on the downward arrow to the right of the menu -> More Commands
From the "Customize the Quick Access Toolbar" window, drag and drop your favourite shortcuts. Each shortcut can be accessed by pressing the Alt key and the number associated with that shortcut. The first shortcut will be Alt -> 1, the next will be Alt -> 2 and so on. By pressing Alt the corresponding shortcut numbers will appear.
3. Pivot Tables
a) I personally use a lot of Pivot Tables, so my Alt -> 1 is to create a Pivot Table (surprise!). Other honourable mentions include the AutoFit Column Widths (which automatically fits a column to the size of the text), Filters and the Refresh All for pivot tables.
4. Create your own shortcuts, no VBA knowledge required!
a) If you've gone through the Quick Access Toolbar and couldn't find what you need, create your own simple Macros through the Developer Tab. To access the Developer Tab go to File -> Options -> Customize Ribbon -> Select Developer.
b) Once you have the Developer Tab open, select Record Macro. Excel will prompt you to name your Macro, then come up with your own Shortcut Key. Make sure you choose something you'll remember, but doesn't already exist. You can store the Macro in your Personal Macro Workbook to access it for multiple worksheets.
c) Once you press OK, then every action you do will be recorded as part of the macro. It's vital you do only the number of clicks you need, and no more. This includes things like switching tabs. I've used these macros to create a shortcut that automatically colours a cell yellow or red. Once you're done your sequence of actions, stop recording the macro, pat yourself on the back for creating your first macro and test it out!
5. Concatenate without the concatenate
a) =Concatenate is a handy function to combine text together in different cell, but concatenate is hard enough to spell without its quadruple syllables and confusing vowels. Thankfully instead of writing =concatenate to combine cells together, you can simply put in an ampersand between selected cells to achieve the same effect.
b) In the example below, you can see row 2 shows the simplest concatenate possible between Column A and Column B. Notice in Row 3 I'm able to add a space between Column A and B by putting spaces in "quotes". Row 4 shows that anything in "quotes" between ampersand symbols will appear.
6. Shift Space or Ctrl Space |Ctrl + & Ctrl -
a) Need to select and remove rows and columns quickly? [Shift Space] quickly selects the entire row of cells, while [Ctrl Space] selects the entire column. From there, [Ctrl +] will add a row or column (depending on what you've selected), or [Ctrl -] will remove a row or column.
7. Ctrl Shift 4| Ctrl Shift 5
a) Need to convert numbers into $0.00 or 0% formats? [Ctrl Shift 4] will automatically convert numbers to $0.00 while [Ctrl Shift 5] will convert numbers to 0%. Who knew that business cares about percentages and dollars symbols.
8. Need to lock in $ in a cell? Use F4!
a) If you'd like to drag a formula across multiple cells, but maintain the same column or row, you'd "lock in" the cells with $. In the example above, A2 is locked in by writing $A$2. If we wanted to maintain column A we'd write $A2, and to maintain row 2 we'd write A$2. But did you know you can select a cell, then press F4 to lock in the cell. The first F4 will lock everything ($A$2), the second F4 will lock in the row (A$2), the third F4 will lock the column ($A2), and the fourth will clear the cell (F4).
This is by no means an exhaustive list, but one to start making Excel your happy place! Have your own Excel tip? Share your favourites below and the ones you find the most useful!