Using Wildcards in Word Find and Replace

Wildcards to extend the Find & Replace options

Last month, you’ll recall, I took you through the process of using Find & Replace to help complete a sorting process within a document (as a reminder… http://www.us4b.co.uk/2018/08/sorting-in-word/)

In addition to the basic options, we can use Wildcards to represent certain characters within our search to quickly widen & speed up the process

How do we use wildcards?

Let’s look at an example using the more familiar options: The question mark (?) and asterisk (*).

The question mark matches any single character; the asterisk matches any group of characters (commonly called a text string). Word looks past the asterisk to see whether the search is limited by any other characters.

For example, searching for wo*d finds text such as word, world, and worshipped. Press Ctrl+H to open Find and Replace dialog box:

1Find and Replace in Word 2016

To use these wildcard characters, select the Use wildcards check box in the Find and Replace dialog box:

2Find and Replace more options in Word 2016

These wildcards are handy for finding words that you don’t know how to spell.

For example, if you are not sure how to spell receive, you can type rec??ve. Word then locates any word that begins with rec followed by any two characters followed by ve.

What Wildcards can be used?

There is a wide range of wildcard characters that can be used as follows…

WildcardUseExample
?Any Characterd?g finds dig, dog, and dug
[-]Character in Range[a-m]end finds bend, fend, lend, and mend (the first character in this case is a, m, or any letter between)
<Beginning of Word<tele finds telemarketing, telephone, and television
>End of Wordtion> finds aggravation, inspiration, and institution
()ExpressionLets you “nest” search expressions within a search term. For instance, <(pre)*(ed)> to find presorted and prevented
[!]NotFinds the text but excludes the characters inside the brackets; t[!ae]ll finds till and toll but not tall and tell
{n}Number of Occurrences.Finds the specified number of occurrences of the letter immediately before the {; to{2} finds too and tool but not to
{n,}Number of Occurrences.Adding a comma after the number tells Word to look for at least that number of occurrences; a{4,} finds four or more of the letter a in a row
{n,n}Number of Occurrences.10{2,3} finds 100 and 1000 but not 10
@Previous 1 or More.Finds one or more of the character immediately preceding the @ sign; ^p@^t finds one or more paragraph break marks followed by a tab mark
*0 or More Characters.Finds a word with one or more of the specified character, or words with none of the characters; des*t finds descent, desert, dessert, and destruct
[]One of the specified characters.b[aeiou]t finds bat, bet, bit, and but
[!az]Any single character with the exception of the ones in the range inside the bracket.m[!oz]st finds mast and mist but not most or must

Errors in Excel and how to use them effectively

Making use of errors

OK, so logic says that you don’t want to have any errors in your spreadsheet & broadly speaking, that’s correct… however, there can be occasions when an “error” can be helpful.

As we saw in last month’s tip, the #N/A error in a VLOOKUP Function means that the item that you’re looking for can’t be found in the list that you’re checking. If you’re using Excel to help with a bank, supplier or other sort of reconciliation, then that can be actually quite useful as it shows the items that are in one list & not the other. We can then use an IF or IFERROR Function to decide what to do with the result; if you can’t remember, then check here: http://www.us4b.co.uk/2018/08/dealing-with-errors-in-lookups/

Error types

#NAME? error

The #NAME? error occurs when Excel does not recognise text in a formula.


Simply correct SU to SUM.


#VALUE! error

Excel displays the #VALUE! error when a formula has the wrong type of argument.


Change the value of cell A3 to a number or use a function to ignore cells that contain text.


#DIV/0! error

Excel displays the #DIV/0! error when a formula tries to divide a number by 0 or an empty cell.


Change the value of cell A2 to a value that is not equal to 0 or prevent the error from being displayed by using the logical function IF.


Explanation: if cell A2 equals 0, an empty string (“”) is displayed. If not, the result of the formula A1/A2 is displayed.

#REF! error

Excel displays the #REF! error when a formula refers to a cell that is not valid.

For example, cell C1 references cell A1 and cell B1.


If column B is deleted (to achieve this, right click the column B header and click Delete.)


In the “new” cell B1 (which was C1 before we deleted column B) the reference to cell B1 is not valid anymore.


To fix this error, you can either delete +#REF! in the formula of cell B1 or you can undo your action by pressing CTRL + z

Using the error type in a formula

Here’s a short table with the “odd” calculation or two

As you can see, I’ve used the ERROR.TYPE Function to display which error is showing. We can then use this, perhaps with a VLOOKUP to generate an informative answer…

When is an error not an error?

Sometimes, when entering data (especially if it’s a date) into a cell, you’ll see ########

There are two reasons for this. The first is that the cell column is too narrow to display the value. The fix is simple: just expand the column to fit.

The second reason you could see ######## is if a date-formatted cell becomes negative. Usually this happens when dates or times are subtracted from each other (for example if you’re working out the difference between two dates. Excel will usually initially format the calculation cell to match the date formats; you’ll just have to change it back to something more useful.

Screenshots in PowerPoint

Easily capture Screenshots in PowerPoint

There may be occasions when the image that you need in your presentation is best served by screenshots of your screen – this can be especially true if you’re demonstrating some software. Ideally, you’d have the software itself available for an ideal demo, but perhaps this is part of the “pitch” or you need to explain a few things first before launching into the actual program.

Some of us use a program called Snagit available from Techsmith (https://www.techsmith.com/screen-capture.html) and, I have to say, it’s a fantastic piece of software (I use it myself to help with these blogs). However, what if you’ve not got the budget (or general need) for that? Well, you can use one of the features in PowerPoint itself.

Insert a screenshot

Yes, the hint is in the heading: you’ll find screenshots on the Insert Tab in PowerPoint (and in Word to be honest!)

When you click on the Screenshot icon, a dropdown selection frame appears that shows thumbnails of each window that you have open at that time and, if you hover your mouse over one of these, the name of the document or file will be shown, together with the program that is being used

Clicking on the thumbnail inserts it into your presentation

As the thumbnail has been inserted as a picture, you’ll notice that the Picture Tools>Format Tab has now become active and the full window of the screenshot that you selected has filled your slide. From this point on, it’s just a picture, so you can resize, crop, move around and all of the other things that you might want to do with an image

But I only want part of the screen in my presentation

There are two ways in which we can achieve this…

The first is by going through the above process and then using the Crop tool to remove those parts of the image that you don’t want to see…

… and then compress the image (to save space), deleting the cropped area of your picture at the same time.

You can then enlarge the image that remains, however, as you can see, below, some blurring of the image can occur… so whilst this works, it’s not, perhaps, the ideal solution

Screen clipping

For this, we’ll have to use a slightly different method and, rather than inserting the thumbnail as we did above, we use the Screen Clipping feature

It’s found at the foot of the Screenshot menu. When you choose Screen Clipping, your entire window will temporarily become opaque or “frosted over.” After you select the part of the window that you want, your selection will show through this opaqueness. When the screen turns white and the pointer becomes a cross, press and hold the left mouse button and drag to select the part of the screen that you want to capture. The part of your screen that you selected now appears as an image on your slide

Sorting in Word

What do you mean, Sorting in Word – that’s Excel, surely?

Normally, I’d agree with you – we use Excel for “Number crunching”, working with lists, etc and we use Word for writing articles or letters – or even Blog posts – but occasionally, it can be very useful to take advantage of the ability to do some sorting in Word.

Helping out a friend

Some of the readers of this blog will be aware that, away from the mysteries of Microsoft Office, I’m researching the history of some Allied Prisoners of War in Normandy (if you want the details, please check out www.menintheshed.com). Whilst I was last in Normandy, a friend asked for my advice. He’s in the middle of writing a book & was having a bit of an issue… We’d discussed using Styles for Headings, Themes, Tables of Contents, etc (in fact, he’s subsequently signed up to this newsletter), however, as he explained, it wasn’t the “look & feel” of the document that was his immediate problem, but the fact that the paragraphs in the introduction & first chapter of his book had become mixed up & were now out of order. What could he do?

Cut and Paste or sort?

He showed me a printout of the relevant pages which he’d then marked up to show which paragraphs should be where… it looked a bit like…

… but it went on for a lot of pages…

If it had been just a short document, I’d have suggested using Cut & Paste or Drag & Drop to select the paragraph in question & then dragging it into position; however, there were far too many pages & paragraphs to make this a sensible suggestion.

So that meant we had to do something unusual… Sorting in Word

Sorting in Word

The first stage was to edit the Word document, so that each paragraph had new start characters – the paragraph number that it should be (rather than what it currently was) with a “#” either side (I’ll explain the “why” of that in a short while…

The next step is from the HOME Tab to select the AàZ sort option

You’ll notice that, if I don’t specifically select any text in the document, Word presumes that I want it all & selects it for me as part of the process…

Then, rather than accepting the default “Text” Option, click the Dropdown arrow & select Number

When you press OK, the result is a Word document sorted into the order that you want…

Using Find and Replace to do a final tidy up

It’s then simply a case of removing the numbers that you added to help with the sort… and that’s where the two “#” characters at each side of the numbers come in to play. Your document may well have numbers in it & we’ll want them to stay where they are, however, it’s these additional numbers that we added to specifically sort our document that we now want removing as the “chance” that you will have a meaningful use of #Number Number# is probably quite small. We could go through the document & remove one by one by selecting them & deleting, however, it’s far quicker to use Word’s Find & Replace feature together with a special Wildcard character to do this in one go for us…

Selecting More>> allows us to tell Word to use Wildcards…

Selecting Replace All, now removes the #Number Number# sequence from our document

Next time, we’ll look at some of the other Wildcards that can be used in Find & Replace

Dealing with Errors in Lookups

The #N/A Error

If we use the VLOOKUP Function to match between two sets of data – then occasionally, Excel will return an #N/A error where it can’t find the value in one list in the other one

In this example, we have two lists of employee data where in the “Month #2” block, there’s a calculation to see if the current employees are the same as last month (simply by checking whether their Employee ID is the same. If it is, in other words the employee was also employed last month, then it brings their Employee ID over; if not, then it shows a #N/A error as it’s unable to find the required value (in this case 1243 or 1244) in the ID column for Month #1

If this was just for “internal” use – for example, it was a quick check that I was doing to see who the new people were (or perhaps I was using it to compare details on a bank or supplier’s reconciliation), then I’d probably leave it like this… I know why the error has occurred & all I need is to know which the relevant entries are.

However, if this is for “external” consumption, or, perhaps, for someone who needs to know what the #N/A error relates to, then we need to do something further. The “old way” was to use an IF Function with an ISERROR one to generate something like this…

=IF(ISERROR(VLOOKUP(F4,$A$4:$C$12,1,FALSE)),”New Employee”,VLOOKUP(F4,$A$4:$C$12,1,FALSE))

This checks if the VLOOKUP generates an error & if it does, it returns the text string “New Employee”, if it doesn’t, it returns the value that is the employee ID

However, to say the least, that’s a little “clunky” (and easy to mistype!), which is why the IFERROR Function was created

The IFERROR() Function

The IFERROR() Function does the above in a far simpler way, the syntax is…

IFERROR(value, value_if_error)

Or, in English: If the calculation works, then show its result; if it doesn’t then show this value

So, our error handling calculation above would simply become…

=IFERROR(VLOOKUP(F4,$A$4:$C$12,1,FALSE),”New Employee”)


A quick note on Text Strings

In each of the error handling methods above, if we wanted the phrase New Employee to appear, we need to enclose it in double quote marks (SHIFT+2) to signify that it is a text string. If you don’t do this, you’ll risk creating a different error: #Name? where Excel thinks that New Employee is the name of a range that you’ve set & it generates the error because it can’t find it.

We’ll discuss different error types next month

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.