Planning Simple Projects with Trello or Microsoft Planner

At some point in your working life you will be involved in a project of some sort. The type and size of project will vary wildly from a list of “to-dos” through to multi-site, multi-year projects.

Managing these different projects requires different skills and also different ways of keeping track of things. There are lots of tools available out there. The problem is deciding which one to use.

If you are involved in the likes of managing the building of the Olympic stadia, or a new rail line/network, the likes of Primavera might be best suited, allowing you to build multi-site, multi-project programs. Working on single, long term projects with a requirement to track resources and costs, then something like Microsoft Project or similar might be your best option.

For many of us though, both of these would be a serious case of over-kill. The cost of licences etc. is also prohibitive which in turn means that only a very limited number of people are likely to have access to the information.

So, what is available to you for quick simple projects?

This is where software like Trello, Microsoft Planner or Asana might be the tools for you. They are all very similar in their functionality and for two of them come in either free or paying version. I’m going to look at Trello and Planner.

Trello

This is an online tool. There is a free version and a chargeable version with added functionality beyond the standard free one. You’ll need to create an account and then log in to your account to view the planning boards.

Click on Welcome Board and this opens a useful hints and tips page to give you an overview of everything that can be done within the application.

The left hand column has a number of cards with hints and actions to perform to help you discover the basics of Trello.

To create a new project, click on CREATE NEW BOARD. This will start what Trello calls a board. Think of this as a notice board where you can pin job cards. Create new cards by clicking on ADD A NEW CARD. Give your task or activity a name and click the ADD button.

Each card that you create can then be edited to add more detail around the job or activity that needs to be completed. To view the details card, click on the job card.

Here, you can add a lot of detail about the task;

• Members – who will complete this task?
• Labels – highlight the task with colour coded labels
• Checklist – add a task specific to do list
• Due date – when does this need to be completed by?
• Attachment – add any relevant files/images etc.

Using the buttons on the right hand side add as much detail as required.

Members: you can invite people to be part of your project team, and once they are on board, you can assign tasks to them. Each task can have multiple people assigned to it.

Labels: These are coloured tags that can be assigned to the card. Each tag can be customised with text. There is an option to use colours suitable for colour blind people.

Checklist: add lists of actions that might be required to complete the task. These could just be a list to remind you, or a breakdown of everything that needs to be done.

If something on the checklist ends up as quite a big task in its own right you can use CONVERT TO CARD, making it a new, separate task in the project.

Due Date: when does this need to be completed by?

Attachment: add any attachments that might be needed or are relevant to the task in hand.

There is an area for comments. People can add comments, attachments, mention people or add emojis. This gives the task more of a social element where team members can contribute or comment about the task.

If you want to kept up to date with changes etc. in the activity then you can SUBSCRIBE and get notifications.

Depending on how many things you want to display on the card you will see a number of icons etc. showing you a simple visual summary of the activity.

Back on the main board, you have a number of icons on the right hand side:

• People
• Activity
• Power-Ups
• Filter
• Stickers
• Settings

People: displays project team members

Activity: shows any activities that have taken place or comments added.

Power-Ups: You can add extra functionality to Trello. If you have the free version, you can only enable one power-up per board. To add more than one then you need to upgrade your account to TRELLO BUSINESS CLASS. There is a long list of power-ups available. Here is just a short sample…

Filter: Use the filter to quickly find things within your project – keywords, tag colour, people or due dates.

Stickers: Click and drag stickers onto your cards.

Settings: A number of options such as changing the background appearance of your board, controlling member permissions etc. Again, there are a number of options that are only available to business class members.

So, what do you get if you choose to use Microsoft Planner?

Planner

If you have used Trello, then using Planner is almost the same. It has the distinct advantage of being fully integrated into Office 365 and all its applications making collaborative working simpler and creates a smooth workflow across multiple applications within a business. Unfortunately, I cannot do a direct comparison to the Business Class version of Trello as I don’t have it. For the purpose of this blog I am more interested in the general functionality of both applications.

To access Planner you need to be using the enterprise version of Office 365. Log in through the portal and click on the Planner icon.

When you open Planner it will take you to the PLANNER HUB or landing view for the application.

Here you will see any plans that you have either created yourself or are part of. If you are involved in a plan click on the plan name to see the details. If any of your plans are ones that you go to regularly etc. you can make them a favourite by clicking on the three dots next to the name and selecting ADD TO FAVOURITES. This will add a shortcut in the left hand panel as well as display a mini dashboard. Although limited in functionality and detail, it is a useful feature which is not present as a standard feature in Trello (free version).

To remove a plan from your favourites click on the three dots in the mini dashboard and select REMOVE FROM FAVOURITES.

You can create a new PLAN by clicking on NEW PLAN. Complete the details on the new window to name the plan, add an optional description and decide whether you want members to receive mail notifications generated by assigning tasks etc.

Whereas in Trello you create a new board, in Planner you create a plan.

Give your plan a name, and decide whether the plan is open to all to view or limited to team members only. When you look at plans on the PLANNER HUB, any plan icons with a padlock are limited to members only.

Once complete click on CREATE PLAN.

When a new plan is created, a group is set up with its own calendar, mailbox, SharePoint area etc.

When you start a new plan the initial layout and way to get started are the same as Trello. Planner uses BUCKETS. By default, you start with a TO DO bucket where you can add tasks. Think of the buckets as top level summary tasks. You then add detail to each bucket by adding tasks. To add new buckets click on ADD NEW BUCKET.

When you create a new plan the default bucket that is created is TO DO. You can rename this by clicking on TO DO and overwriting it to whatever your task summary name you want to give it. To add a task, click on the plus symbol.

You will ned to build a team so tasks can be assigned to the various team members. Click on the MEMBERS arrow and type in the name of someone you want to invite to the plan. As you are linked to the enterprise, the names of every employee should be listed, so just start typing a name and when the name appears click on it to add it to the list.

Now, when you create a task you can name it, select a name or several names to assign the task to, and set a due date. Then click on ADD TASK. This is the bare minimum required. Just like Trello, click on the task card to reveal a details window.

You can set the PROGRESS status for the task (Not started, In progress, Completed). Set a START DATE if necessary.

You can add a more detailed description if that is of help.

Add a checklist if you want to break down the task a bit.

Tick the SHOW ON CARD option if you want people to see that there is a checklist.

If an item on the checklist is later considered too big a task to be a checklist item you can convert it into a separate card. Click on the item to select it then click on the PROMOTE ITEM arrow over on the right hand side.

This will convert the checklist item into a new task that can be edited and assigned like any other task.

Add any useful or relevant attachments. If you tick the SHOW ON CARD option you will get a preview of the file appearing on the card.

Comments can be added to the task. These will form a conversation within the task.

