What excel skills does a new accountant need?

How much vlook up / conditional formatting / macros /pivot tables knowledge would someone need?

Didn't find your answer?

I was looking at Excels fun on YouTube and the guy has so many videos for each topic that you don’t have so much time to go through all of them. So if there’s some specific things that you really need, please advise. 

Thanks 

Replies (27)

Please login or register to join the discussion.

By Michael Beaver
10th Nov 2017 14:58

It depends on what type of accounting work you do.

When I was a CFO in the finance industry I used a lot of IF functions and pivot tables. Conditional formatting was also useful to tart up reports for dissemination.

I never used macros. But I did know business analysts who did, but for very specific purposes.

Now that I'm in practice I rarely use any of the above. It depends on what data you have to work with.

I use it occasionally if I get a particularly complex spreadsheet from a client, but other than basic formatting and basic formulae I probably use Text to Columns mostly.

Thanks (1)
By Dick Lloyd
10th Nov 2017 15:49

As an accountant now working in industry, if this is what you are doing, I would suggest you learn IF, VLOOKUP, HLOOKUP, SUMIF, SUMIFS and PIVOT TABLES

Thanks (3)
Routemaster image
By tom123
10th Nov 2017 15:30

I couldn't do any of my work without pivot tables - and also the ability to retrieve data from other systems using ODBC links.

Conditional formatting is good for reports.

I have one macro that I use, to generate random samples of stock to count - but don't have a great deal of knowledge in that area.

Thanks (1)
David Winch
By David Winch
10th Nov 2017 16:27

Aside from pivot tables, sort & filter are useful combined with copy / paste visible data only.
But what you use depends on what you are doing!
David

Thanks (2)
Replying to davidwinch:
Routemaster image
By tom123
11th Nov 2017 09:44

I agree with the 'paste visible data'

I can never remember how though, and have to google each time.

Just knowing the feature even exists is great!

Thanks (0)
By jon_griffey
10th Nov 2017 16:53

Why don't they teach Excel as a GCSE in schools? It must be one of the key skills that employers want.

Thanks (1)
Replying to jon_griffey:
avatar
By Ken Howard
11th Nov 2017 08:36

jon_griffey wrote:

Why don't they teach Excel as a GCSE in schools? It must be one of the key skills that employers want.

Don't be silly. That would be far too sensible. Don't you know that schools exist just to keep teachers and academics busy and to enable them to indulge their passion for academia. If they were to teach skills for the workplace, they'd be unrecognisable, with a different set of subjects, different teaching methods, etc.

My son did in fact start on the EDL course in his first year at secondary school, which impressed me because it is a respected workplace qualification. They were going to do it over 3 years and did the first 2, but then suddenly dropped it, so 2 years wasted with nothing to show for it. Hey ho, back to spending a year making a wooden fish!!

Thanks (2)
Replying to jon_griffey:
avatar
By Richard Willis
16th Nov 2017 12:20

Well they (still?) teach basic algebra, without which Excel would be a no starter!

Thanks (0)
avatar
By Chris Smail
10th Nov 2017 16:59

They do, not all schools offer it not all pupils listen.

15+ years ago my children did some sort of test that counted as two GCSE's and covered excel, word, databases, websites etc

Thanks (0)
Replying to Chris Smail:
avatar
By Ken Howard
11th Nov 2017 08:39

Chris Smail wrote:

They do, not all schools offer it not all pupils listen.

15+ years ago my children did some sort of test that counted as two GCSE's and covered excel, word, databases, websites etc

Many have now stopped doing it. (Though few did it in the first place). Emphasis now is on programming rather than use, so yet again aimed towards the more academic, and stuff the rest who won't be going to uni.

Thanks (0)
avatar
By adam.arca
11th Nov 2017 09:35

