Ask not what you can do with Excel…

… 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:

unformatted-table

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:

extended-table

Summarising data

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…

 

How to make a form work for you…

We recently worked with Jubilee Medical Group helping them manage their patient questionnaire.

This is what they had to say…

What you did for us re the questionnaire saved us a huge amount of time and there is no way we could have replicated this in practice. The format of the results were superb. Patients at the PPG commented on how impressive they looked but also how user-friendly / simple they were to understand. The support received was invaluable in my opinion.

What we did for the practice…

Jubilee Medical Group approached us for help in managing their patient questionnaire, collating the results and then presenting them in a report format.

Previously the practice would make a paper version available, and email some with a Word/Excel version of the document that patients could complete and return. They had a low return electronically and had to collate all results (electronic & paper) manually into an Excel spreadsheet. Once all responses had been collated, they had to manipulate the data to get it into the required format and build graphs for those they wish to show graphically to the patients in the results report.

It is estimated by the practice that this approach would take approximately 20 – 25 hours, taking into account all of the manual aspects involved.

The practice sent us the patient questionnaire they use in Word format and we built the questionnaire. The practice then emailed patients with a link to the form, as well as putting it on the practice website. They also made paper copies available in reception and would key any paper replies. The practice let us know when to close the form, and we exported the results in tabular and graphical format. We did some filtering of the results so they had an overall view of the group and then a tailored view for each branch and we exported the comments for each branch for review.