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…

 

Running for Non Runners

Just Because You Never Have Doesn’t Mean You Can’t!

Remember the kids at the back of the running group at school? Well I couldn’t keep up with them!

Primary school, secondary school and beyond, I couldn’t even finish 90 minutes of football. Now I run 3 times a week, 10 miles per week and increasing. I’m aiming for 10k this summer and hoping to increase or at least maintain my fitness from there.

Here’s what worked for me.

Where to Start

Someone recommended a free audio running course called Couch to 5k. I’ve always been a patient, goal based learner and I knew that if I stuck this out, as long as it took, I’d manage this course. So I went for it. I must admit, having my wife do the course with me was a massive support. Two are better than one in some cases.

Do It Right, Without End in Sight

A fit, healthy 18 year old girl said to me recently; “I used to run but I can’t anymore because I got shin splints.” Call me a sceptic but certain questions come to mind;

  • Did she have gait analysis (professional advice after your running is recorded and reviewed by an expert)?
  • Did she buy running shoes with professional advice?
  • Did she overdo it? Did she have at least one rest day after each run?
  • Did she have a training plan or just have a run-to-fail approach?

For me, running is like learning a musical instrument or learning to drive; it includes doing your homework before getting stuck in. So I read around my subject (nerd!) but it pays off.

So we found out that we over-pronate, bought expensive Brooks trainers (in the sale of course), wicking running gear, even running socks with extra support. I think looking and feeling the part motivated me. After spending a wedge of dosh, there was no way I was quitting…

I found great info at;

  • DK Complete Running and Marathon Book
  • Runner’s World Magazine (Articles for all levels)
  • NHS C25K articles
  • Google and Youtube on any specifics e.g. Is running with a heavy back-pack a good thing?

The more you read, the more you start to hear the same things repeated – essential truths! Follow the advice and you’ll be running, progressively, safely and without a desire to stop!

Many Runners are Doing It Wrong

I’m no expert, but I’ve read a lot by experts. Here are some hum dingers I see all the time on the streets. This is not to look down on anyone, I’m against that, but if only more people knew more about running first – they would stop giving it a bad name as a sort of dangerous sport;

  • Carrying water bottles – You don’t need water if doing less than 60 mins, sip some before you go. In fact, gripping objects causes muscle tightness in your arms and bad running form. Keep your hands loose, don’t even clench your fists or your shoulders will tighten.
  • Arms flailing – If you read up on running form, you can spot poor form a mile off. Get it right and you’ll feel a good, solid, steady rhythm.
  • Jackets and jumpers tied around waist! What?! In summer as well? I see this all the time. Your body temp rises as you run. Set off a touch chilly and you’ll soon be toasty. Don’t carry stuff you don’t need.
  • Landing – Again, where should your foot land? Heel striking? toe? Middle? It pays to do your homework. No wonder so many people get injured.
  • Clothes – Wow. I see people dressed in all sorts of crazy stuff. Wicking running gear reduces sweat on your skin. It’s so worth it.
  • Trainers – it’s not a scam. Expensive running shoes are not for the naïve. Run in high street fashion trainers and you’re asking for a visit from Mr Pain.
  • Huffing and Puffing – Controlled breathing was a new technique for me. It didn’t even feel natural for a while. Once I cracked it though, I was living out scenes from Forrest Gump! A miracle for me.

Use Force of Habit for Your Good

According to a recent study it takes 66 days for a behaviour (such as running) to become unchangingly automatic. https://www.theguardian.com/lifeandstyle/2009/oct/10/change-your-life-habit-28-day-rule

That’s the same time for a small tomato plant to bear fruit! Two months, it’s not that long really – 3 times a week. Don’t conclude that a relapse is a permanent failure. Expect to face some setbacks as you work toward your goal. A great phrase I heard on the NHS C25K was “Even a bad run is good for you.

Putting your running gear by the front door or by your bed forces your good intentions. Making an appointment and sticking to it is best too. For me, 5:30pm is ideal, on an empty stomach, maybe a bit of water.

You Will Meet With Opposition and Bad Advice

Get your knees up!” “I thought you were supposed to be running!” and other dumb remarks I get from onlookers. I have a theory; these people can’t be runners. They must assume that running means sprinting. Even a respectable 6mph is not an all-out sprint. Some of my mates are good runners, and they don’t sprint for 10k or 20 miles plus. Anyway, jog on and pay no attention.

I’ve had it said “Running is bad for you it causes injury.” But 7 months and injury free; I feel the cautious approach is slow, but sure!

Your Personal Best is Personal