Depending on the number of options you selected to be displayed on the card you will see something like this for each task. Note that you can only display one of the SHOW ON CARD options at a time at present.

You can switch views from the BOARD with all your buckets and tasks, to the CHARTS view, which is the mini dashboard giving you a quick overview of the plan.

Click on CHARTS to get this;

Obviously, the more tasks and assignments you create the more information will be displayed here, but it’s a nice feature to get a quick summary of the plan in a visual format.

Use the drop down GROUP BY option to change how you see your plan’s status.

This is a useful feature so you can quickly review your plan’s tasks in a variety of ways. To quickly highlight any tasks assigned to a specific person, go to the MEMBERS area.

Click on the image or the initials of the person whose tasks you want to see and in the window below, any tasks assigned to that person will turn grey.

This will work in any layout view.

So, whether you choose to use Trello, Planner or Asana, it’s down to the individual people who were assigned tasks to do them and update them so everyone involved in the project is aware of what is going on and up to whoever the project manager or equivalent is to oversee everything. At the end of the day, no software, however good, manages a project…people manage projects. The tools are there to hopefully make managing them a bit easier.

If you want to look at Asana for comparison go to Asana.com. This comes in three versions: free, Premium and Enterprise. Below is a screenshot taken from their website. A you can see it has a similar feel and layout to Trello and Planner. As with Trello, some of the neater features such as creating dependencies are only available in the paying versions.

Planner is probably the one with the fewest features at the moment, but is relatively new and there are regular updates so no doubt over time, Planner will add a whole raft of features to bring it more in line with the likes of Trello and Asana. The main advantage that Planner has over the other two is that is fully integrated in the Office 365 suite of applications so working from one application to another is pretty much seamless…you just have to be on an enterprise version of Office 365, whereas the other two are standalone packages.

The choice is yours.

 

 

One Note – The forgotten tool in the Office suite

If you’re already using One Note then you will know how useful it can be and also how easy it is use. If you are amongst the majority who have never used it or as is often the case, not even noticed it is installed on your laptop or PC, then read on…

As the name suggests it’s a notebook, but it offers a lot more than just somewhere to jot down the odd comment or two.

One Note allows you to organise data, notes, web links, images etc. into sections, with each section containing pages. Each page is quite literally a blank canvas where you can add notes etc. anywhere you like. So unlike Word which is fixed, in other words, you start writing at the top of the page unless you press enter 10 times to start further down the page, you can start typing anywhere on the page, just like a paper notebook, with the advantage of being able to move or copy notes anywhere within the page, notebook or even to another notebook.

Let’s look at the structure of a notebook;

•  First of all you can have multiple notebooks.
•  Each notebook can be divided into multiple sections.
•  Each section is made up of pages and sub-pages.

There is a great diagram showing how a notebook is structured within One Note itself which you can see below.

Notebook structure

To create a new notebook go to FILE, NEW and set where the notebook will be created (web, network or My Computer), give it a name and click on CREATE NOTEBOOK.

To add new sections just click on the tab with a star on it or right click on an existing tab and select NEW SECTION. When the tab is created the name of the section is active. Type a name for your new section and press enter. You can rename a tab at any point by right clicking on the tab and selecting rename.

Section tabs

By default, a section has one page. Rather than click or anything else on the tab on the right hand side of the page, you type the page name or heading in the dotted box in the top left hand corner of the page. To add new pages, click on NEW PAGE above the page name tabs and use the same steps as above to name your new page.

Once you have your basic structure, it’s down to adding content.

You can add all sorts to your notebook;

• Text
• Tables
• Pictures
• Screen clippings (using the Snippet tool)
• Links
• Files (as attachments)
• Files (readable – whole printout)
• Scanned documents
• Voice memos
• Video memos

All the insert options in the Ribbon

Note there isn’t an insert text option – just start typing wherever your cursor is.

To help you flag content, you can add tags. You’ll find these on the HOME tab. You can easily create to do notes, highlight questions etc. You can add new ones and you can move tags that you use regularly to the top of the list so you can use a keyboard shortcut to add the tag. Once tags are in place you can search by tag name to find them quickly again.

List of available tags

There is also a DRAW tab, which gives you a number of drawing tools. Pick from various pens, colours and thicknesses to create free hand drawings, as well as draw shapes and graphs. Obviously, this will work much better if you have either a touch enabled screen or a graphics tablet rather than trying to write or draw with a mouse which is pretty hopeless to say the least.

The Draw tab

So that’s pretty much everything you can add to a page from within One Note.

Also note that One Note automatically saves anything you do, so don’t go looking for the save button. You can do SAVE AS to save pages and sections etc. as new pages, new sections or even new notebooks.

Now…where One Note sits high above all the other applications is its level of accessibility across applications and devices.

Just look for the One Note buttons in most applications.

Outlook:

You receive a mail that relates to a project you are tracking in one of your notebooks and would like to have a copy of it along with your notes – just click on the One Note button and the open mail item will be copied. You will be prompted to select the notebook and page to send the mail to.

Pick where you want to save a note

The e-mail in its entirety will now be in your notebook and can be annotated etc. like any other note.

Alternatively you can e-mail pages directly from One Note. If you have minutes from a meeting, action points etc. in One Note just click on E-MAIL PAGE – no need to copy/paste the contents or re-write them.

Internet explorer:

In IE (bit not Firefox) you can create notes that are directly linked to a web page (One Note linked note), or even send the web page itself to One Note. As with e-mail you’ll be prompted to select a specific page in a notebook. Just right click on the web page and select whichever option you prefer.

PowerPoint:

From within PowerPoint you can add LINKED NOTES. As always, select a page to add the linked note to, start typing something and One Note will add a PowerPoint icon next to the note. Then, from within One Note you can click on the icon and it will open the presentation at the slide you created the linked note from.

Word:

Like PowerPoint, you can create notes that have links to documents created in Word. Look for the Word icon next to a note and click on it to open the document at the page relating to the linked note.

Mobile Devices:

You can download One Note to your mobile phone – for both Android and iOS. OK…it doesn’t have all the features of the desktop version but it’s great for quick notes, sending images etc. while you’re out an about. You can view all your notebooks and make edits through your phone.

Screenshot of One Note on an iPhone

The final great feature of One Note is that you can access your notebooks from any laptop, PC where you are a user. You can even install One Note on both Android and Apple phones and link it back to your notebooks. The convenience of this is that you can be anywhere – see something interesting, or suddenly come up with an idea, you can make a note, record video or a voice memo and save it straight to One Note which is then automatically synchronised to all your other devices. Even if you don’t have access to any of your devices but can get on the internet, sign in to your Microsoft Live account and access your notebooks from any PC, laptop or tablet without the need to even install One Note. If you have shared notebooks then all of One Note’s features are available to all team members. Brilliant, and so convenient it’s untrue.

Cross device connectivity

