Case Study: Hyde Community College

The Client’s problem

Hyde Community College (HCC) is a secondary school in Tameside with almost 1,000 pupils.

In common with many commercial businesses, HCC makes extensive use of data; analysing, amongst other things, pupils’ performance data in assessments & examinations. The raw data is derived from a download from an external system which is then copied into an input sheet from which a series of pivot tables are used to create reports allowing staff to drill down to individual subjects & pupils.

The processes undertaken involve setting up a new input sheet for the next data set & re-establishing the pivot tables.

Again, in common with many commercial businesses, this takes quite a lot of time.

How we solved it

We suggested that the input data be converted into an Excel Table which would then allow new data to be added to the foot of the previous data set. We also suggested an additional column be added to show which data set (or “track”) the data relates to. The next stage was to link the pivot tables to the Table, rather than the original data range, as that would ensure that the Pivot Tables always related to the whole data set, even when new data was added.

By adding a field for the data track, HCC could still analyse by the relevant “track” as before, but having a whole year (or several years’) worth of data in a single table means that individual pupil’s progress can be evaluated throughout their whole time in school

The benefits for the client

These were summarised by Mr Rorick Houghton, deputy head with responsibility for curriculum & data: The initial and most obvious benefit was one of time saving. I’d say that a conservative estimate of the time saved with the new process is 15 hours of work per data drop. This is completed six times per year, so a saving of 90 hours per year has been achieved.

In addition, the re-establishment of pivot table links and refreshing of formatting was prone to errors that at first glance would not always be obvious to us. This had led to flawed analysis in the past. The new process is much more robust, in fact error free, which in turn has increased staff engagement with our data analysis.

Finally, whilst this approach worked very well for this particular student data analysis, the enhanced understanding, by key staff, of the value of table formats has led to further improvements in other analysis carried out using excel across the wider school.

PowerPoint connections

Connections to make it work

So, you’ve built your PowerPoint presentation, it’s looking good, you’ve even tested that the various effects work seamlessly… and then you turn up at the venue only to find that you’re unable to connect your laptop to the projector that the venue has available & this is in spite of the fact that you’d confirmed that they had the relevant connections!

It was a meeting at one of my client’s last week which prompted this post – he’d just purchased a new projector to match the new offices & training suite that he’d opened, but wanted some advice on which cables to purchase to ensure robust connectivity; he’s quite unusual… many centres simply provide the cables that come with the projector… if any at all!

To be fair, this is one of the reasons why I & many others who are regularly needing use of a projector invest in one to keep in the boot of the car – it’s your tech & you know how to use it… but what if you have to use someone else’s tech?

HDMI or VGA

HDMI is the latest technology & provides a high definition image & many modern laptops only have an HDMI connector; but what if they have VGA… or the other way around?

HDMI or VGA

Initially, the choice will, very simply, come down to what connections are available on your laptop. If, as in the case with my latest piece of kit, you’re restricted to HDMI – given the increasing attempts to lighten the physical weight of laptops, more & more will only be providing this option, then that’s your starting point & it’s probably worth carrying a couple of HDMI cables & a connector to join these together in your bag

If you’re not using your own projector, then it may well be that the projector that you have to use at the venue only has a VGA connector… in which case an HDMI to VGA converter will be essential…

This is the one I got from the helpful people at Pimoroni https://shop.pimoroni.com/products/raspberry-pi-hdmi-to-vga-converter

 

Projectors

