Creating Forms in Word

The other day, I was approached by one of my training contacts – Audrey Bodman from the Outshine Group – for some assistance with one of her Word Questionnaires as, rather than getting her delegates to complete the form by hand (with the challenges that reading other people’s handwriting brings!) Audrey wanted the delegates to be able to complete the form on their computer & email it back. As you’ll appreciate from her web URL Audrey offers focused Telephone training to busy people that need to make lots of phone calls & who are more used to typing into databases than handwriting, so having an electronic form to complete fitted in more tightly with her delegates’ experiences & expectations.

As I’ve created electronic forms for other clients, too, I thought that this made for a good topic to share…

Creating the Form

In this case, Audrey had done a lot of the hard work in that she had already created a form ready for manual completion…

However, as you can see from the green boxes on the copy below, she’d had to leave large amounts of blank space where delegates could write in their answers…

… and, of course, these answers could be clearly written, scrawled, or in handwriting so large that there wasn’t enough space to wri

So, there were lots of benefits in having the form completed electronically…

Getting the form ready

As far as the document was concerned, the first thing that was needed to be done was to remove all of the blank spaces that had been left for a delegate to manually complete as once the document had been turned into a form, the space allowed would automatically expand to fit the content.

Then, before proceeding further with amendments to the document, we needed to get Word ready!

In order to create a form, the Designer Tab is needed & by default, this is hidden.

Making the Designer Tab visible

Our first step is from the File Tab

… select Options (depending on which version of Word you’re using, this may be in a slightly different place in the menu)

