10 simple tricks for Excel to make you look like you know what you’re doing

As an accountant, you’d think that my most used software would be the accounting software we use at work.

Hinthint; it’s not. On a normal day, I spend about 25% of my time in Sage, rest of it is spent playing around in Excel. It’s not that there’s anything wrong with Sage, but Excel is much simpler to use, can do a lot more and most of all, is compatible! If I want to move payroll data from the payroll software to Sage? Excel. If I want to share financial reports to the executive management, I use Excel. Even if it’s a simple thing as a sum calculation, I use Excel over a calculator every time.

If you’re thinking of a career in accountancy, mastering Excel is a must. You might hate it, but you need to learn to love it.

So, without further ado, here’s my quickfire tips to make you look like you know what you’re doing with Excel!

1. Sum

By far my most used function.  A simple addition

=sum(A1:A10) sums up values from column A cell 1 down to column A cell 10

hint: you don’t have to type the cell references; you can just select by clicking & dragging – the light blue coloured box shows what numbers you’re selecting!

2. Ctrl+PgUp/PgDn

Once you start using excel through only your keyboard, do you not only cut down processing time, but you’ll also feel like a badass. Pressing Ctrl and PgUp will move you through the next sheet, and PgDn to the previous one.

3. Ctrl+End

You’ve got an Excel sheet with a whole heap of data, and you’re sitting in the very first cell, and you don’t know where the end is. You don’t want to scroll through rows and rows of crap, you just want to go to the very last cell on your data, what to do? Ctrl+End will take you there.

Ctrl+Home will take you to A1 no matter where you are in your data

4. Ctrl+Shift+End

If you’re used to any other software package, Ctrl+A should be a familiar shortcut; it’s simply a way to quickly select everything. I avoid using this because if I have a blank row or column, it won’t actually select all of the data I have like so:

You could technically add another tap of the A key for Ctrl+A+A and Excel will select every cell in the spreadsheet, but that’s a bit unnecessary. Ctrl+Shift+End will select all the cells that contain data like so:

Just remember to hit Ctrl+Home first!

5. Selection

Confused yet? This is a simple tool that so many people forget. Like in our first example, you’ve got 10 numbers and you want to find the sum for it. You could do the formula, but if you’re not looking to present it or keep it, you just want a quick calculation… All you need to do is select the numbers and look in the bottom right corner…

Boom – you’ve got quickfire info right at your fingertips!

6. Bar Graph

This where we get to things that look affa fancy but really are simple as anything!

So, I did some interviews in daycare and ask the kids what how many of them liked each of my items and the results were as follows;

Now, I could leave it like this, it’s simple enough, OR I could make it look more professional with a graphic representation. So, I go to the top bar & ‘Insert’

And in the middle you can see the different kinds of graphs you can have. I just want a bar chart, so I select the chart I want

As long as I’ve got headers for my data, Excel will do the rest.

7. Auto Expand

Sometimes a cell holds more info than it can show;

and you’re not quite sure how long you need the cell to be to show all but not to have too much empty space. Well, fear not, Excel will handle this on its own! All you need to do is position your mouse pointer in the line between ‘B’ and ‘C’ until it turns into a bar

then just double-click!

If you click onto the little square between A and 1

you can click between any colums AND any rows, and it’ll automatically adjust the heights & lengths in your whole sheet. This is something you should never do manually!

8. & Or Concatenate

Useful when, for an example, you have first names in one column and last names in another, and you want to combine the two.

=concatenate(A2,” “,B2)

=(A1&” “&A3)

both bring up the same result. Please make sure you put a space in between (AND in quotation marks), otherwise you might not be able to use the data elsewhere!

Great for mail merges etc.

9. sumif

I want to know how many cats each of the families have in total. I have three families; Smith, Simpson & Brown and each member of the family has a different amount of cats. So I would adjust my dataset to include the names below:

And for Smiths, the formula would be;

=sumif(B2:B7,C9,C2:C7)

or the way I remember it; where I’m looking from, what I’m looking for and what I’m adding up

 

Are we ready for the last bad boy? Because this is a wonderful, WONDERFUL tool that can do so much, but does need a bit of practice…

10. Index, Match

I used to be in the Vlookup camp until Index, match came to my life. Essentially, you give it something to look for, and it’ll find it.

Let’s say that I want to find out how many cats Hayley has. I could look at the table and manually type numbers in, but if I’ve got thousands rows & columns, it’s going to be nigh on impossible. So I Index match it, which means that I tell Excel – my answer is somewhere round about here(index). Look for this thing here in this row/column (match),and return the value to me. So, we’re off to find Hayley’s cats.

We know that

• the number of cats is in column C
• first names are in column A
• we need to match the names to ‘Hayley‘

Our index would be column C (I have a set array of data, but if you wanted to look in the whole column, you could just put in C:C or simply click on the ‘C’)

I need to find ‘Hayley‘ that is in B13 to the values in Column A

I want an exact match, so my match type is ‘0’

so our formula is ‘i need to know this’ (find that there) aka

=index(C2:C7, match(B13,A”:A7,0))

 

What does Excel make you feel?