Tricks to Make Yourself a Microsoft Excel Master
Wednesday 31 May 2017

Tricks to Make Yourself a Microsoft Excel Master

The number of ways you can use Excel is countless, and so are the number of features packed inside Microsoft’s most popular number-crunching application. Whether you’re a casual user or an Excel expert, it pays to know everything that the program’s capable of, and here are 18 easy tricks that can make a difference.

1) Apply diagonal borders

bhc3oim8y4vjrdzsuzgt

If you’ve got a table that needs labels for both the row and column headers in the same cell, use diagonal borders. Click More Borders at the bottom of the borders drop-down menu (on the Home tab of the ribbon), and the diagonal buttons are by the box corners.

2) Insert multiple rows and columns

You probably know you can select a row or column then add a new one via the Insert drop-down under Home. What you might not be aware of is if you select multiple rows or columns to begin with (like 5 or 10), then Excel adds in that many extra gaps in your sheet.

3) Turn columns into rows (or vice versa)<

tbxgixevlj7fps4ndney

If you have data in columns that should be in rows or the other way around (whether through your own fault or someone else’s), all is not lost. Copy the original block of cells, then right-click on the destination cell, and choose Paste Special then Transpose.

4) Hide individual cells

Excel has a trick for ‘hiding’ cells. With the cells selected, right-click, choose Format Cells and then set the format as Custom under the Number tab. Enter ;;; (three semicolons) as the format. The cell contents disappear but they’re still there and can be used in formulas.

5) Draw out equations

hk5vff6j6xdtzijed78w

If you find it easier to draw out equations, this is now possible in Excel 2016 (handy if you’ve got a touchscreen computer too). Go to the Insert tab on the ribbon menu, then choose Equation and Ink Equation. You can then sketch away in the yellow box.

6) Learn Excel’s best shortcut keys

If you really want to improve your productivity on Excel, learn some shortcut keys to do common tasks faster. There are lots of them to pick from, including Ctrl+; to insert today’s date, F2 to edit the current cell, and Alt+Enter to start a new line in the cell you’re in.

7) Quickly resize columns and rows

You can resize columns and rows by right-clicking or by dragging the row or heading boundaries at the edges of your sheets with the mouse. There’s a quicker option, though. Double-click a row or column heading border to automatically resize it to fit the data.

 

8) Add your own graphics to charts

nt1bjbjrxvucq4q77rmg

You don’t have to settle for the colored blocks that Excel gives you by default when it comes to creating charts. Double-click on a bar, then click the paint bucket to change the fill options: you can switch to a gradient, a pattern, or load in an image file from disk.