I don't think it's the job of schools to teach Excel "skills" (and they'd only do it poorly, anyway).

As for the OP's question, I think you're approaching this from the wrong angle. Rather than asking what functions you need to know and then, presumably, learning those, why not dive in the deep end, start creating spreadsheets, decide what features you need and then teach yourself the necessary functions (and VBA, it's really not that difficult): you can thieves shamelessly from other people's work and there are countless online resources.

Thanks (0)
Replying to adam.arca:
avatar
By Ken Howard
11th Nov 2017 11:11

adam.arca wrote:

I don't think it's the job of schools to teach Excel "skills" (and they'd only do it poorly, anyway).

So who decides? Why teach, say, Queen Elizabeth 1? Why spend forever making a wooden fish? Why do they have to learn French conjunctives? Only a tiny proportion of kids who've had to learn this stuff will ever need it. I'm coming to the end of my son's secondary education and can't actually believe how little has changed in the 40 years since I was at secondary school. The workplace has changed out of all proportion, but schools are plodding on teaching the same stuff.

Thanks (0)
Replying to Ken Howard:
avatar
By adam.arca
12th Nov 2017 21:34

Well, speaking as a history buff, I'd say that learning about Queen Elizabeth, or any other monarch for that matter, is about a million times more useful to learn in school than Excel skills. That's because what you learn now in a history lesson will still be relevant in 40 years time whereas Excel will most likely have disappeared into the void and been replaced 2 or 3 times over by then.

IMHO the purpose of schooling is to teach kids to think for themselves and not to turn them into technicians only able to employ a limited set of skills they have been spoon fed.

Thanks (4)
avatar
By SteveRA
11th Nov 2017 10:56

Like Chris’s children, my daughter took an ICT diploma along with her academic GCSE subjects about 6 years ago and it was supposed to equal 2 GCSEs, but as she had 10 others it really did not add much academically, in fact there was no space for it on her UCAS form. But she now has some useful skills which my son was not taught, 3 years later. It seems to have been overtaken by programming, called coding now.

However, the best thing my daughter learnt at school was to be an accurate, fast, touch typist. A skill I wish I had, as I make endless mistakes, despite typing every day.

Thanks (1)
Replying to SteveRA:
By coops456
16th Nov 2017 14:46

I did well in my GCSEs but the most useful was Typing. Best thing I ever did was drop Physics for it!

Thanks (0)
avatar
By D V Fields
11th Nov 2017 11:38

A good accountant, new or otherwise, needs the skillset covering: analysis, strategy, risk, planning and communication. If they get those right then I would expect they'll have the ability to use tools such as Excel to assist them; and will find the appropriate (Excel) function as and when required.

Thanks (1)
avatar
By DA Baker
11th Nov 2017 15:20

“However, the best thing my daughter learnt at school was to be an accurate, fast, touch typist. A skill I wish I had, as I make endless mistakes, despite typing every day.”

Despite using computers for many years my typing is slow. The solution for the last few years has been to use dictation software (Dragon Naturally Speaking). Dictating correspondence comes naturally to my generation who were used to having their own secretary who could do shorthand.

Thanks (2)
RLI
By lionofludesch
13th Nov 2017 07:30

What's a pivot table ?

Some kind of rotating office desk ?

Thanks (0)
Replying to lionofludesch:
Avatar
By I'msorryIhaven'taclue
13th Nov 2017 16:55

I once stayed at a Hotel, in Kensington I believe, with a circular rotating serving table, which revolved automatically carrying continuous food dishes to which the guests helped themselves as they passed their individual apertures. Quite speedy, so terrific sport if someone on the other side just missed their preferred dish, because you could grab it as it passed by you and leave them without and fuming - not unlike collecting your luggage from an airport conveyor, I suppose.

Thanks (0)
avatar
By paulwakefield1
13th Nov 2017 11:16

I'm delighted to hear they're teaching coding. A brilliant discipline and I would have thought great grounding for a future accountant!

If the OP is a member of the ICAEW, there are tailored online Excel training courses available in conjunction with "Filtered". These are free for ICAEW students. Google search is probably your best bet because the ICAEW site is completely and utterly hopeless.

Thanks (0)
avatar
By Maverickj8
13th Nov 2017 16:28

I believe one cannot know too much excel. There’s always room for more.

I use lookups, pivots, sumifs, whatifs, offset and match, on a regular basis. Some macros and VBA every so often.

I work in financial services and knowing the basics of the first five on that list will push you ahead of the rest when it comes to reporting and financial modelling.

Most board members are not proficient in excel to the level that one could measure a tangible value produced from it. Build them a dashboard, a one pager report, or simply show them a quick and simple way to do something, they will appreciate you for it. Being creative with any type of reporting can bring massive value to a company.

Incidentally I used excel to overhaul a board reporting pack at a company and reduce month end close from 14 days to 5 days with just some minor

2cents

Thanks (1)
Avatar
By I'msorryIhaven'taclue
13th Nov 2017 16:56

You'll come across SWITCH function (eg for switching narrative from say profit to loss), but IF and nested IF functions are more reliable (SWITCH can have referencing issues when you copy or move file to another computer).

Use CONCATENATE to merge in repetitive data fields (company name, year end date etc) from "dashboard" area - it'll save you typing in the same data over and over. Profit and Loss Account for the...[concatenate year or period] ending [concatenate a date].

ROUND, and its variant ROUNDDOWN, are a must for any accountants who don't appreciate being told their (visible) figures don't add up.

Basic MACROS are easy to learn: just record the keystrokes for printing out a sixteen page document, and use the macro to do the work thereafter. Or a macro that copies and pastes the line above, for example, saves on RSIs.

Anchoring is useful, as is a grasp of relative versus absolute referencing. Oh, and work diagonally down the sheet so that you can insert or delete rows or columns without causing carnage. Use Window view functions to freeze column headings, and learn how to hide or display rows or columns.

There, that'll keep you busy.

Thanks (2)
avatar
By Maverickj8
13th Nov 2017 17:39

Rank is also another very useful formula, most foret about. Great when used in conjunction with offset and lookups to produce a top 10 analysis from large raw data.
This becomes the driver of most in dashboard style reporting

As above emphasis on relative and absolute referencing will save a lot of time itself in the learning process

Thanks (1)
avatar
By Richard Willis
16th Nov 2017 12:31

When I was still a commercial accountant our accounts clerk used to take about 1.5 days to manually collate and then compile the accounts pack, using a TB extracted from the ERP system. During a lull in my workload I spent about the same amount of time automating the process, using ODBC and macros. The resulting pack took about 15 seconds to run and could go backwards as well as forwards, so any historical queries could be looked at after month end.

I learned the value of macros in our original Lotus123, in which they could be typed into a cell and invoked from within the workbook.

Thanks (0)
Avatar
By I'msorryIhaven'taclue
17th Nov 2017 15:49

The single most useful advance for me was colour coding, which means I no longer have to trace through each and every cell to reach a source cell, and can avoid overwriting the wrong cells.

There's no single industry standard set of colours for financial modellers, so the variation I've adopted is:
black font: static data, & static function cells such as eg totals;
blue font: user input cell;
blue font on a yellow background: key user input cell (on which other cells are dependent);
purple font: static cell, dependent on another cell(s) elsewhere in workbook;
green font: static cell, a mix of dependency on another cell(s) and inbuilt functions (such as eg IF or CONCATENATE functions);
red font: notes / fix this sh*t.

So when I glance at a busy worksheet the blue / blue & yellow input areas jump out at me; and I know not to overwrite purple or green text (although I might amend their code). I've found colour coding saves hours of looking at individual cells' formulae, cuts down on mistakes, and helps me understand a worksheet all the better.

Thanks (1)
Replying to I'msorryIhaven'taclue:
avatar
By Alf
11th Dec 2017 09:22

I use conditional formatting for cells requiring manual input - similar to you it uses a yellow background, but when the cell is empty it has a purple background. This highlights at a glance any manual inputs still needed

Thanks (0)
avatar
By paulwakefield1
17th Nov 2017 15:59

The ICAEW have published a "Spreadsheet Competency Framework" which is designed to help users at different levels to assess the skills they need and the ones thay already have for their role. It is also to help employers assess the real meaning on a cv of "Expert in Excel"!

Link: https://www.icaew.com/-/media/corporate/files/technical/information-tech...

Thanks (0)