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?
I must bookmark this post! I’ve been on so many courses about Excel but just cannot retain the information yet there are so many times I want to be able to put info in a simple spreadsheet. Hopefully your clear steps will help me!
This is fantastic, thanks so much for sharing such an informative post. My accountant will be over the moon when my spreadsheets are in better order! xxx
I much prefer excel too, I use it to list my blog and work related earnings and it informs me of any pending posts I need to write. Pretty much anything.
I love excel though I am still to explore its best use! That’s my best buddy in planning and recording. I like using shortcuts. These are all essentital additional points in excel. 🙂
I haven’t used excel in ages but when I do I will be looking at this post because its very useful 🙂
This is such a useful post, I only have very basic knowledge of Excel but I use it a lot in my full time job.
Really useful stuff here. I’ve started doing my blog accounts on excel so this has actually come in handy!
I hate excel, it is the work of the devil, some great tips though x
I love excel too. For work I sometimes use raw data and pivot tables – I’m such a geek! Such useful tips.
I really need this as I don’t actually have a clue how to use excel – my husband usually does it for me.
Oh gosh this all looks so complicated! This is probably why I am not an accountant. 😉
Great tips. I found I googled so much when using excel at work to make my life easier haha x
Oh these are awesome. I should really use Excel to make my life easier. My daughter uses it for her data.
You make it sound so easy! When I do it I just get so confused!!
I love Excel! I use it for everything, it has so many handy functions. Thanks for teaching me a couple of new tricks! 😀
What a cool resource. I’ve bookmarked it for future reference.
I’m useless at excel but my partner has been saying he wants to learn so I’ll pass this on x
Some very good tips on this blog post that I did not even know, many thanks for sharing.
Fantastic tips – I think just the word excel scares many people…..now, can you write one of these for Numbers for Mac please 😀 😀
Great post!! It’s so important to know spreadsheets … many jobs look for this skill!
xoxo, Candice
http://www.candicenikeia.com
This is really helpful – I don’t work in a financial field, but I think a good knowledge of Excel is so helpful for pretty much any job involving a computer!
Thank you so much for this, I seem to have a mental block with excel and have bookmarked this to come back to x
Thanks so much for this – I suck at excel!
this scares me! i am such a techie phobe, when i was at scholl there were no such things as spreadsheets! you obviously know your stuff, well done in writing such an informative post 🙂
I thought I learned everything to do with Excel in school! Clearly not! Great post, so helpful as I’m using it now for a few things! xoxo
What a useful post! I love Excel – I actually own that spreadsheet mug!
This takes me back to school. Very good tricks some I already knew and new ones I’ve learnt 🙂
Definitely going to bookmark this post for reference. I struggle to understand excel. I find it so confusing sometimes.