Never compare yourself with others. Never. You will haemorrhage morale. Anyway, there is always someone better than you at anything you try in life. But they could give up one day. So outrun your critics.

Manageable Goals

Goals are like blueprints—it takes work to turn them into reality.

Many health experts no longer subscribe to the “no pain, no gain” approach to exercise. So, to reduce the risk of injury and to avoid the burnout and discouragement that often lead to quitting, keep exercise at a comfortable level.

What’s Next?

For me, the discipline of 3 times a week has been good for the past 7 months. In August I’ve signed up to a 10k. So there’s no turning back. Even if my achievements are modest, they’re mine, they’re improving and I’ve no desire to stop.

766% ROI

766% Return on Investment – all in a days’ work.

Well, 12 hours actually.

Whilst delivering some bespoke Excel training to the EPMO Team at Teesdale House, we looked at some of their processes, particularly around consolidating the data in both the TRS and ESR systems. It was at this point that my “Database light bulb” was lit.

The team had to manually compare two extracts from these systems and flag up any anomalies – a tedious and time-consuming task. Why not have a database do all this analysis (and more) for you in a fraction of the time?

The NECS Applications Team don’t train Microsoft Access as a rule, mainly because there is such a massive amount of theory as well as the “point and click bit” and bombarding someone with about 4 days of solid training and then expecting them to go away and build a database from scratch simply isn’t feasible.

Instead, we work with the department or team in question, establish their needs and build a system for them before they test it and then get trained on how to use their new database.

The EPMO database now analyses the 2 datasets in depth and at speed for the team and can produce a wide range of reports at the click of a button.

The team estimate that this saves them 2 days per month – 24 days a year!

A 766% return on investment.

A happier, more efficient workplace.

Why a lawnmower analogy is my favourite yarn

You’ve spent a small fortune on it – your new lawnmower.

Let’s call it the NECS-3000x – a top of the range job.
All the features. All the tools. All the extras.

To make sure that you get the best out of your new purchase, you eventually find, and book onto, and pay for a course at your local college. Let’s call it “All you need to know about the NECS-3000x”.

Perfect.

You arrive at the course and each delegate has their own NECS-3000x provided by the college and you spend the next 2 days learning everything about the machine.

What every single knob, button and setting does. How to dismantle and re-assemble the thing. How to service it. What might go wrong. How to fix it if it does go wrong.

Everything.

You now have “all you need to know” about your NECS-3000x – except 1 simple thing – how to cut grass.

On the front of our website we state “We are no ordinary training team” and this is why.

Our course would be called “Achieve the perfect lawn using the NECS-3000x” – a subtle difference in focus, but a massive difference too.

Our courses and services focus on the end results that you need to achieve first and foremost. The tool you are using is almost secondary.

Next month we are delivering some bespoke training for practice staff within Darlington CCG, but it isn’t just Excel training – it’s reporting on practice populations/data analysis using Excel. Real life examples, real life (anonymised) data, real learning.

Similarly, the services we offer in small-scale database building, demographic analysis and mapping, custom surveys are all aimed to fulfil a specific purpose in the intelligent commissioning model.

Get in touch and we can talk (tea, milk and one sugar for me).

Robin
necsu.learning@nhs.net

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.

I think this is the beginning of a beautiful friendship…

When our surgeries upgrade their clinical systems our aim is to help make that transition as easy as possible for all staff concerned. We advise and support throughout the move, aiming to make all staff as comfortable as possible with their new system. But all of this work happens behind the scenes.

While surgery staff work tirelessly to make the transition from one system to the next, patient care is uninterrupted and the consulting rooms remain focused on the treatment and wellbeing of patients.

This move to a new system is just the start of the journey.

The Trainers may leave the Practice when the staff have gained enough confidence to use the system on a day-to-day basis but we don’t go far and we will be back.

 

They will then continue to help with any query relating to the clinical systems via email, phone or logging in remotely. Queries may range from quick pointers in the right direction or complex protocol building. Ask anything relating to clinical systems – the Training Team will help.

Not only do new systems bring advanced functionality and better ways of sharing information, they continually evolve. Trainers spend time with Practices helping to match possible new ways of working with the surgery needs.

The result:

“We’ve made amazing progress and are feeling much more positive about our future with the system.”

As new developments are introduced to the systems the Trainers will revisit surgeries and help with understanding the impact and implications. Any staff affected will be trained and supported to make the addition of the new functionality as seamless as possible.

While continuously supporting our surgeries with their new systems the Trainer’s role is to help the practice staff work as accurately and efficiently as possible while they support their patients.

“The Trainer’s time with us has improved our working day and this has a direct impact on the people we serve as patients.”