For those of you in a work environment you may not be so lucky. Depending on network and access restrictions, you may find yourself limited to using One Note on your PC or laptop only, and unable to create shared notebooks, or link to mobile devices as it all depends on having a Live account which most businesses will not allow. If you can create networked notebooks, then this can be very useful if a team of you are working on a project. Each team member can update files, add comments etc. to the notebook, and any added comments have tags showing who added the comment, as well as the date and time it was added. This makes One Note a great collaborative tool, even if you are restricted to your network. If you have access to the full range of functionality including mobile apps then even better.

One Note has the potential to be a very powerful tool across multiple users especially for remote teams who need a simple and convenient way to keep in touch and share files, thoughts, images etc. quickly and easily. Even if you can’t use it to its full potential, simply using it as a private notebook is an excellent way to manage all your projects and all kinds of information in a variety of media types.

As a quick note, if you have Windows 10 installed, One Note is pre-installed, but this is only the app version not the desktop version. It works fine, but you don’t have all the functionality of the full version. You will need to download the desktop version direct from Microsoft.

So if you haven’t tried using One Note yet, take a look. Once you start using it you’ll find out just how easy it is to use and how useful it can be.

Excel – Dealing with Non-Printable Characters

Every so often you may encounter a problem with Excel where a function is not working properly, even though you know that your function syntax is correct. And when you manually check the data you find that the function is not returning the correct answer.

So what is going on?

This problem happened to me again recently and it took a little while for the penny to drop as it is fairly rare, but highly annoying when it does.

And it is down to something called “non-printable characters”. These pesky characters are invisible, which makes it doubly difficult to identify. These tend to crop up in reports that have been downloaded from other systems, which for reasons unknown, slip in characters of some sort that cannot be seen or identified on screen, but nevertheless cause mayhem with a number of functions.

As an example I’ll use the data which caused me the most recent problem.

Sample data causing the problem

The company I work for uses a web based database to manage courses and delegates and some information was extracted to generate a catalogue within a workbook (sample above). I wrote some VBA code to help users find courses either by name of ID number and in one instance, the dreaded end-debug screen kept on appearing.

The form I created allows users to enter partial names or IDs and search for information based on that partial string. When they entered the number 26, a message told them that two records had been found.

Message returned by the VBA code

This was done using a COUNTIF function. However, there were in fact 6 records that contained the number 26.

…but autofilter finds more records

So on the one hand the COUNTIF function was finding 2 records, but the loop in the code which populated an array in the memory was crashing as it found a third record that contained the number 26. The dynamic array was not big enough as the size was recalculated based on the findings of the COUNTIF function.

Initially I looked at formats – text vs. numbers for example.

I also looked for any spaces that might have caused an issue.

I manually added a COUNTIF function in the worksheet to double-check the findings of the function created in VBA but this gave the answer 2 as well.

=COUNTIF(C:C,”*26*”)

Finally, realising that this was an extract from a non-Excel source, I tried using the CLEAN function suspecting that there may be some non-printable characters in there somewhere. The CLEAN function removes all non-printable characters from text. The syntax is very simple:

=CLEAN(cell reference)

In a new column I wrote this formula and then copied all the “cleaned” text values and used PASTE SPECIAL VALUES over the original cells and retested the COUNTIF function.

…and hey presto…it found all 6 records.

So next time your functions are not working and seem incapable of identifying values in cells, try applying the CLEAN function to your data, it might just be the solution to your problem.

Excel Guest Blog -Spreadsheet Passwords (Facts about Protection) by Tom Urtis

Password security

Spreadsheet password protection is a topic of major concern for Excel users, rightly so. Information in worksheets can be confidential, needing to remain undisturbed with formulas that must be protected from deletion.

It’s wise for an Excel user to voice his or her curiosity of spreadsheet protection, or has questions about just how secure a password-protected spreadsheet really is. When people know the facts without scare tactics or hyperbole, they can make the best decisions for themselves when armed with objective, unbiased information.

As protection platforms go, Microsoft’s products have inherent weaknesses. In its defense, Microsoft has never claimed to have reliable spreadsheet protection. In Office applications, a password is like the lock on your home’s front door; its primary purpose is to keep your friends out. If someone really wants to get in, they will get in.

Try this: open a new workbook, go to Sheet1 and protect it with the password “test” (without the quotes, lower case just as you see it here). Now unprotect Sheet1 but instead of using the password “test”, use the password “zzyw”.

Take comfort that Microsoft is like any other company, in that virtually any application is hackable. Here’s some background on Excel spreadsheet password protection:

When someone password protects a sheet in Excel, they generate a 16-bit 2-byte hash, a technical term for a number generated from a string of text by a function called the MD5 Message Digest Algorithm. An MD5 hash has fewer numeric characters than the actual password text, making it unlikely but not impossible to be replicated. Note that “replicated” is not the same as “duplicated”.

When unprotecting a protected sheet, the password value is compared to the MD5 hash. Excel allows for up to 255 password characters in its worksheet protection scheme. Since it is a case-sensitive scheme, there are over 90 acceptable characters, which translate into the multiple trillions of password text possibilities. Since the combination of possible passwords is much greater than the combination of possible MD5 hashes, some passwords can share the same MD5 hash value.

The MD5 hash is a standard mixing algorithm, executed as follows:

• Take the ASCII values of all characters.
  • Shift left the first character 1 bit.
  • Shift left the second 2 bits.
  • Continue for quantity of characters up to 15 bits, with the higher bits rotated.
  • XOR those values.
  • XOR the count of characters.
  • XOR the constant 0xCE4B.

As you may know, XOR is a logical term associated with a mathematical compound statement, an acronym for “exclusive or”. In this case, statement “A” is the password value you type in. Statement “B” is the generated MD5 hash. The XOR operation returns TRUE when only one of its combinations is TRUE. This translates to more than one password value possible in the context of a truth table:

A B XOR Result
T T FALSE
T F TRUE
F T TRUE
F F FALSE

By the way, if you wanted to reproduce the actual password, and not just a compatible one, it’s a virtual certainty that it literally could not be accomplished during your lifetime.