… this will bring up the Options dialog box, from where we select Customise Ribbon from the items on the left hand side & then click in the empty box next to Developer in the “Customise the Ribbon” group in the right hand column (these are the various Tabs that we can see at the top of Word

It’s then a simple case of clicking OK until we return back to our document & we should then see the Developer Tab between the View & Help Tabs

Adding Content Controls

Content controls, include check boxes, text boxes, date pickers, and drop-down lists & is the generic name given to the various fields that can be added into a Word document.

Plain Text Content Controls

On the Developer Tab
, having selected where the Control needs to be we select Design Mode, then a Plain Text Content Control

As the Content Control is clicked, it is inserted in the document where our cursor has been flashing

At this point, we can select the Properties associated with the Control & amend as necessary (including the ability to have multiple paragraphs should this be needed)…

We repeat these throughout the document wherever we want someone to add in comments

List Controls

There will be occasions where we want to guide the person as to their answer, whether it’s a simple “Yes/No” or a more complex set of options. There are two options that we can use, either a Combo Box or a Drop-Down List

They look the same within your document & are modified in the same way. The key difference between them is that the Combo Box will allow users to input their own answer (which may not be in the original list); Drop-Down boxes only allow selection from the items within the list.

Creating the options in either is the same process… click on the control & then select Properties then use the add button to add in the various options that you want…

… from a simple Yes/No response, through a 1-10 scoring system to something more complex

Finalising the form

Once we have all of the Content Controls where we want them, we need to finalise the form so that it can be used.

  1. The first step is to take the document out of Design Mode which you do by simply clicking on the Design Mode button (on the Developer Tab) to remove the shading

  1. The next step is to Restrict Editing to ensure that users can only type into the areas that you’ve set up or make use of the options in the Content Controls. This

When we select Restrict Editing, a task pane appears to the right of the screen

The key points are to ensure that there’s a tick in the box regarding Editing Restrictions & Filling in Forms is selected from the dropdown, we can then click the “Yes, Start Enforcing Protection” button.

This will give us the opportunity to set a password if needed (it’s not essential)

The document is now ready to use…

Editing the document

If you need to make any changes to the document, simply click the Stop Protection button (see image above) entering any passwords as necessary. You’ll then need to restrict editing again.

Analysing Tables with Slicers

A brief reminder

In this Tip, we first remind ourselves some of the basic analysis tools for tables before moving on to discuss Slicers.

Tables, you’ll hopefully recall, are Excel’s way of holding data in a clear & structured manner; If you need a “refresher”, here’s a link to November’s tip when we discussed Tables in Excel

The basic analysis options

Because they have a clear structure, tables automatically include a number of analysis features…

At the top of the table, within the header row…

  1. Each column in the table has an Auto filter arrow from which a drop down provides a number of options
  2. It allows quick sorting of the table in either ascending or descending order. It’s not necessary to select the whole table before applying the sort, Excel will select it all for you
  3. Depending on whether your data is text, numbers or dates, different filters will be available from a side menu that appears when you select the relevant filter
  4. Unique items are listed in the bottom window of the dropdown – if there’s a tick in the box, you can see the data. To only see a few items, it’s quicker to untick the Select All box & then pick the ones that you want. If you have a column of dates, you may find (as in this example) that they’ve been grouped into years (which will be calendar years rather than tax or accounting ones – you’ll need to use a lookup to create these)

At the end of the table in the Total row

By default, the Total Row is turned off – to turn it on, go to the Table Tools>Design Tab & tick the box

Doing this brings a Total Row to the foot of your table…

  1. By selecting Total Row on the Table Tools>Design Tab…
  2. A new Total Row is added…
  3. With a total in the end column of your table already selected (whether you actually want one there, or not!)
  4. This total uses the SUBTOTAL() Function (more on that later)

Adding more totals

To the right of each cell in the Total Row, there is a drop-down arrow that allows you to select a totalling method

Excel uses the SUBTOTAL() Function to provide the “total” selected as this automatically excludes any data that you have filtered out so the total will always be based on the values that you can see. However, as you can see, it’s not just “adding up” that’s possible, maximum & minimum values as well as variances & standard deviations can also be calculated.

A point to note with the Total Row is that when you add more data into your table, the TotalRow moves down automatically – you don’t have to insert any rows first and, because it’s using the Field Names in it’s calculations, you don’t need to adjust any total formulas, either!

Taking it further: Slicers

One of the problems with the filters that can be applied to an Excel table is that it can be difficult to spot which field is being filtered on; OK, you know that the table is filtered as the row numbers change to blue, but the filter buttons hardly seem to change…

Slicers have been available to use with Pivot Tables since the 2010 release; in the 2013 release of Excel, they were extended to Tables, too.

So, what do Slicers offer?

In simple terms, a quick & visual way of filtering data which allows the user to clearly see where a filter has been applied

This is the same filter as above, but this time using a Slicer. It clearly shows the Field Name, the options that can be selected & the filter that’s applied (the coloured “lozenge”)

Adding a slicer

From the Table Tools>Design Tab select Insert Slicer. If you’re unable to see these tabs, just check that you’ve actually selected a cell within the table.

This will then open the Insert Slicers dialog box which lists each of the headings from your table.

Clicking on the box next to the field name(s) and then clicking on the OK button will insert the slicer(s) for you and, at the same time, open the Slicer Tools>Options Tab

Here you can select different colours for your slicers, as well as increasing the number of columns of data per slicer & changing their height & width

Placeholder text in PowerPoint

Placeholder text to help with drafting your presentation

There will be times when, whilst you might have decided on the topics of your presentation, you’ve still not sorted out all of the detailed text that you want to include – perhaps you’ve an image & you need to find an ideal quotation that sums up what you’re trying to say so you might choose to have some placeholder text to help you get the layout sorted first.

Sometimes, it can be enough to have a simple text box with the words “My text here” (or equivalent) in it

As it’s November, I’ve picked a suitably evocative photo that I took one evening at the Thiepval Memorial on the Somme during my first visit to France. As you can see, I know what I want to the right of the image & I’ve even used the sizing options to give an idea of what it will look like, however, with only a single row of text, it looks a little odd.

So, whilst I’m finishing off my presentation & then doing a “Google” to get the correct text, is there a quick way of getting some placeholder text in to fill out that box?

Of course there is…

Generating random text

There are two ways that we can generate random placeholder text

Generate lorem ipsum Text in a Shape or Text Box

Very often when using PowerPoint, you quickly add placeholders to show where the text is going to be. For random text, you can go to a website and copy paste the famous lorem ipsum paragraph, but you can also generate it directly from PowerPoint and save a few minutes!

Just pick any shape, type “=lorem(x)” (where x is the number of paragraphs of placeholder text that you want) and hit Enter.


Generate random text

Alternatively, if you are not fond of Latin, you can use the “=rand(x,y)” function instead. Type “=rand(2,3)” to generate placeholder text with 2 paragraphs with 3 sentences each. Your shape will be filled with random text, preventing you from one more copy-paste.


You can then go & find the “real” text or quote that you want

Word’s “Backward P” and why it’s useful

Looking at formatting in Word

In last month’s Word Tips, I mentioned the “Show/Hide button” or Pilcrow (or “backward P”) & how it can be used to reveal the various formatting features that have been used in a Word document, so here we go…

First of all, a reminder of where to find it… it’s about half way across on the Home Tab

Apparently, it’s a very old mark, used for various reasons including Chapter headings, but nowadays, It is mainly used to be able to identify formatting issues in word processing documents that occur because users have hit the enter key too many times and can’t tell because they are not shown. Hitting the Show/Hide button allows the non-printing characters to be revealed. “Nonprinting characters” is Word’s term for anything that takes up space or has a formatting function but does not appear on the printed page, including spaces, tabs, page or paragraph breaks, etc.

In this blog post, I’ll go through some of the symbols which will be revealed by the Show/Hide button, what they mean and why you might or might not want them there.

Paragraph marks ¶

The paragraph mark represents a paragraph break. You should see one at the end of each paragraph (if there is not one, you’ll likely find that you have a problem). Ordinarily, you should not see one anywhere else. By this, I mean that you should not be ending lines with paragraph breaks, nor should you be using “empty paragraphs” to create “blank lines” between paragraphs (in most cases, this is better accomplished with Space Before or After in the Paragraph Formatting options).

The ¶ contains all of the formatting relevant to the paragraph. You can select it, copy it, and paste it onto another paragraph to copy and paste the formatting. The last ¶ in the document contains formatting for the entire document (header/footer and margin information, for example) or for the last section if there are more than one. Unfortunately, they look the same, so you need to be careful which one you’re using if you choose to use this copy/paste method!

Line breaks

A right-angle arrow pointing to the left represents a line break, inserted with Shift+Enter. You can use a line break or sometimes called a soft return to start a new line without starting a new paragraph. This is helpful when you want to start a new line within bullets or numbering, but don’t want to start a new point or have set your paragraph spacing to have “blank lines” as above, but don’t want them in this case, for example lines of an address…

Pagination breaks

These include things like column, page, and section breaks, which are a bit more obvious in their meaning. Pagination breaks are applied automatically by Word, or you can add these manually to control where the text should be pushed onto a new column, page or section. To delete these, you can simply select them and press the delete key.

Finally, you will sometimes see a small black bullet in the margin next to a paragraph. This indicates that the paragraph is formatted with the “Keep with next,” “Keep lines together,” “Page break before,” or “Suppress line numbers” property.

These settings are found on the Line and Page Breaks tab of the Paragraph dialogue box; accessed through the dialogue launcher in the bottom right corner of the Paragraph group on the Home tab. You can also double-click on the “bullet” itself to bring up this dialogue with the Line and Page Breaks tab selected. Word’s built-in Heading styles are formatted as “Keep with next” by default, so you will always see these bullets next to them.

Space characters

In most fonts, and certainly all Windows “core fonts,” a· small· raised· dot· represents· an· ordinary· space· Be sure you don’t have space · · characters · · where · · they are · · not · · needed. If you are tidy-minded, for example, you won’t want a string of them at the end of a paragraph where your thumbs relaxed on the spacebar while you stopped to think.· · · · · · · · · · · · · A degree symbol ° represents a nonbreaking space (Ctrl+Shift+Spacebar), which you can use to prevent words from being separated at the end of a line. This is useful for keeping dates together (so you don’t end up with November 19, 2018 being spread over two lines).

without seeing the formatting it looks like this


An arrow pointing to the right represents a tab character, where you have pressed the Tab key.

Cell markers

In tables, you will see one additional character, the universal monetary symbol (¤). Upon close inspection, a circle with four lines radiating from the corners can be seen: ¤. This is the end-of-cell marker. It is a little like the paragraph mark in that it contains paragraph formatting for the last (or only) paragraph in the cell, but it also holds formatting for the cell. The same mark at the end of each row is the end-of-row marker, which serves a similar purpose with regard to row formatting.


Another very important nonprinting character is the anchor symbol – when working with floating objects it’s often crucial to know where these are positioned relative to the paragraph or page. For example, as in the image below, the image has been placed relative to the paragraph, and hence the anchor symbol appears next to the beginning of the paragraph.

Don’t forget the basics

Before printing or emailing your document, it’s always a good idea to proofread it to pick up any obvious mistakes.

In fact, it’s best to proofread your documents twice; once for content, with nonprinting characters off (as they can be distracting when reading); and a second time with nonprinting· characters · visible, · so · that · you · can · check · for redundant line breaks, space · · · characters · and · the like.

How many times have you had trouble with a blank page at the end of your document and can’t figure out why?  Now you know why, by showing the paragraph marks you will likely find there are too many of them at the end and you really only should have one.

Tables in Excel

What are Tables

Tables are Excel’s way of allowing you to manage and analyse a group of related data in a structured way. You can turn a range of cells into an Excel table at the click of a couple of buttons & this then provides a wide range of analysis possibilities. They also make you focus on keeping your input data away from your calculations, leading to much neater spreadsheets.

What are the benefits of using tables?

There are a number of key benefits to setting up your input data in tables…

  1. The table is given a name (starts with “Table1” & increments as you add more) which you can change to something more memorable (I usually prefix with “tbl” so, for example a table of customers would be named tblCustomers). As more data is added, the range automatically expands to include it (whether adding more rows of data or additional columns)
  2. As the range automatically expands, if you refer to it in a VLOOKUP() Function or Pivot Table, etc, then your function or analysis will always be looking at the latest set of data, you don’t need to go back & change the range
  3. As you scroll down your data, the column letters (A, B, C, etc) are replaced by the headings from your table for ease of data input
  4. Autofilter arrows are automatically added to column headings
  5. When adding a calculation in a column of your table, it’s automatically copied into all of the relevant cells of that column & if you add a new row of data, the formulas are already in place
  6. You can add totals ate the foot of your table by adding in a Total Row which will automatically take account of new data

Are there any downsides of using tables?

The main one is that, at first glance, formulas look a little “odd”… rather than seeing, for example =F2*G2 your formula will be something more like =[@[Qty Sold]]*[@[Sold Price]] where the “@” symbol relates to “this row” & the items in the inner “square brackets” Qty Sold and Sold Price relating to the name of the field (i.e. the column name); these are known as Structured References. The reason why there is an “inner” set of [] is because there’s a space between each of the words & it’s Excel’s way of showing that this relates to the same thing. If you want to avoid that, then removing the space between each of the headings would mean that the formula above would be =[@QtySold]*[@SoldPrice] which is slightly neater.

Basic rules to follow when using tables

  1. Every column of your table needs a name that must be unique (but that’s just common sense!). If you insert a new row, to preserve this, Excel will initially call it Column1 which you can then change.
  2. The first row of your table should be the headings of each column (only use a single row for this, don’t try to merge cells, if you want the appearance of more than one row tell Excel to Wrap the text in the cell or use ALT+Enter to insert a line break within the cell
  3. Remove any complete blank rows or columns (sometimes users have a blank row to distinguish between different months, but this isn’t necessary & can cause problems in using your data effectively)

Creating a table

  1. Having selected a single cell in your data
  2. From the Home Tab
  3. Select Format as Table

Select the style that you want to use (this can be amended later) & Excel will bring up a prompt…

If you have no complete blank rows or columns in your data, this will have automatically selected all of your data. If there are some blank rows or columns, it’s a good idea to delete them first so that this process is automatic

Your table is now set up

  1. Filter arrows are added to column headings
  2. A Table Tools Tab has been opened
  3. The Design Tab has all of the initial options that you can use within your table
  4. It has been given a name (which you can change)
  5. You can choose whether to have a Header or Total Row, Banded Rows or Columns, etc

Changing the name of your table

To change the name of your table, you simply type the new name in the Table Name box (No. 4 above) & hit the ENTER key

I always prefix the name with tbl to differentiate it from other named ranges that I may have

Adding more data

To add more data to your table, you could insert rows & input the information (in the same way as with a normal list), however, the most effective way is to go to the last row & right most column & then use the TAB key

This will then take you to the first column of your table, but on the next row. In addition, the Totals Row (if you’ve used one) will have moved down a row automatically to accommodate your new row of data

Next time, we’ll look at some of the ways that we can use the features within Excel to begin to analyse our Table

Modifying the Quick Access Toolbar

The Quick Access Toolbar in PowerPoint

I briefly touched on the Quick Access Toolbar a couple of months ago in the “Quick Tips” section of the newsletter, but earlier today I was working on a PowerPoint presentation for a client & was making use of the ability to quickly add & remove items from it, so I thought that I’d go through it in more detail.

Although I’m using PowerPoint for the example, it works in exactly the same way in other MS Office programs

Modifying the Quick Access Toolbar

Whichever program you’re using, the Quick Access Toolbar is right at the top of the screen & comes with a number of icons already selected & in place…

However, by selecting the More button at the right-hand end of the Toolbar, a number of other options become available; to use them, simply click on them, a tick will appear next to them & they will appear on the Quick Access Toolbar…

Near the bottom of the drop-down menu is the More Commands option, this allows some additional flexibility…

On the right-hand pane, you can select an item that’s already on your Quick Access Toolbar (in the above example, I’ve selected Spelling) & then by using the Up & Down buttons to the right of the pane move where you want that item to appear (“Up” equates to “left” on the toolbar & “down” to “right”). This allows you to sort & group your Quick Access Toolbar into one that really works for you.

In the left-hand pane, you have all of the commands that are available from the current selection. The default is “Popular Commands” (as you can see just underneath where it says “Customise the Quick Access Toolbar” in the above image). Clicking on the down arrow brings up a list of all of the various Tabs

Selecting one of these (for example the INSERT Tab) brings up the various commands available from that Tab that you can then add to your Quick Access Toolbar

Short cut method to add items to Quick Access Toolbar

The above process is really useful if you’re building up your Quick Access Toolbar at the start of using the program – in fact, I selected ALL COMMANDS from the customisation as it allowed me to work through them all…

However, if you’re working on something in particular, as I was when I was inserting & manipulating some images, it can be more effective to use a short cut & that’s simply if you are using a menu item on a regular basis, right click on the item & select Add to Quick Access Toolbar. In this case, I’ve selected an image, gone to the Picture Tools>Format tab, selected Align>Align Center & then done a right-click to bring up the menu that allows me to add the button

And the button is added…

When you hover over the button with your mouse, you get the shortcut tip…

Short cut method to remove items from Quick Access Toolbar

To remove any item, simply right-click on it & select remove

Section Breaks in Word

Using Section Breaks to split up your document

I had a call from a client last week who needed his Word document to have both Portrait and Landscape pages within the same document as, in addition to narrative information, he had to include some large tables of data, so we needed to use Section Breaks to allow this to happen.

When he had reached the point where the first narrative ended & the table needed to be inserted, he had tried to simply change the orientation from portrait to landscape, but that hadn’t worked…

From portrait…

… to landscape

Unfortunately, this changed the whole document, which wasn’t what he’d intended; hence his asking me.

The key thing was, before changing the orientation, he needed to insert a Section Break so that Word would know to apply the change in orientation to that section only

Inserting Section Breaks

Section Breaks can be found on the LAYOUT Tab, just to the right of where you change the orientation of the page…

If you are wanting to change the orientation, then the NEXT, EVEN or ODD page section breaks make most sense (‘though if you choose CONTINUOUS & then change the orientation, Word will put the “Landscape text” onto a new page automatically). Whether you use EVEN or ODD usually depends on whether you have different page layouts in terms of Headers & Footers (for page numbering & titles, etc); if you don’t, then it’s as quick to select NEXT Page.

Initially, all that happens is a new page is created, however, when you then select LANDSCAPE from the ORIENTATION button, the new page changes to LANDSCAPE

At the end of your table (or whatever needs to be landscape (an image, map, etc), insert another CONTINUOUS Section Break, reselect PORTRAIT for the Orientation mode & your document will look like this…

Obviously, you can have more than one page in each Section and you can have more than one switch between Portrait & Landscape orientation within your document

Viewing Section breaks

If you want to see where your Section Breaks are, on the HOME tab, click on the Show/Hide button (also known as a “Pilcrow” – more on how this can be useful next month) & some of the formatting that you’ve used will be visible

Paragraph marks and Section Breaks can now be seen…

Changing case in Excel

Changing case to help printing

I’ve recently been doing some consultancy work which has involved copying some data from another software program into Excel. Unfortunately, the text was all in upper case & not only is this difficult to read, it physically takes up more space, so I decided to change its case it to a more readable format & at the same time reduce the space it took up. You may recall that in Word, there’s a simple button on the HOME Tab that allows us to do this (; in Excel we have to use a function in changing case of selected text

Figure 1 With capital letters, column A is 36.22 or 333 pixels wide

PROPER() Function

Using the PROPER Function allows us to use lower case for all but the initial letters…

It simply asks us to reference the cell that has our text in it.

Copying this down & increasing the column width appropriately gives us…

Figure 2 Text reduced to lower case letters with initial first capital letters

… a 17% reduction in the required column width. The benefit of this comes both from improved readability and especially when printing as the sheet is more likely to fit on a page, or require less reduction to fit

Related functions

There are two related functions to PROPER…

UPPER() Function

This converts the selected text to all uppercase

LOWER() Function

This converts the selected text to all lowercase

Nudging around the PowerPoint Grid

Using Gridlines for better positioning

A little while back, we looked at the various ways of lining up images relative to each other ( , however, there will be times when there’s only one image or you want to overlay a number of images & so that method won’t work. Fortunately, help is at hand on the VIEW command in PowerPoint where we can add Gridlines onto the PowerPoint window…

You’ve now got a set of gridlines to allow you to set things up exactly where you want them to be

Changing the grid

It may be that the default grid size isn’t small enough for what you’re wanting to achieve. In that case, click the expand button in the show group…

This opens the Grid and Guides dialog box

The other thing to consider at this point is the Snap Objects to Grid option – the first item in the dialog box

Snap to Grid

Selecting this option means that when you’re moving objects around on your PowerPoint slide, they will move in the increments that your grid is in; this means it can be far easier to line things up exactly.

On your PC screen, it’s sometimes difficult to see the difference unless you have the zoom set quite high… but of course, this becomes far more obvious once you’re running your presentation through a projector…

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…

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…

?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