Learn VBA for Excel

Over 10 years of training VBA have helped me put together a book aimed at absolute beginners and novices in VBA. Having seen first hand what excel users, and for the most part non programmers struggle with in class when they’re learning coding I have made a point of highlighting the sorts of common errors people make so that hopefully you can avoid them.

The book entitled “VBA – Turning to the Dark Side of Excel” is available through Amazon in both Kindle and paperback versions. So if you want to learn from a trainer then this is for you. Over 20 chapters starting from the basics through to loops, forms and even working with other applications, there is plenty to get you automating your Excel workbooks.

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 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 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.

2014 in review

The WordPress.com stats helper monkeys prepared a 2014 annual report for this blog.

Here’s an excerpt:

The concert hall at the Sydney Opera House holds 2,700 people. This blog was viewed about 57,000 times in 2014. If it were a concert at Sydney Opera House, it would take about 21 sold-out performances for that many people to see it.

Click here to see the complete report.

Excel – Creating & Using Named Ranges

When you first come across named ranges they don’t seem to be the most useful of things in Excel, yet the more you learn in Excel the more uses and benefits you will discover in using them.

So what is a named range?

Basically, it is a name that has been given to a single cell or group of cells in a worksheet. But why call it by something that is longer than its address?

Let’s look at an example naming a single cell.

In A1 let’s enter the UK VAT rate (sales tax) of 20%. In some cells elsewhere in the worksheet I have some values and I want to calculate the VAT on each of those.

Formula showing use of absolute reference

You can see my values in column A, the calculated VAT in column B and for ease of understanding I have displayed the formula in column C. Basic stuff you might say…a simple calculation using $ symbols to fix the cell address of the 20% rate.

Now let’s give our 20% cell a sensible name that reflects what it contains e.g.  VAT_Rate. Note the use of an underscore in the name. When creating named ranges do not use spaces…it won’t work. Also be aware that calling a cell VAT20 for instance, which may seem like a sensible name, will actually take you to cell address VAT20. Don’t forget that if you are using 2007 or later, columns go all the way to XFD so add underscores or something to the name that will not turn it into a cell reference.

To give your cell a name, the easiest way is to click in the NAME BOX.

The Name Box

Where you see the cell address, start typing the name you want to give to your cell remembering the rules about names I mentioned earlier. Make sure you press ENTER otherwise the name won’t be saved.

If you want to check that the name works, click on the arrow on the right hand side of the NAME BOX, click on the name you have just created and all being well the cell you gave a name to will be highlighted.

Name added to the Name Box

“Fantastic!” I hear you say…”but you have just spent about 30 seconds creating and testing a name that is three times the length of the actual cell address and no obvious benefits…would have been quicker typing $A$1!”.

And so it would seem. But let’s rewrite our formula…

Start writing your formula exactly as before but instead of typing in $A$1, start typing the name you assigned to the cell. As we are entering a formula, when I type the first letter of the name it comes up with a list of functions, and rather conveniently, named ranges beginning with the same letter…

Named ranges appear in the functions/names list when you type a formula

Either arrow down to the name or type the name in full and press enter as you would for any normal formula once all the cell references etc. have been entered. And because I’m using a name, it has a fixed location so no need for those $ symbols.

Same formula as before but using the new named range

The end result in column B is no different to what we had before, but when you look at the formula, instead of some cell reference we can now see a name that gives us an indication of what is actually being calculated. So there is advantage number 1 of a NAMED RANGE. If you are used to building more complex formulas, then they will become much shorter and what they are calculating becomes much more obvious.

The second advantage is that I can now make reference to my cell anywhere in the workbook without the need to know where it is, or even ty and find it to click on it to build it into any of my formulas.

Using the named range elsewhere in the same workbook

Just type the name as part of your formula as before, select the name from the list that appears, and done! Simple, and if you look at the formula you know exactly what it is calculating – the name tells you.

If you forget a name that you have created, you can bring up a list of all named ranges in your workbook by pressing the F3 key as you build your formula. Click on the name you want to use and click on OK.

Use F3 to see a list of all named ranges in your workbook

So far I have only named a single cell, but you can create named ranges for entire blocks of cells, and you can even have overlapping named ranges.

Naming a block of cells

Here I have manually named a block of cells containing sales data for “North”. I could then repeat this for each column, however, I will show you a more efficient way of doing this a bit further on, but here it is just to show that blocks of cells can be named too.

I can now write formulas that refer to the block of named cells;

=SUM(SalesNorth)

…which will sum up the cells A2:A12. No need for me to physically select any cells, just use the name and once again when I look at my formula I know exactly what it is adding up.

Hopefully by now you can start seeing some of the advantages of using named ranges.

A bit of a random geek fact for you: if you zoom down to 39% or less, named ranges are actually displayed in the worksheet (works best with blocks of cells rather than single ones).

Zooming down to 39% (or less) to see named ranges in a worksheet

The quickest way to name a range is to simply type the name in the NAME BOX and press enter but you can also do it through the NAME MANAGER or DEFINE NAME in the FORMULAS tab.

Choosing the scope of a named range

Use the dialog box to name your range, decide on its scope i.e. is referencing the name limited to a single worksheet or can it be used across the entire workbook? You can add comments, personally I never bother but the choice is yours. And finally which cell(s) does your name refer to? Click on OK when done. The new name you have created will then appear in the drop down list in the NAME BOX.

If you go through the NAME MANAGER click on NEW in the top left hand corner to open the same dialog box as above.

The name manager

If you want to EDIT or DELETE your named ranges you need to come through the NAME MANAGER. Unfortunately, you cannot delete named ranges via the name box.

If you have dozens of named ranges, you can use the FILTER button to limit the list by filtering on different criteria.

Using the filter in the name manager

I mentioned earlier a quick way to generate range names. Previously I created named ranges by selecting the cells myself and using the NAME BOX to assign a name. That’s fine if you only have 2-3 to create but what if you have loads of columns or rows that you would like to refer to by name?

Using a basic two-way table (you can do this on any size of table by the way) highlight the whole table. On the FORMULAS tab, click on CREATE FROM SELECTION or if you like keyboard shortcuts use Ctrl + Shift + F3 and you will see this…

Using Create From Selection to create named ranges

Decide which row/column values you want to use as range names and tick/un-tick the relevant boxes in the dialog window. Click on OK. Nothing apparent happens until you check out the NAME BOX or the NAME MANAGER.

The full list of named ranges in the workbook

We now have a whole bunch of newly created named ranges using the row and column headings from the table!

Showing the automatically created named ranges

Test them out if you want by clicking on any of the names and see which cells are highlighted. You can now start writing formulas using as many names as necessary;

=SUM(Yr_2009,Yr_2011)

Now tell me that’s not better than =SUM(Sheet4!B2:B5,Sheet4!D2:D5)!

Finally, be aware that all the methods I have shown you here apply to FIXED dimension ranges i.e. the cell(s) you select and name remain unchanged unless you go into the NAME MANAGER and edit the REFERS TO information adding further rows/columns. You can create dynamic ranges but I will write about those separately as they are quite a big topic in their own right.

A named range CAN be automatically resized as long as new rows/columns are added WITHIN the boundaries of the original named range. If my named range is from A1 to D20, as long as I insert or delete rows between rows 1 and 20, or insert/delete columns between A and D, the range will include those automatically. If I add data from row 21 or column E and beyond I would need to manually change the REFERS TO references in the NAME MANAGER.

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.