There are 94 standard characters (26 of A-Z; 26 of a-z; 10 of 0-9; and 32 special such as #,%,!, and so on). That means, for every character there are 94 possibilities.

To extrapolate using the example of an eight-character password, the number of characters to test is
94 x 94 x 94 x 94 x 94 x 94 x 94 x 94
which equals
6,090,000,000,000,000

At the hefty pace of 100,000 password attempts per second, it would take 1,932 years to recover the exact password. And that’s just with 8 characters; with the 255 max it can take millions of years.

What all this boils down to is, if you don’t want to expose your Excel spreadsheets to *any* possible password circumvention, don’t share them. However, the likelihood of someone guessing a compatible hash is very slim, though there are commercially-sold password cracking programs.

One thing is sure, you are in good company: the whole world is in the same boat with this Excel protection issue. As you understand the spreadsheet password protection scheme, you can make your own informed decisions about what and what not to risk putting in your workbook, and how or with whom you share access to your workbooks.

Blog kindly provided by Tom Urtis (Excel MVP). If you are interested seeing more blogs and tips by Tom follow him on Twitter (@tomurtis) or visit his website Atlas Consulting

Excel – Using the “X Y Chart Labeler”

If you have ever looked around for tutorials on charts or read through books on charting techniques, one thing that pops up regularly is the X Y Chart Labeler by Rob Bovey.

This is a great little add-in, and best of all it’s free. You can download it from http://www.appspro.com/Utilities/Utilities.htm

So what does it do, and why is it so great?

When you create a chart in versions up to 2010, you can add labels to your series with one of the following;

• Series name
• Category name
• Value

Label options 2010

In most cases this is ample, but what if you wanted to put something different? You could re-label everything in your data table but then it probably won’t make sense to anyone else. If you use scatter diagrams or bubble charts you can’t even do that as you need both x and y coordinates to place your markers and these act as labels. Identifying the individual scatter points or bubbles becomes quite difficult. You could add text boxes manually to everything but if the data point moves the text box doesn’t, so you have to move everything again by hand. All quite painful really.

So this is where the X Y Chart Labeler comes in.

On the Ribbon

Let’s take a simple example using a table showing sales by customer. What might be of interest is to show the name of the account manager against the customer sales. I could show sales for each customer or sales for each account manager but not both at the same time. Not easily, anyway.

Basic table showing customers and sales

You create your chart as normal, but then you create a set of custom labels, or as in this case the names of the account managers. You will need one label per data point/series in order for it to work, but there is functionality within the tool to show/hide individual labels.

List of account managers

Assuming you have installed and turned on the add-in, click on the X Y CHART LABELS tab and click on ADD LABELS.

Tab detail of the Chart Labeler

The add labels form

Pick the series you want to label from the drop down list, then select the cells that contain your new custom labels. Select where you want them to appear (top/bottom/centre) and click on OK.

Completed chart with custom labels

Your new labels will now appear on the chart. Job done!

If the position is not quite right, click on MOVE LABELS.

Move labels form

Set the number of points you want your labels to move with each click of the arrows, and using the arrows, relocate your labels. You can apply the move to all labels in one go, or select individual labels to apply the movement to.

Other functionality within this add-in;

Manual labeller: allows you to manually label individual data points using either already existing values in cells or by typing a value directly into the form.

Manual labels form

Delete labels: as the name suggests, you can delete labels for a whole series or all labels in the chart.

Delete labels form

Help: it comes with a searchable help file, although to be fair, you can’t really go wrong using this add-in.

Help file

If you have Excel 2013 (and I’m guessing that this will continue to apply to later versions too) you do have the option to attach custom labels, using cells from your worksheet.

Click on the chart to activate it and click on the plus symbol that you see top right hand corner of the chart area. Select CHART LABELS then click on MORE OPTIONS.  Near the top of the window that appears on the right hand side, you will see the option VALUE FROM CELLS. Tick the option and then select the cells containing your custom labels.

Custom label option in Excel 2013

Knowing that this functionality exists in 2013+, is there a need for the add-in? Probably yes! You still need the add-in to move, show/hide labels etc. more easily than doing it all manually, so from point of view it’s worth having. Perhaps later versions will include this sort of extra functionality, but until then use the add-in.

Now…some of you will be looking at this and thinking “wow, this is brilliant….but they’ll never let me download it at work”. We all have over zealous IT security police and if they don’t know what it is or can’t support it (even though it needs no support) they block it. The add-in runs off VBA code and Rob Bovey, the developer, has not locked the code down, so you, or any despotic IT jobs worth can look at the code quite easily and read all the helpful notes and comments that are in the modules and forms.

Forms & modules in the add-in

Sample code & comments visible to all

The add-in is perfectly safe. Rob Bovey is a recognised MVP or Microsoft Most Valued Professional, so he’s not some random programmer who is dumping dodgy code on the internet. The add-in is also mentioned by the likes of Mr Excel, Jon Peltier and other leading lights in the Excel world as the go to tool for this job. So the only reason the IT bod will refuse to load it is because he/she knows nothing about VBA, not because the code is seen as a justifiable threat!

So if you need to add custom labels to a chart, get this add-in. Once you have it you’ll be able to apply it to all sorts of custom charts. It’s quick and easy to use.

 

Excel – Using the Data Form

If you find you are working a lot with long lists of data, you probably find after some time that distinguishing one line from another becomes increasingly difficult…a form of snow blindness if you like. You can format the list as a table and shows lines in alternating colours which might help but wouldn’t it be nice sometimes to be able to see only one line at a time.

Data list formatted as a table with banded rows

Well…you can, using a FORM. You can go to the length of creating a form in VBA, and adding all kinds of buttons and clever functionality, but there is actually a built in FORM creator which will build a form when required on any data as long as it is in a list format i.e. column headings only, no row headings.

In 2003 or earlier versions of Excel, the FORM was available from the standard menu. Since 2007, it has been relegated to just another button in the CUSTOMIZE THE QUICK ACCESS TOOLBAR (2007+) or CUSTOMIZE THE RIBBON (2010+) options.

You will find the FORM button under COMMANDS NOT IN THE RIBBON or ALL COMMANDS.

Finding the FORM button to add to the QAT or RIBBON

Add this to either the QAT or RIBBON and you are ready to go!

So now you have access to the FORM, what does it do and how does it work?

Click anywhere in your data table and click the FORM button.

The FORM button added to the QAT

 

 

 

 

 

And without any further intervention by you, or working your way through multiple steps of a wizard, Excel will create a user FORM for you.

Viewing a single record using the data form

Using the column headings, there is a separate line for each heading and a text box displaying the information held in that column for a single record.

In the top right hand corner you will see the total number of records in your list. Using the scroll bar between the data and the buttons you can move from one row to another using the arrows at the top and bottom of the scroll bar or click and drag the scroll bar itself to move quickly through the records.

Not only does the FORM allow you view records, but you can also edit the information directly in the FORM which will update the spreadsheet. There is extra functionality available using the buttons on the right hand side.

Buttons on the data form

New: To add new records to your list click here and start typing the information directly into the text box next to the heading. Sometimes, you will see some values/data in the form that does not appear in a white text box. This will be a calculated field or perhaps a look up function based on something you enter in another box. Because these cells in the worksheet are reliant on a formula you cannot enter information directly here.

A form showing both data entry boxes and un-editable formulas

One major downside to using the FORM is that if your data table has drop down lists to select values from, these are not transferred to the FORM, so you need to type values in manually.

One advantage of using a FORM to add new records, is that if your data is stored as a NAMED RANGE, any new records you add are automatically added to the NAMED RANGE so you don’t have to manually adjust the REFERS TO bit in the NAME MANAGER. If you are unsure about NAMED RANGES then refer to my blog http://wp.me/p2EAVc-99 to find out more. There is however, one caveat to this. A FORM will only add to an existing NAMED RANGE if it is called “database”. Call it anything else and the NAMED RANGE is fixed and it will not automatically expand to include new records.

Delete: Does exactly what you would imagine…it deletes the record from your table/list – permanently. Note that I said it deletes the record, not just the highlighted piece of information in the FORM.

Restore: If you make a change to anything in the FORM other than delete a record, this button becomes active and allows you restore the data back to its original value.

Criteria: I’ll jump to CRITERIA next because the FIND PREV and FIND NEXT buttons are generally used in conjunction with this button. When you click on CRITERIA it clears the FORM. This now becomes a search FORM. Type search criteria in one or more of the text boxes. You can enter whole values or you can use wildcards. Using FIND PREV and FIND NEXT then allows you to scroll through records that match your search criteria. When you click on CRITERIA you will see a CLEAR button that will clear the form of any criteria you have entered, and a FORM button that will return you the standard form.

So what wildcards can I use?

*(star/asterisk) denotes any number of characters.

H* will search for any value that begins with H followed by any number of characters.

*h will search for any value that ends in h.

? (question mark) denotes any single character

H?p will search for Hip, Hop, Hup, H3p etc.

You can then combine these together or use them in conjunction with < or >.

>h* searches for any names (for example) that begin with the letter h, I, j, k etc. and beyond.

<>h* will search for any values that don’t start with h.

Example showing multiple search criteria

So use any operators you are familiar with (<, >, <=, >=, <>) either on their own or with wildcards and you should be able to find just about anything in your list. Use the FIND PREV and FIND NEXT to scroll through the records that meet your criteria.

And finally Close which unsurprisingly closes the FORM.

So next time you are working with a list of data, give the FORM a go, it might be of use…might not.

Excel – Using Form Controls

From time to time you may come across tick boxes, option buttons or similar on a worksheet. These seem to be little objects that appear to do some clever things in formulas or to objects on your worksheet such as charts. These are not as difficult to set up and use as you might think.

First of all, you need access to FORM CONTROLS.

Make sure your DEVELOPER tab is turned on (2013 – right click on the RIBBON and select CUSTOMIZE THE RIBBON and tick the DEVELOPER box/2007 – click the OFFICE button, then select EXCEL OPTIONS and tick the third box down – SHOW DEVELOPER TAB IN RIBBON).

From the DEVELOPER tab click on INSERT. This will show you all the available controls.

 

Make sure you choose from the top half i.e. FORM CONTROLS and not from ACTIVEX CONTROLS or you’ll be expected to start writing VBA code to make them work! If you want to, right click over the icons and select ADD GALLERY TO QUICK ACCESS TOOLBAR. This will then give you a shortcut to the controls.

Shortcut added to the QAT

Now you have access to the controls, let’s start using them.

The controls at your disposal are;

• Buttons
• Combo boxes
• Tick/check boxes
• Spin buttons
• List boxes
• Option buttons
• Group boxes
• Scroll bars

There are a few others there but the label option is pointless as you can use INSERT – TEXT BOX, and there are a few greyed out buttons too, that no one seems to know why they exist on the toolbar if they can’t be used.

So how do you use these FORM CONTROLS?

Buttons:

Click on the BUTTON icon then click and drag on the worksheet to draw your new button. Don’t worry if it’s not the right size you can always adjust it afterwards.

As soon as you draw the BUTTON a new window will appear prompting you to assign a macro to the BUTTON.

Window to assign a macro to a button

Select a MACRO from the list and click on OK. If you don’t know what macros are, then you won’t need any buttons.

Any BUTTON you add will be given a default name…Button1, Button2 etc. Once you’ve added your macro, edit the caption on the button to let users know what it does.

Edited button caption

Click away from the button, and it is now active and ready to use. Just click the button to make it run the macro for you.

If you need to adjust the size of the button or change the caption hold down the CTRL button and click otherwise you’ll just run the macro. Make sure you release the CTRL button once you’ve clicked on it or you’ll create a duplicate if the mouse moves while the CTRL button is being held down.

Tick/Check Boxes:

Along with buttons, this is probably one of the more likely controls you are going to use. As with the BUTTON, click on the icon in the toolbar and draw it on your worksheet.

Basic check box

You can edit the caption and resize if necessary. However, a tick/check box is purely a visual object. By that I mean it does nothing by itself other than look pretty. In order to make it useful, you need to right click on it and select FORMAT CONTROL. All the usual formatting options exist and you can play with those to discover what they do. The important tab to look at is the CONTROL one.

Control tab for a tick/check box

Set the default value (read appearance) of the tick/check box. Mixed is an odd one…the box just appears filled in.

The Mixed option on a check box

The most important bit here though is the CELL LINK. Select a cell that will contain the value associated with the choice made with the TICK/CHECK box. Ticked/checked is equal to TRUE, and un-ticked/unchecked is equal to FALSE.

Ticked/un-ticked values

It is the value in the selected CELL LINK that you then use in your formulas. The TICK/CHECK box is simply an interactive image. Without the CELL LINK, the TICK/CHECK box does absolutely nothing of use other than display a tick or no tick.

Option Buttons:

Setting these up is exactly the same as the TICK/CHECK box. Draw it, right click, and assign a CELL LINK. As you add more OPTION buttons they are all linked to the same cell. Depending on the OPTION button you select a different number will appear in the cell.

Selecting Option butons

What if you want to create a set of separate OPTION buttons that are not linked to the first set?

This is where you have to use a GROUP BOX.

Draw a GROUP BOX, add some OPTION buttons inside it, select any one of the buttons, right click and assign a CELL LINK. All the other buttons within the group box will then link to the same cell. You will now have two independent sets of options to choose from.

Independent option groups

Combo Boxes:

Follow the same routine to set these up as above. The difference with these is that you need to have a set of source cells that contain information you want to see appear in the list. This is the INPUT RANGE on the CONTROL tab. Select a CELL LINK too. DROP DOWN LINES refers to the number of items to be displayed in the box. Beyond that scroll bars need to be used to see the rest of the items in the list.

Control tab for Combo boxes

When you use the COMBO BOX, the INPUT RANGE appears in the drop down, and when you make a selection from the list, the CELL LINK will display the position of the selected item as a number.

Input range and Cell Link for a Combo Box

Spin Buttons:

Usual set up…go to the CONTROL tab.

Set a current value, minimum and maximum values, as well as a value for incremental change i.e. each time someone uses the up or down button, how much does the value change by? Make sure you set a CELL LINK as with all the others.

Control tab for a Spin Buttons

Sequence of spin buttons

Obviously, you can set any min/max and increment you want…entirely down to what you want to display.

List Boxes:

Wheras the COMBO BOX gives you a drop down list, a LIST BOX is exactly what is says – a box that contains a list. So when you draw the box, make sure it is big enough to display at least part of your list. Scroll bars will appear if the list is longer than the box.

There are a few options in the CONTROL tab that we have not seen yet.

Control tab for a list box

INPUT RANGE and CELL LINK are identical to the other controls but you have options relating to SELECTION TYPE. In reality, only the SINGLE option is viable. Although you can select multiple items in the list, the CELL LINK can only store one value. EXTEND allows you to click and drag across multiple items to select them, but again, same problem applies to the CELL LINK. To make use of MULTI and EXTEND options you need to apply some VBA coding.

Scroll Bars:

Usual set up applies…SCROLL BARS can be vertical or horizontal. The settings for SCROLL BARS are identical to SPIN BUTTONS. Just go to the CONTROL tab and set everything there.

So setting all these controls is fairly simple. They all pretty much follow the same steps. The next step of course is using them in a useful way.

It is probably tempting once you know how to set these up in a worksheet to put as many FORM CONTROLS as is humanly possible into your work. However, be warned…the more controls you put in the more possible combinations you need to deal with.

Let’s take a simple example: you have two tick/check boxes and four option buttons in one group. Seems simple enough but what are all the possible choices that someone can make?

Combinations possible with 2 tick boxes and one option group with 4 option buttons

Looking at the table above, each TICK/CHECK box can be TRUE (T) or FALSE (F). We can choose only one option out of four. If an option button is selected then this is shown with a Y and N if not selected. What started as a good idea, quickly becomes a major problem, as you now have to write nested IF statements that can cope with any of the 16 possible outcomes. Imagine adding another option group or a combo box into the mix and how that might affect the total number of possible combinations! Unless you are very confident with creating long nested functions this is a non-starter. I’m not saying don’t do it….but think about what you need to do to manage it all in your spreadsheet. Here is a basic example of a nested IF statement to handle a single tick/check box and two option buttons…

=IF(AND(I1=2,J1=FALSE),SUM(A3:A18),IF(AND(I1=2,J1=TRUE),SUM(A3:A18)+SUM(B3:B18),COUNT(A3:A18)))

Gives you an idea of what you are facing, the more controls you add.

So FORM CONTROLS are a great way to add some sort of clever interaction in a spreadsheet. They can help to show/hide information, perform calculations based on criteria or can help you build dynamic charts. All I’ll say is don’t get carried away as the number of possible outcomes increases very quickly with each additional control that you add, but certainly a set of tools worth taking a look at.

Excel – Analysing Your Workbooks with the Inquire Add-In

In Excel 2013, amongst all the other amazing features that have been added since 2010 is a new add-in that can analyse your workbook for errors or inconsistencies.

The problem with this add-in, is that it has not been particularly well promoted and it is not in the standard add-in list that most of us go to turn on add-ins.

Firstly, note that this add-in is only available in the Professional Plus version of Excel. If you do have this version, then go to FILE, OPTIONS, ADD-INS. Go to the bottom of the screen and click on the drop down where you see EXCEL ADD-INS and click on COM ADD-INS and then click on GO.

The COM Add-In option

Tick the box next to INQUIRE to activate it and click on OK.

Turning on the Inquire add-in

You should now see a new tab on your Ribbon.

The Inquire tab on the ribbon

You will now access to a range of functions that can help you to examine your workbooks. If you don’t have this tool, you can still use some of the FORMULA AUDITING tools in the FORMULAS tab such as SHOW DEPENDENTS etc. but of course this is a more manual process and won’t give you the same level of detail that the new add-in can.

Workbook Analysis

Make sure that the workbook you want to analyse has been saved. This will not work on an unsaved workbook. Click on the WORKBOOK ANALYSIS button and let it do its thing. After a few moments (this will vary depending on how big and complex your workbook is) you will see this window;

 

Workbook analysis summary

 

You will see an overall summary of the workbook contents and if you click on each element in the ITEMS window, you will see the details in the RESULTS window on the right hand side.

OK, you may not necessarily be interested in how many cells contain formulas etc. but there is some useful information in here such as external references, cells containing errors etc. As you click on each element, you will see exactly which cells on which sheet meet those criteria.

Click on EXCEL EXPORT to create a copy of the report.

Workbook Relationship

As the name suggests, this will show you a diagrammatic view of how your workbook is connected to external data sources – other workbooks, databases etc.

Workbook connections

Use the buttons at the bottom of the window to print the diagram, preview the diagram, or simply to refresh it.

Option buttons

If your workbook relationships are particularly complex you can rearrange the various workbooks and databases by clicking and dragging the icons in the window into an easier to read layout.

Worksheet Relationship

Similar to above, but this time looking at how the worksheets are connected rather than at workbook level.

Worksheet connections

Same buttons at the bottom of the window and the layout can be changed too just as before.

Cell Relationship

When you run this you will get a few options to select from in terms of searching for precedents and/or dependent cells. Pick whichever options suit your requirements best and click on OK.

Options when selecting cell relationships

A simple cell relationship diagram

And it does exactly what is says…looks at precedents/dependents of any selected cell (rather than every single cell in your workbook). As I mentioned earlier, this can be achieved using the FORMULA AUDITING tools but this is a more convenient way of seeing everything. As with all the other relationship diagrams, same options are available.

Compare Files

In Excel you have had the option to look at workbooks side by side for some time but again this was a very manual process scrolling through your workbooks carrying out visual checks to see any differences between workbook versions.

This option gives you an automatic check.

Click on COMPARE FILES and use the drop downs to select the correct workbooks to compare. Note that both workbooks must be open in order to run this. Click on COMPARE to run the comparison showing differences

This will produce a full screen report. Where there are variances the cells will be colour coded depending on the type of variance.

Workbook comparison report/dashboard

You can review each worksheet independently. Check the lower half of the screen to see the colour coding and the details about the differences.

Details showing variances by cell reference

There is also a small chart to show you how many of each variance type it has detected. Not the most essential bit of information but looks pretty in the exported report as a sort of dashboard.

COMPARE FILES also compares VBA code between workbooks if it is present, so quite a useful tool.

Clean Excess Cell Formatting

You well find that a workbook is slow to open, or may take a while to refresh every time you make a change. This could be for a variety of reasons, but one of those may well be excessive cell formatting. When you set up a worksheet it may be convenient to apply conditional formatting, or just simple formatting to entire rows or columns. This can make your workbook quite bloated. CLEAN EXCESS CELL FORMATTING looks at empty cells beyond the last cell that contains data and removes any formatting that may be applied.

When you run this you get the option to run on the active sheet or the entire workbook. Click on OK once you have made your choice. You may be prompted to save changes but otherwise no other windows to interact with.

Set the scope of removing excess formatting

Workbook Passwords

This is used to store passwords for workbooks so that next time you open the workbook to run a comparison analysis you don’t have to enter the password. This probably has only limited appeal but is useful if you run any analysis or comparison when accessing the INQUIRE add-in via the START menu.

Click on START, go to the MICROSOFT OFFICE folder and open OFFICE 2013 TOOLS.

Getting to comparing workbooks via the start menu

From there click on SPREADSHEET COMPARE 2013. This will open a new window. Use the COMPARE FILES button to select the two workbooks you want to run the comparison on. If passwords have been stored in the PASSWORD MANAGER then you won’t have to enter any passwords to open or access the file(s).

Pick your workbooks to compare

Click on OK once you have chosen your files and continue as above to review your files.

So next time you need to run a quick analysis of your workbook, or need to compare two files, (and you have Excel 2013 Professional Plus) remember to turn on your INQUIRE add-in and give it a run…much quicker than doing it all manually.

 

 

 

 

 

 

 

 

 

Excel – Keyboard Shortcuts

Within Excel there are literally hundreds of keyboard shortcuts, from accessing all the buttons on the Ribbon, saving, editing or selecting data. Having said that, unless you have an amazing memory and are a touch typist, it is highly unlikely you will ever remember or even need to use all of these shortcuts.

Here are a handful of shortcuts that I have found useful over the years. This is by no means a definitive list of “must know” shortcuts as everyone has different needs, but I think these are a useful selection for most users of Excel.

The conventions I use here are as follows;

Plus Symbol +

If the plus symbol is shown between two or more keys then the keys have to be pressed at the same time. E.g. Alt + = means press Alt and = at the same time to insert the AUTOSUM function in a cell.

Greater Than Symbol >

If this symbol is used between keys then this represents a sequence of keys. E.g. Alt > D > P means press Alt first, release the key and then press D. Again, release the key and then press P which in this example opens the Pivot table wizard.

Forward Slash Symbol /

If this symbol is used between keys then this represents “or”. E.g. Ctrl + →/←/↑/↓ means press Ctrl and right/left/up or down arrows.

Ribbon Shortcuts

Everything on the Ribbon can be accessed via a keyboard shortcut. To view the shortcuts press Alt.

Press Alt to see keyboard keys required to access each tab in the Ribbon

A letter will appear for each tab. For example, if you want to access the FORMULAS tab you need to press M.

After pressing the letter that corresponds to the tab you want to get to, that will activate the tab and because you pressed Alt the letters you need for that tab are visible.

Showing all the keyboard keys to activate functions on the Formulas ribbon

Again, press the relevant key to activate the function in the Ribbon. For example, if you wanted to activate the CALCULATION OPTIONS, you would press Alt > M > X and in this case this activates a further drop down set of options. Press the key you need when this is displayed – here we have the options of A, E or M.

Additional keyboard options to set calculation method on a worksheet

I won’t go through every possible set of key combinations as each person will have their own favourite actions or buttons that they need to access on a frequent basis, but I will try to split the shortcuts into categories where possible…

Formatting Cells

Alt > H > AC	Align text centre in a cell
Alt > H > AL	Align text left in a cell
Alt > H > AR	Align text right in a cell
Alt > H > AT	Align text top of a cell
Alt > H > AM	Align text middle of a cell
Alt > H > AB	Align text bottom of a cell

At least these are fairly obvious in terms of which letters represent a position within a cell.

Ctrl + 1 (one)	Open FORMAT cells dialog window
Ctrl + B	Make cell contents BOLD
Ctrl + I	Make cell contents ITALIC
Ctrl + U	UNDERLINE contents of a cell

Simple ones but very useful, very often.

Workbook/Worksheet Navigation

Selecting cells quickly and moving around a worksheet at speed are great time savers and well worth knowing.

Ctrl + Home	Returns you to cell A1 from anywhere in a sheet
Ctrl + End	Goes to last cell containing test/data in your worksheet
Ctrl + Page Down	Move to next worksheet to the right
Ctrl + Page Up	Move to next worksheet to the left

The next set of shortcuts allow you to move quickly to the end of a contiguous set of cells containing data. The cursor will stop as soon as it hits an empty row or column.

Ctrl + →	Go to last cell to the right (with data) in the row
Ctrl + ←	Go to last cell to the left (with data) in the row
Ctrl + ↓	Go to last cell (with data) in the column
Ctrl + ↑	Go to first cell (with data) in the column

If you hit a blank row or column and there is data beyond it, simply repeat the key combination until you reach the end of the data table. If you go too far in any direction (i.e. to the far right or bottom of the sheet), just use the opposite arrow with the Ctrl button.

To select cells between your current location and the end of a row/column or the entire table combine Ctrl with Shift and the arrows.

Ctrl + Shift + →	Select all cells up to last cell to the right (with data) in the row
Ctrl + Shift + ←	Select all cells up to last cell to the left (with data) in the row
Ctrl + Shift + ↓	Select all cells up to last cell (with data) in the column
Ctrl + Shift + ↑	Select all cells up to first cell (with data) in the column
Ctrl + Shift + End	Select all cells to the last cell in the data sheet (with data)

Using Ctrl +

Using Ctrl + Shift + Arrow Right

Using Ctrl + Shift + End

If you select too many rows or columns, leave the cells selected and press Shift + ←/→/↑/↓ to deselect one row/column at a time. The same can be used on a single highlighted row or column of cells.

Ctrl + G	Opens the Go To dialog box
Alt + S	Opens the Go To Special dialog box from the Go To window
Ctrl + F	Opens the FIND dialog box
Ctrl + H	Opens the REPLACE dialog box

Note you can only use Alt + S after opening the Go To dialog box.

Go To is useful to get to cells or named ranges quickly. The Go To Special dialog box gives you the ability to navigate to or highlight certain types of cells in your worksheets such as any cells with conditional formatting applied to them.

The GoTo Special dialog box

Selecting/Inserting/Deleting/Hiding Rows/Columns

One thing you find yourself doing often is deleting or adding new rows and columns or wanting to hide some. So you end up right clicking and choosing from the options there or start searching for a button that can do it for you. I have found these to be quite useful little time savers;

Shift + Spacebar	Selects the entire row of the current selected cell
Ctrl + Spacebar	Selects the entire column of the current selected cell
Ctrl + +	Inserts cell(s) or a new row/column
Ctrl + –	Deletes cell(s) or a row/column
Ctrl + 9	Hides the row(s) of the selected cell(s)
Ctrl + 0 (zero)	Hides the column(s) of the selected cell(s)
Ctrl + Shift + 9	Un-hides the row(s) of the selected cell(s)
Ctrl + Shift + 0 (zero)	Un-hides the column(s) of the selected cell(s)

A few notes about this last set of shortcuts:

If you use Ctrl + +/- without selecting an entire row or column first then you get a dialog box asking you whether you want to insert or delete the selected cell(s) or the entire row or column.

Insert/Delete cell(s)/row(s)/column(s) dialog boxes

Also note that if you are using a laptop without a full numerical keypad, you need to use Ctrl + Shift + +/-. If these dialog boxes appear then press the letter key that matches your choice e.g. R to delete or insert an entire row.

So if you do want to use these keyboard shortcuts you are likely to do the following;

Ctrl + Spacebar > Ctrl + + which will insert a new column to the left of the selected column.

Using Ctrl + 9/0 will hide the row or column of the selected cell and if you IMMEDIATELY press Ctrl + Shift + 9/0 it will unhide whatever you just hid. However, it is more likely that you will want to unhide something at a later stage, so you will need to select the rows/columns either side of the hidden ones before you use this shortcut.

The final section is just a selection of other keyboard shortcuts I use, including some very basic ones that you can use in any application;

Ctrl + S	Saves the current document/workbook
F12	Opens the SaveAs dialog box
Ctrl + N	Creates a new document/workbook
Ctrl + C	Copies the selected cell(s) or object
Ctrl + V	Pastes the copied cells(s) or object to another location
Ctrl + X	Cuts the selected cell(s) to the clipboard
Ctrl + Z	Undo the last action (repeat for multiple undo actions)
Ctrl + Y	Redo the last action (repeat for multiple undo actions)
Ctrl + Shift + L	Applies AUTOFILTER to the selected cells/row
Ctrl + F1	Minimise/Maximise the Ribbon
F11	Creates a chat in a new chart sheet (uses default chart type)
Alt + F1	Creates an embedded chart in the same sheet (uses default chart type)
Shift + F2	Insert cell comment
F3	Opens the PASTE NAME dialog box to show a list of any named ranges you have in the current workbook
Ctrl + Shift + F3	Opens CREATE NAMES FROM SELECTION dialog box to create named ranges using existing column/row headings
F7	Runs spell check
Alt + F11	Opens the VISUAL BASIC EDITOR
Ctrl + `	Toggles between showing/hiding formulas in the worksheet (Note this is the combination on a UK QWERTY keyboard)
Shift + F11	Insert new worksheet to left of current worksheet
Alt + =	Insert the AUTOSUM function
F4	Toggle between relative and absolute cell references or repeat last action (limited use). See below for more information on the use of F4

After entering a cell reference in a formula press F4 to add in the “dollar” characters. Continue pressing F4 to get the required level of absolute/partial referencing.

Using the F4 button

And there you have 56 keyboard shortcuts that hopefully you will find a use for. As I said at the beginning this is not a definitive list. You still have about another 200 to learn beyond this list, but personally, these are shortcuts that I use regularly and cover what I think are the day to day actions that you will use in just about every spreadsheet you open.

Interactive Excel Voting Board

If you run a training session, or perhaps you chair a meeting, you want to get some instant feedback, or perhaps you want attendees to vote on something. You can do a simple show of hands which works fine, but how about something a little more high tech but without going overboard?

Ideally, for this to work, and in particular to get people involved you will need a touch screen. You could just use a mouse on the computer but this seems to work quite well and gets people out of their seats participating in the session.

There are two ways I can do this:

• The non VBA way
• Using VBA

Let’s start with the non VBA way using FORM CONTROLS.

If you are using 2003 then you will need to activate your FORMS toolbar. Go to VIEW, TOOLBARS and select FORMS. Do not use CONTROL TOOLBOX which looks very similar as this requires the use of VBA to make things work.

If you are using 2007 or later, turn on the DEVELOPER tab and you will find FORM CONTROLS under INSERT in the CONTROLS group.

Select the SPIN BUTTON option. Your cursor will change to a small cross-hair. Click and drag somewhere in your worksheet to “draw” the SPIN BUTTON. You can move and resize this at any time if it’s not quite right so don’t worry if it does not look quite right yet.

Right click on the new SPIN BUTTON and select FORMAT CONTROL.

In the dialog box decide what your minimum and maximum are and also the increment that each click of a button will increase/decrease the value by. So in this example I will set the minimum to 0, the maximum to 10 and the increment value to 1.

The final thing you need to set is a reference cell or CELL LINK. The form controls do nothing other than look pretty. In order for them to work, you have to link them to a cell and it is this cell that will do the work.

Click on OK.

Now create a chart based on the value in the CELL LINK.

Format your chart whichever way you want and repeat as many times as necessary to create multiple charts to display people’s votes.

Now for the VBA version. If you are familiar with VBA then this is a very simple way of doing things, and if you aren’t familiar then this is simple enough to have a go.

The benefit of VBA over the FORM CONTROLS is you have more options available to you and you can, if you want to, go mad and program it to do all kinds of weird and wonderful stuff…but for now, here is a simple example for you which you can embellish as your VBA skills grow.

This time start by entering the value 0 in a cell.

Now add a button to the worksheet, but this time use the ACTIVE X CONTROLS, NOT the form controls we used in the previous example.

Click on the BUTTON icon then click and drag to “draw” a button on the worksheet. Again this can be moved at a later stage if it’s not in the correct position.

Click on the PROPERTIES button and do the following:

• Give a suitable name to your button (helps when writing code)
• Enter a caption i.e. what you want to display on the button face

There are many more options you can go for but this would be the bare minimum at this stage.

Close the PROPERTIES window for now.

Now we need to add some code to our button to make it work. Double click the newly added button, making sure that you are in DESIGN mode in order to be able to make changes to your button.

 

I declare a PRIVATE variable in my GENERAL DECLARATIONS area at the top of my code sheet. I need this to store the value of the cell that I am associating with the button and subsequent chart.

I can then add the following bit of code:

 

Again, repeat as many times as necessary to build up your voting board.

So far though, we only have a button that increases the value of the cell. We now need to add one that takes the value down by one each time we click on it. At this point you are probably thinking that this is a bit long winded, considering we had a single button that did both before. Using this method means you can have a more customised look and feel to your voting board rather than just up/down arrows (the choice is yours!)

Follow the same steps as before to add a new button and then add the following code:

 

This time as well as decreasing the value of cell C6, I have added an IF statement to prevent negative values from being generated.

The final button you might want to add is reset button so you can quickly reset the value(s) to 0. This might be worth doing last of all once you have created all the voting buttons and charts you need and then have a single button to reset all the reference cells in one hit

.

 

Simply add extra cell references once you have set them all up e.g. Range(“C6, F6, I6”).Value = 0

Your final interactive voting tool will look something like this…

 

So next time you want to have a poll in class or at a meeting you can do it with a little more style than just a show of hands.