If I had to make a list of all of my favourite office technology and software Microsoft Excel would most certainly be missing from the top ten (urm… it probably wouldn’t make the top 100). I do hold a grudge against Excel I must say. Mostly because of all the time spent on Excel doing expenses for Executive’s that had that habit of chucking months and month’s of receipts at me with no idea where they came from or what they were for. I would then have to input them all into a crappy spreadsheet when really I wanted a fancy piece of software that did the job for me. Despite my reservations I do know that Excel isn’t going anywhere and assistants are going to using the software for quite some time to come so I thought I would share my top 40 Excel hacks. These are the little tricks that make you look like an Excel genius and make the time you spend in spreadsheet hell somewhat quicker!

  1. One click of the corner button will select all your data instantly. CTRL + A will do the same job.
  2. Click CTRL + any arrow button on the keyboard to jump to the edge of the sheet in different directions.
  3.  Use =UPPER, =LOWER and =PROPER to transform texts for different purposes. UPPER will capitalise all characters, LOWER can change text to all lower case and PROPER will only capitalise the first character of a word.
  4. When an input value starts with zero, Excel will delete the zero by default. Rather than reset the Format Cells, add a single quote mark ahead of the first zero
  5. There are multiple ways to rename sheets, and most users will right click to choose Rename but just click twice, then you can rename it directly.
  6. To quickly enter data in multiple cells, first select all of the cells.  Type in the data and press CTRL + ENTER, which enters the same value in all of the selected cells.
  7. F4 repeats whatever you last did
  8. CTRL+SPACE selects the entire column
  9. SHIFT+SPACE selects the entire row
  10. Hit F2 to edit the formula of the curently selected cell
  11. SHIFT+CTRL++ allows you to add cells/rows/columns
  12. CTRL+PG UP / PG DWN allows you to navigate between tabs of a spreadsheet
  13. CTRL+1 opens up the cell-format menu
  14. Paste special (ALT+H+V+S) is very useful and allows you to paste values, formulas, formats, etc…
  15. CTRL+HOME takes you to cell A1
  16. Hitting ALT+= Automatically calculates the sum of an adjacent row/column of text
  17. PAGE UP / PAGE DOWN will move the screen up and down
  18. ALT + PAGE UP / DOWN will move the screen from left to right
  19. CTRL +PAGE UP / DOWN lets you jump between worksheets
  20. SHIFT (hold) + ↔ lets you select a cell range
  21.  SHIFT +SPACEBAR lets you select a range of cells then hit [CTRL + “+/-” to add or remove rows
  22. CTRL +SPACEBAR selects an entire column
  23. + SHIFT+ PAGE DOWN / PAGE UP will enable you to select a number of worksheets
  24. DATA-TAB will remove duplicates
  25. Click ALT + F10 to see all elements in your worksheet.
  26. A huge time saver when you want to delete empty cells, rows, columns is to select all blank cells in your selection. Click F5 ->special ->blanks ->ok.
  27. Formulas don‘t calculate? Most of the time you can solve this by changing the calculation option. Go to Formulas-Tab ->calculation options -> automatic]
  28. To customise the cell edit order when you are typing out a large list of entries just go to Menu > Tools > Options > Edit tab and set the “move selection after enter” to whatever direction you like
  29. Tell Excel to change the default font from Arial to your favorite. In Menu > Tools > Options > General Tab set the “Standard font” to what you like. You can set the font size as well.
  30. To change the colours for the current workbook go to Menu > Tools > Options > Color and define your own RGB values for each of the colors.
  31. Imagine how much of typing you can reduce by defining simple replacements for several day to day words you type. Define your own auto correct rules in Menu > Tools > Auto Correct Options > Auto Correct Tab.
  32. Sometimes excel files can be massive depending on the number of sheets and formulas you have used. There is one trick to reduce the size of Excel files with images. You can tell excel to compress the images for web viewing. This will force saving the images at lower resolution of 96dpi instead of standard 200dpi. In the Menu > File > Save As dialog box, click on “Compress pictures” option and adjust the resolution in the “change resolution” area.
  33. If you frequently filter lists or tables, this shortcut should be at the top of your list.  With the same shortcut, CTRL + Shift + L you can toggle filters on and off any list or data set.
  34. Once you have set up a find, and have found at least one match, you can keep finding the next match by using Shift F4.
  35. To copy a value from the cell above, use CTRL +D. To copy data from the cell to the left, use CTRL+R.
  36. To create a embedded chart, first select the data that makes up the chart, including any labels. Then use use the keyboard shortcut Alt + F1.
  37. To create a chart on a new sheet, first select the data that makes up the chart. Then use use the keyboard shortcut F11
  38. Quickly add an entire column or row by clicking in the first empty cell in the column. Then enter ALT += to add up the numbers in every cell above.
  39. Right clicking may not seem like a big deal, but when your fingers are busy typing away, why shift your attention to your mouse or touchpad? Instead, use the right-click shortcut: “Shift+F10” to automatically open your right-click menu and the up and down arrow keys to navigate.
  40. To format numbers as currency, the keyboard shortcut is Ctrl+Shift+4. To format numbers as percentages, press Ctrl+Shift+5.


You may also like

Leave a comment

Sign up to Practically Perfect PA

Enter your email and stay on top of things