… but what it can do for you.
Excel is a spreadsheet program – in simplistic terms. But I have used Excel throughout almost its entire lifespan. It is capable of all of the mathematical calculations you’d expect – and probably a massive amount of logical arithmetic that you may not expect. If you want find the average value in a list, there’s a function for that; mode & median – there are functions for those too. There’s even a function which will display the date and time at this very moment.
If you want to count how many cells have a specific value in – there’s a function for that. Even if you only want to add cells together if their value is over a specific amount – there’s a function for that.
How about if you have a large list of values you want to add. Most people have heard of the SUM function which will make this nice and easy – but there’s even a button called AUTOSUM which will do it all for you.
The fill handle
One thing most of us will have done or seen in the past is a monthly table of information. Be it costs, income or “miscellaneous”, it’s something that has been done since Excel was first introduced – and even using the spreadsheet programs which were around before Microsoft (yes, there were other spreadsheet programs before Excel!).
Excel makes monthly tables much easier and faster to create. Next time you create one, try typing the name of the month required (I.e. January). The small black square in the bottom right of a cell is called the Fill Handle. Click and drag this downwards to see what happens. Release the mouse button and you will see a list of months displayed.
This does not only work for January to December, it also works, as per the animation, if you start at any month. Better still – try typing a day of the week and drag the fill handle to see what happens.
Sorting and formatting as a table
Back when Excel was first introduced, a lot of tables of information were being created and maintained. Occasionally, these tables often needed sorting in order. However, if a table of information had 8 or 9 columns, it was quite simple to sort the data in one column without making changes to the others.
If we had a list of employees, for example, with their first and last names, their start dates, salary, payroll number it may look like this:
You may want this sorting by length of service/start date. A number of times in the past, I have seen tables where this happens:
Notice how only the Start Date column is sorted. This happens due to the person making the changes selecting only the cells which need sorting and not encompassing the entire table of information. One major issue with this, is that if the file is then saved and sent out, it cannot be “undone” (i.e. you cannot re-sort the column into its original order.)
If the data is formatted as a table, it gives you much more scope to manage the information and also makes the above Scenario impossible to replicate.
Firstly – it looks nicer. Aesthetics matter. It is far easier to read a table formatted in this way and the entire table is automatically formatted with whichever style you select.
Secondly, any sort function completed, using either the dropdown filter arrows in each heading or the Sort option on the data tab, would now automatically select the entire table to sort. Filters and functions are automated – notice, on the animation below, when the sort box is moved aside, the entire table is selected automatically:
Thirdly, and perhaps most importantly, that table could be extended well beyond its 10 rows of information and well into the hundreds (or thousands). More columns can be added too such as department, gender, salary bracket (or band) etc. Any rows added to the bottom of the table will automatically become part of the table. Likewise, any columns added to the right (or inserted into the middle) will also automatically become part of the table. Any charts/pivot tables etc. referring to the table as a source will automatically update when rows or columns are added. (I love tables by the way!)
So we could end up with a table looking something like this for employees:
This table has now got 99 rows of information. If you are looking to summarise that data, prior to Excel 2000, (and if you weren’t aware of Pivot Tables) it would’ve been a case of building a manual table with a lot of lookup references. This would work but, if it did break (which I found often happened in the past), it could be almost impossible to find out exactly where – which meant me building ANOTHER reference table and setting everything up again.
Using a pivot table to summarise the data was introduced in Excel 2000 and is an absolute gift of an idea. Pivot Tables are found on the insert tab. Click on this and select where you want the pivot table to appear.
The PivotTable field list will appear on the screen with a blank pivot table next to it. I have always found that the best way to create a pivot table so it displays the information you want, is to drag and drop the items from the top, to their required location at the bottom.
Say we want a list of staff salaries, grouped by age, per directorate.
For this, as per the animation, I would drag Age to the row labels, Directorate to the column labels and Salary to the Values. (It is possible to have more than one field in any of these boxes).
Your pivot table would appear like this:
It shows the information requested in the standard pivot table format. This is, perhaps, a little messy but it is very easy to tidy up. You could amend the formatting of the numeric salary amounts to show as currency – try right clicking on one of the values in the table and select Number format to format the entire table as currency. You’d only need to click on one of the values to update all of them.
To make it nice and neat, you can also group the row labels – if they are dates, you can group by day/month/year – as numeric values, our youngest staff is 20 and eldest 54 in this example. Can Excel make that neater – you bet it can – right click on any of the values and select Group:
This will make the pivot table much easier to read and will summarise the data in age ranges.
As sad as it may sound, I fell in love with Excel a lot of years ago and use it every day of my life now. Be it for work related stuff or stuff at home. I often learn new things about it and know that it is much more advanced than I use on it – but feel free to play with it – that’s how I learned most of the stuff I know and it’s the best way I find of learning what you need.
See you next time…