If you decide to purchase your own projector, try to ensure that it has as many connections as you can afford; this is the one that I use & as you can see it has plenty of connectors including both HDMI & VGA. In addition, it’s designed for wide screen projection (to match the layout that my computer has so that presentations don’t look “squashed”. If you’re prepared to pay more, then you can have 3D ready, lots of other connectors, smaller size, etc – at the end of the day, the key thing is: can you connect to it & is it bright enough? The projector that I use – an Epson EB-W05 WXGA (https://www.epson.co.uk/products/projectors/mobile/eb-w05) is 3,300 lumens & that’s bright enough to see an image very clearly & bright in a room with all the lights on.

Point and click

When delivering your presentation, you can choose to use a mouse (as I used to), but it’s far easier with a pointer – it frees you up & even has a laser pointer to allow you to draw people’s attention to areas of your slides

You just need to ensure that you’ve enough USB connectors on your laptop to plug it in…

So, a few pointers to ensure that, when onsite, you can actually connect up to deliver the presentation & ensure that all of that hard work wasn’t in vain!

Table of Contents in Word

When do you need a Table of Contents?

In my view, it doesn’t really make sense to go to the trouble of creating a Table of Contents (easy though the process is) until you’ve a document that’s a few pages long that incorporates a number of headings. Even multi page letters would probably look a little “odd” if they included a Table of Contents.

However, if you’re writing a report or proposal, then a Table of Contents on the first or second page (if you have a “front or title sheet” can make a lot of sense as it can not only aid your writing of the document, but, more importantly, allow your reader to quickly find the information that they need.

In the past, I’ve seen people manually typing Tables of Contents & then having to re-read their documents after any change to ensure that the headings are on the same pages & if not, adjusting their TOC’s accordingly; however, if we get them right from the start, that problem goes away!

So, how do we create them; And more importantly, create them properly?

Inserting a Table of Contents

Provided that you’ve been using Styles (we covered that in our last Word Tips posting) for any Headings or Sub-Headings in your document, then it’s very easy…

As you can see, we’re using the Styles set up following last month’s tips for the Safeguarding Association. When applying styles to headings, remember that the same style gets applied to the same level of heading; so your most important headings are likely to be Heading Style 1, your second most important, Heading 2, etc

Once you’ve got your document set out (‘though it doesn’t have to be complete – remember the Table of Contents will automatically update), you need to think about making space for it.

In this example, I’ve put my mouse pointer to the immediate left of the first letter “F” in the first main heading & hit the ENTER key

When I then select the line, it shows that it is still Heading 1, so I need to select “Normal” to reformat it; I may also choose to insert a new page here to put the Table of Contents onto a separate page (CTRL+ENTER is a quick way of doing this)

Then it’s over to the References Tab & Table of Contents

Whether it’s laziness or not, I invariably use the Automatic Table 1 to create…

As you can see, subheadings are indented & if the heading style is changed (for example a H2 becomes H1 or H3) or new pages are inserted or deleted, or even a section (including its headings) is moved elsewhere in the document, then clicking on the table of contents brings up the Update Table button & clicking on this allows you to either refresh simply for changes in page numbers, or more major changes (I tend to update the entire table each time “just in case”)

Navigation made simple

Once you’ve inserted a Table of Contents, by hovering your mouse over it, you’ll see that there is the ability to “jump” to it by holding down the CTRL button & clicking on the link…

In addition, by selecting (from the View Tab) Navigation Pane, an outline of the document appears in a right hand task pane allowing you to quickly navigate through the document. In addition, clicking & holding the left mouse button down on one of these navigation links allows you to move the heading & its associated text to elsewhere in the document

Conditional Formatting in Excel

Highlighting cells in accordance with a set of rules

That is, in a “nutshell” what Conditional Formatting is all about – the formatting changes based on a set of rules that you specify. This could be whether a cell value is higher or lower than a target, or a date before or after another or matching against other cells in a table to create a “heat map”.

Once you’ve set your conditions & how you want the cells meeting those conditions to look (for example highlighted in Red, Amber or Green), that’s it; Excel will then change the formatting of your cells in accordance with your rules

Simple Conditional Formatting

In simple terms, for Conditional Formatting to work, you need to be comparing values, so we’re looking for comparisons that are…

  • Greater than >
  • Greater than or equal to >=
  • Equal to =
  • Not equal to <>
  • Less than or equal to <=
  • Less than <

So, in this example, we want to know if the value in Cell A2 (the “Actual”) is greater than the Target (in Cell B2). So, having entered two values, we select Cell A2 (where we want the Conditional Formatting to be applied) & then select Conditional Formatting from the HOME tab. Them, as we’re wanting the cell to change colour, we select Highlight Cells Rules followed by, in this case, Greater Than

This takes us to the parameter fields for the conditions & formatting that we want to apply; initially, you’re prompted with a value which can be replaced with another or, even better, linked to your target cell

You can also select the type for formatting & as you can see, the idea of a “traffic light” system (Red, Amber – or “dark yellow” – & Green has been pre-populated)

Once this is set, as soon as the Actual figure exceeds the target, the required shading is applied…

Traffic lights

As has been mentioned, using Conditional Formatting, it’s fairly easy to create some sort of traffic light system… this can be useful if you’re looking at dates before an action needs to be taken – more than three months to go, it’s Green, between three & two months, it goes Amber & with less than a month to go, it turns red.

We have to be careful with dates & remember that Excel treats them as numbers… the further into the future, the higher the number… so, for example, in Excel’s world 30th June 2018 = 43,281, 31st July = 43,312

If we’re setting up this sort of system, then we’ll also find the TODAY function helpful (=TODAY() is all you need in a cell) as that will automatically show “today’s date” whenever you open the spreadsheet.

In cell E4 we have a calculation that resolves to less than zero if the due date is older than today’s date & we then use the Conditional Formatting>Highlight Cells Rules>Less than option to turn any that match that criteria Red

We’ve then use the Conditional Formatting>Highlight Cells Rules>Between option to highlight any that are between a lower value of 0 and 30 & we’ve used the drop down to show these as Amber

And finally, we’ve again used “Between” to shade those with a due date of between 30 & 60 days; any invoices due beyond those terms won’t have any shading

It’s also a good idea to have a key…

When creating the Conditional Formatting, the little button with an upward pointing arrow indicates that the value can be linked directly to a cell

On earlier versions of Excel this looked like this…

Simple Heat Maps

If you have a block of data – perhaps sales figures for a number of products over a number of months – & you need to highlight the highest & lowest sales quickly & easily, then the Heat Map (or Color Scales) that you can generate through Conditional Formatting might be just what you need…

A warning

When applying Conditional Formatting, in later versions, Excel simply adds them to the formats already created. This means that if you’ve made a mistake, it could still be there skewing your results, so it’s always a good idea to check by selecting Conditional Formatting>Manage Rules

And ensuring that the Manager only shows the rules that you’re expecting…

This is also where you can edit & delete specific rules

Lining up images in PowerPoint

Images in PowerPoint

As Telly Savalas used to sing (using the word “sing” in it’s widest context, and showing my age at the same time): “A picture paints a thousand words” and many of us, when designing PowerPoint presentations make use of images. In some cases, the image IS the slide & when delivering our talk, that’s enough to ensure that our audience “gets” what we’re talking about. However, on many occasions an image (or images) on a slide is only part of the story.

Of course, once we have an image (or especially more than one), we’ll probably want to line them up neatly & where we want them to be, rather than where PowerPoint necessarily inserts them.

Lining up images – the basics

First of all, we need to insert some images – I’ll pull a couple from my PC

❶Here we have three pictures – me with Slade’s drummer, Don Powell, one of the loudest drummers (& nicest blokes) I know and the equally famous (especially if, like me, you’re from the Midlands) Steve Gibbons – awesome songwriter & musician – and finally, me dreaming “what if?” at a recent High Peak Business Club meeting out at Chapel en le Frith Golf Club where Edwina Currie (who hosts the meeting) secured a talk by some of the team from Bentley Motors & they bought along some of their very nice toys with them – and yes, I didn’t just sit in it, I had a drive, too!

❷ If you’ve one of the later versions of Office, you have the advantage of the new “Design Ideas” window which automatically makes some layout suggestions (however, we’re going to ignore those and move the images ourselves)

Having selected the images that you want to move (to select more than one either click on each of them whilst holding down the SHIFT or CTRL keys or, hold down your left mouse button and “draw” an oblong around the images; when you let go of your mouse button, all images that are wholly enclosed by the oblong that you drew will be selected )

Having selected a picture (or pictures) the Picture Tools>Format Tab becomes active, however, if you’re already on the Home Tab, then you can make use of the Arrange option that appears there…

This allows you to ❶ move items in front or behind each other, ❷ to Group (as one image) or ungroup items or ❸ to align items relative to each other

Nudging them around

Sometimes, however, we don’t want or can’t use that straightforward top or left align. For example, if we only have one image. Obviously dragging & dropping works reasonably well, but sometimes we find ourselves wanting to move an image just a small amount & dragging & dropping is too crude… we could enlarge the view using the zoom command, however, a quicker solution is to hold down the Control (Ctrl) key and move the object using the arrow keys and your slide objects will move by just a pixel at a time.

Styles in Word

What are Styles in Word?

At their most basic, Styles in Word are a quick way of applying a number of formats to a font in one go. By applying a Style to a block of text, you can, at a single click, change it from the default “Calibri 11” to Arial, Size 16, Bold, with a blue colour & red underline… OK, that may be a little OTT, but once set up, Styles can not only speed up the creation of a document but also ensure that your document has a consistent “look & feel”.

However, in addition to “making your document look pretty”, Styles are also used by Word to allow you to quickly create Tables of Contents in longer documents by using the text that’s been marked as Headings to drive the details of the table of contents and, once set up, if you then add in further content, a refresh of your table of contents updates it with all of your new information. Gone are the days when you typed your own contents pages & manually adjusted the page numbers.

So, how do we do this?

Creating styles to use

When I’m talking about Styles in Word, I’m referring to this block of icons on the home tab…

If you look closely, you’ll notice a thick grey border around the letters & word “Normal” – that indicates that the Normal style is initially being used to do any typing. By selecting some text & clicking on any of the other buttons (& there’s a lot more that you can’t see from the beginning) you’ll immediately change it from Normal to look like one of the various Styles shown. In addition, built in to Word (& accessible from the Design Tab are a lot more pre-formatted Word Styles – some good, others less so.

So, back on the Home Tab, it’s possible to edit the default Word Style by Right Clicking on it & selecting Modify from the drop down.

However, I prefer to use the option above that & get Word to update the heading to match a Style that I’ve created within my document. I do it this way as it enables me to quickly see how each of the heading styles will look in comparison to each other.

So, the first step is to set out the various type of headings that you want in a blank document…

… and then change them to how you would like them to appear with different Fonts, colours, etc

To save me reinventing the wheel, I first went to the Design Tab & told Word to use the colour scheme for the Safeguarding Association that we created last time – this means the headings that I create will match the corporate colours of the Association. Then I went back to the Home tab & used the Font and Paragraph groups to change how each of my headings look…

So, this is now my headings set up & ready to use…

…all that I have to do is to match them to Word’s Styles… which I do by selecting each one in turn, right clicking on the Style that I want updating & selecting “Update Heading to match selection” from the drop down menu…

… and the Styles change to match how they look in my document

Saving your Styles

Once you’ve spent the time creating your Styles in Word, you’ll want to ensure that you can use them in other documents, so it’s back to the Design Tab & select the More button…

This then expands the selection & provides the option to Save as a New Style Set…

Which allows you to give it a name (so I’ve picked one that makes sense for this document) – leave the folder where Word wants to save it as the default (Word will find them more easily that way) …

And now, when I need to create a document for the Safeguarding Association, there on the Design Tab is the Style all ready to use…

Of course, I could (if this was the only Word Style set that I needed), make it the default by selecting the option

Comments in Excel

Making your spreadsheet understandable and easy to update

For many of us, building a spreadsheet can be a one-off exercise to answer a specific issue or calculate a value. However, for others, spreadsheets can be designed to capture a year’s worth (or more) of data. And then there can be an added issue… whilst we might be designing the spreadsheet, someone else will be responsible for inputting data and, even, perhaps, amending as time goes on.

So, these issues all raise a number of questions, amongst others, some key ones are…

  • How do we remind ourselves of what that long, complex formula does?
  • How do I remember where to get the data from to input into the spreadsheet?
  • What do I need to do next having input the new data?

The answer to all three is simple: Comments

Comments in Excel

You may already have seen a spreadsheet with comments in – you can tell as there are little red triangles in the top right hand corner of a cell if there is a comment there

If you then hover your mouse over the cell, you can see what the comment is…

How do I insert a comment?

You can insert a comment by going to the Review Tab in Excel and selecting New Comment

Alternatively, you can Right Click on the cell & select Insert Comment from the dropdown menu that appears

Whichever method you choose, you end up with the same Post-It style note linked to the cell ready for you to type in your comments

If your comment is larger than the standard sized space that you have, just select one of the fill handles (the little square boxes around the edge of the comment) with your left hand mouse button, hold that down & drag to resize. Once you’ve finished typing your comment, you click outside of the comment box & it will disappear leaving just the red triangle.

Editing a comment

Once your comment is in place, it’s easy enough to edit by, again, right clicking on the cell that has the comment & selecting Edit Comment from the dropdown…

… which opens the comment with the cursor flashing at the end of the text that is currently there

Once you’ve completed your edit, click away from the comment (resizing the box if necessary) to close it down

Deleting a comment

To delete a comment; again, right clicking on the cell that has the comment but this time selecting Delete Comment from the dropdown…

Printing comments

Unless you tell Excel to do so, comments will not print. If you need them printing, then on the Sheet Tab in the Page Setup options of the print menu, you can change the default “None” to have them printed at the end of the sheet or as displayed. Don’t forget that “As displayed” may result in them obscuring some of your data

Case Study: Printerbase Ltd

The Client’s problem

Printerbase is a very successful printer, print consumables & stationery supplies company.

Peter, the MD had realised that one of the issues that was constraining the growth & development of the company was him! He’d realised that whilst he was the “Key” person in the business, he’d become a “bottleneck” so, amongst other things he needed to free the business and himself.

The first part was to use the system developed by Sam Carpenter called “Work the System” & you can find out how that works here: www.workthesystem.com

The second part was to free up his time to focus on development & planning. So how could he reduce the 6 hours or so that he spent on month-end data analysis and have more time on planning the action to take as a result?

That’s where we came in.

How we solved it

In order to effectively analyse the business, Peter was using 7 spreadsheets – each targeted at a specific area of the business. However, on detailed analysis, it became clear that much of the information (at least with regard to the column headings) was duplicated, with a few additional columns for each spreadsheet to pick up specifically relevant information. This meant a lot of data entry by Peter which was taking a lot of time.

Having identified that the information came from the same source, we helped Peter create a single spreadsheet pulling in all of the information thus immediately reducing the input time & accuracy as the process only needed doing once.
Then, from this data, we created a number of Pivot Tables to provide the detailed analysis that Peter needed; it also made additional comparisons far easier to accomplish. And, once created, when new data was added for the following month, the Pivot Tables were simply refreshed & showed the up to date information.

The benefits for the client

In addition to reducing the number of spreadsheets in use, the time saving in data input & preparation for analysis has massively reduced, allowing far more time to be spent on looking at the results & analysing what they mean for the business & planning for this in a far more timely manner; what’s more, Peter has additional time available during the day to work on improving other areas of the business.

In Peter’s words: “Colin Foster came in to my business and spent 3 hours showing me how Pivot Tables could solve a particular business issue, this has not only helped us save several hours each month on this issue, but our new knowledge of Pivot Tables has helped us identify other areas of the business where they can be used and saved us countless hours of further time. I would definitely recommend anybody speak to Colin Foster regarding Microsoft Office training as it will definitely pay for itself.”

Case Study: Gericke Ltd

Summary

Using Excel to create a configurator tool to speed up quotations

The Client’s problem

Gericke Ltd. produce sifters, mainly for the food industry. Each model that they manufacture can have over 20 features that can be selected and various logical rules impact on which combinations are allowed. This, of course, means that the physical equipment can be tailored down to exactly meet their customers’ requirements; it also means that the pricelist is very complicated.

What they required was a spreadsheet-based configurator to be developed which would not only speed up the processing of quotations but use internal rules to ensure that invalid combinations of features were prevented. In addition, in the longer term, this would be used across other companies within the group without support from the Manchester site.

That’s where we came in.

How we solved it

Because this was a very complex process, the team at Gericke had already worked out all of the conditional logic that needed applying (“If Option 3 is a 1, 3 or 5, then Option 7 can be A or B, otherwise it’s C or D”) as they were doing that manually. Fortunately, this then meant that it was a relatively straightforward process to fit the necessary IF() Functions in place; less straightforward was the linking each of the various features to a set of “drop down” options in the main configurator because of the logical rules used.

The benefits for the client

That’s summarised in the following testimonial from Colin Arlott, Gericke’s General Manager who commissioned the work:

Colin Foster worked with Gericke Ltd to develop a spreadsheet-based configurator that made using a very complicated sifter price list much easier to use. There are over 20 features that can be selected and various logical rules disallowing certain combinations.  The configurator has sped up our sales office processing of quotations and should eventually allow our sister companies within the Gericke group to price up their own quotations without needing support from us.  We use the output of the configurator along with our CRM to formulate our quotations in a professional and effective manner.

Colin was easy to work with. He understood and clarified the brief efficiently and dealt with snagging promptly. The whole project took about three months from inception to go live.  We shadowed the existing manual approach for a month and now have the confidence to rely on the configurator entirely.

I would recommend Colin for developing stand-alone spreadsheet tools for SMEs.

Customising Word’s Themes to match corporate colours

What are Themes?

When using Word, whether you’re aware of it or not, you’re using a Theme – usually the Office default one. This decides what the basic fonts & colours will be and, used with Styles (which will be the subject of our next Word tip), can enable you to create a Word Template that matches with your corporate colours.

I’ve picked this idea as the theme (pun intended!) for this tip & the next as Kate Young of the Safeguarding Academy who wrote the Guest blog in my March newsletter has just launched the Safeguarding Association. When I saw the new logo for this I dropped her a note saying how much I liked it & that “If you then get the RGB colours, you could design your own Theme in MS Office so that whether you’re using Word, Excel or PowerPoint, they all look the same & then SlideMasters in PowerPoint & Templates in Word to complete” to which Kate & some of the other members of the trainer network that I belonged to suggested (even politely!) that I should explain how to do this…

Where can I see the theme?

So, the first place that you’re likely to notice the Theme is when deciding to change the Font Colour or a fill colour…

Changing the colours of a Theme

So, Kate had got her new logo…

… and details of the colours used…

So now it was off to work which meant heading to the DESIGN tab, selecting Colours and Customise Colours…

It’s the Accent Colours that we want to look at amending & you’ll also notice that we can give it our own name…

Fortunately, Kate had 6 colours in the new logo & we have 6 accent colours that we can change, so starting with the first…

Selecting the drop down next to the colour, we need to further select More Colours to allow us to get to the RGB colour scheme to pick the exact colours that we want

We then amend these to match those that Kate had & then repeat the process for the other 5 colours until we have…

As you can see the colours match with those that Kate wanted & I’ve also taken the time to give the Theme Colours a name & clicked Save

Now the colours showing when changing the colour of a font are those of the Safeguarding Association…

And, as we can see, here, they’re also available from the Design Tab in PowerPoint

Next steps

The next step will be to set up some Styles for use as headings, incorporating Kate’s specific Font & the above colours to enable fully colour & font coordinated documents to be produced – but that’s for next time!