Blog Archives

Excel – Applying Conditional Formatting to Charts

Hopefully you are familiar with applying conditional formatting to cells, and creating rules to apply more complex formatting rules. If not, check out my blog to see how you can apply conditional formats to cells.

However, when it comes to charts there is no in-built conditional formatting functionality where you can create some sort of rule and   apply it to a series.

In order to create the “illusion” of conditional formatting on charts we have to create some dummy data based on our base data.

Let’s start with a simple table.

Simple sales table

When converted into a chart it gives us this.

Standard chart

As you can see we have a range of products being sold and have different sales figures against each one. To make things stand out a bit more it would be nice to apply some colour coding to the bars of the chart to show which sales are good, bad or indifferent or simply to show on a colour scale how sales are performing.

For this example, let’s say I just want to show a RAG (Red/Amber/Green) system for each product.

First, decide what your break points are going to be i.e. what determines if something should be red, amber or green;

Red <=1500

Amber <= 3500

Green > 3500

Once we have our break points we can set up our table to create the dummy data.

Add a couple of new lines above your table and if you want, label them something like Lower and Upper to represent the lower and upper bounds of your range of values e.g. 0 to 1500 for red.

It’s worth also adding some labels so that these appear in your legend when you create the chart so people understand the meaning of the colours. In this example, I used the formula = C2&” – “&C3 to build up the label for the red values.

Modified table to create dummy data

Now we build up some IF statements to determine which category the actual value from the base data falls into i.e. Product A should be in the amber section. We will start by creating a nested IF statement in cell C5 (in this example) that will test if the sales value for Product A is between 0 and 1500. If it is, then display the number, otherwise show #NA. We need to have #NA as this prevents anything from being plotted in the chart. By fixing certain elements of the formula (see my blog on absolute vs. relative referencing if you are unsure about what I have done) so that the formula can be copied across the columns and then down the rows ensuring it works in all cases.

=IF($B5>=C$2,IF($B5<=C$3,$B5,NA()),NA())

Once applied to all the cells in the modified table, each sales value for each product should only appear once in the new columns.

Completed dummy data table

Now it’s time to replot the chart but this time using the dummy data.

Highlight all the data apart from the original values.

Chart data selected

Use a stacked column chart to display your data and then apply whatever formatting you want to get your end chart.

Conditionally Formatted Chart

Because of the way the data has been set up, if any of the values change, the formatting you have applied will automatically change based on the values.

Updated numbers

 

Advertisements

Excel – Create a Custom Matrix Background

If you have ever been on any sort of management course, you will have been shown a variety of matrixes to represent data, strategy etc. These can be quite visual and can help to identify products or people who fit into different areas allowing you to target specific areas rather than try and tackle everything. Most of the time, you will see these in PowerPoint or Word, and have been manually created and are not particularly accurate and are not dynamic in any way.

So how can we create a useful matrix on which we can plot data?

First of all we have to decide on the type of chart that will best display our raw data. In the case of matrix displays a scatter chart is best, plotting results based on two set of values, effectively giving us x and y co-ordinates.

Using recent enquiries I have had, some examples of this might be:

  • strategic importance of work compared to the revenue it will generate
  • knowledge transfer from long serving staff due to retire – level of critical knowledge vs. the amount of time before retirement

In both of these examples, the business wants to identify work or people that might have a critical impact on the business so that they can be prioritised ahead of less critical work or where very specific and unique knowledge sits with one person, and ensure that an individual’s knowledge is recorded or transferred before they leave.

Let’s take the critical knowledge transfer and create a chart to track that.

One piece of information I will need is the amount of time between today and the day the person is scheduled to retire, and I might break that down into three segments;

  • less than 3 months
  • 3-6 months
  • 6 months to one year

The other piece of information I will need is the level of knowledge an individual might have and assign scores or levels (these are likely to be subjective);

  • Low
  • Medium
  • High

So we may find ourselves with data like this;

Basic starting data

Basic starting data

Because we can’t plot “words” in a scatter chart I need to change the Low-Medium-High ratings to numerical values. Depending on how you measure this you can use a simple 1-2-3 or have something with a few more breakpoints – choice is yours.

So now our new table might look like this (I’ve used a basic data validation list for the rating);

Data with added scores/rating values

Data with added scores/rating values

We can now create a simple scatter chart using the due retirement and rating columns.

Standard scatter chart

Standard scatter chart

The data has plotted OK but nothing jumps out at us to start dealing with those who will be shortly retiring and have critical knowledge level of high. What we need is some sort of background with clear segments to help identify target employees.

We could create or find an image that contains the number of segments we need but the proportions may not be that accurate, so the image will have to be stretched or simply won’t fit in with our data. So we are going to create the background manually using dummy data.

As we have three levels of criticality and 3 time periods we will need a 3 x 3 grid pattern.

Away from your real data, type the following values;

Dummy data to create bandings

Dummy data to create bandings

Using these values create a stacked column chart…

Base chart

Base chart

…and get what you see above. At this point we need to do a few minor adjustments;

  • Fix the maximum value of the Y-axis to 3
  • Set the gap between series to no gap or 0%
Change axis maximum value

Change axis maximum value

Change settings on the chart - gap width

Change settings on the chart – gap width

Our chart will now look like this;

ScreenHunter_214 Aug. 09 11.23

Before we start applying different colours to the various segments that make up the chart, we have to move the axes out of the way. The axes need to be moved because we need to copy the “real” data onto the chart, and in order for the data points to look as they did in the first basic chart (with no background) we need to use secondary x and y axes. If this is not done, the real data looks back to front and upside down as you need to read the values off the secondary axes rather than off the normal x and y axes. It’s not a major issue but best to sort out at this stage.

Right click on either axis. If you select the x axis first then select the axis option VERTICAL AXIS CROSSES AT MAXIMUM CATEGORY.

Right click on the y axis and from the options select HORIZONTAL AXIS CROSSES AT MAXIMUM AXIS VALUE.

The chart should now look like this;

ScreenHunter_214 Aug. 09 11.24

Apply formatting of some sort to your segments. Use any of the fill options available to you at this stage – solid colours, gradients, images or patterns…more options than you can shake a stick at.

The path is now clear to add the real data to the chart.

Highlight the due retirement and rating columns as we did in the original plain scatter chart and copy them.

Depending on how much data you have, you can create a separate column using a formula to only identify people who have a leave date within the next year…no need to plot people who are not going for another 10 years!

Click on the chart that is being used to represent the 3 x 3 grid of our matrix. On the HOME tab click on the arrow below PASTE and select PASTE SPECIAL. Instead of the normal options you see if you do this (paste values, paste link etc.) you will see this dialog box;

Paste special window for chart data

Paste special window for chart data

Select all the options as in the picture above and click on OK. The chart that appears looks very odd as it is trying to plot your copied data as stacked columns – the same chart type as the base chart with our grid.

Our chart with the added "real" data

Our chart with the added “real” data

If you’re using 2010 right click on the new series you just added and select CHANGE SERIES CHART TYPE, and select SCATTER WITH ONLY MARKERS type chart. If you’re using 2013 or later then it will open the COMBO chart dialog window. Select the correct series and select SCATTER.

Set the correct combination charts

Set the correct combination charts

ScreenHunter_214 Aug. 09 11.28

New series changed over to the correct scatter type plot

Now, it’s purely down to formatting. Depending on what you are presenting and how you want to highlight data points will be entirely up to you. In my version here, you will see I have added shading to differentiate between the high/mid/low ratings and some extra labels to identify the criticality levels, as well as some arrows to highlight the timescale – the latter having been added manually.

Completed chart with custom gradients and additional labels/arrows

Completed chart with custom gradients and additional labels/arrows

The only other thing I have added are some custom labels. As we are working with a scatter chart we can’t attach any useful labels to the data points. I have added a new column with a formula to pick out the names of people who have a criticality level of high and their leave date is within the next three months. I then used Rob Bovey’s xy chart labeller which is a free add-in that is perfect for this sort of job. If you are not familiar with this add-in, read my blog http://wp.me/p2EAVc-et which explains how it works and where you can get it from – an absolute must for these sorts of jobs.

So that’s one way of creating a custom background to make a matrix type display using Excel. Once you have your basic set up for the background, i.e. number of segments you want to measure against, it’s down to your raw data. Depending on your own Excel skills, use data validation, dynamic ranges, formulas etc. and whether you are working with static or continuously evolving data, how much interaction and dynamism you want to build into your matrix. With a little thought and preparation you can construct a pretty useful matrix type chart that will highlight data points, be they people or activities that need attention.

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

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

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

…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 – Using INDEX & MATCH (2)

You’ve probably come across the MIN and MAX functions which give you the lowest and highest values, respectively, in a range of cells. This is fine as long as all you need to know is the lowest or highest figure, but what if you want to know who or what got the lowest/highest figure?

Let’s take a simple example;

IM basic list

Basic table data

So we have a list of people and the amounts they have spent. With a quick scan of the data you can see that Mrs Delores Turner had the lowest spend, and Mr Louie Moreno had the highest. This is easy because there is only a very small amount of data, but if there were hundreds or even thousands of lines it would not be so obvious.

By using the MIN and MAX functions I can quickly find the lowest and highest values using MIN(B2:B11) or MAX(B2:B11), but it doesn’t tell me “who” spent these values.

So how can you get around this?

This is where INDEX and MATCH come in. If you are unfamiliar with these two functions, check my blog https://excelmate.wordpress.com/2014/12/04/excel-using-index-match-part-1 which will show you how they work. For the rest of this blog I will assume you have either read the blog or are already familiar with INDEX and MATCH.

To get the name of the person with the lowest/highest spend I need the following formula;

=INDEX(A2:A11,MATCH(MIN(B2:B11),B2:B11,0)

im_MIN SOLVED

Formula to find the person with lowest spend

The MATCH part of the formula find finds the lowest value in cells B2:B11. It then uses this number and finds its position within the range. In this example the MATCH function will return the value 4 i.e. it is the 4th value in the selected column of data. The index function then looks within range A2:A11 and looks in the 4th row – the value returned by MATCH. You don’t need to reference the column because there is only one column selected in the array portion of the INDEX function.

The find the person with the highest spend, the formula would be;

=INDEX(A2:A11,MATCH(MAX(B2:B11),B2:B11,0)

IM_MAX SOLVED

Formula to find person with highest spend

So as you can see INDEX and MATCH, although pretty useless on their own as we saw in the earlier blog, when combined they are a useful duet of functions to know and should be part of your “must know” functions in Excel.

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

shutterstock_129753287

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 – MIN & MAX vs. SMALL & LARGE Functions

The MIN and MAX functions are two of Excel’s basic functions to find the single lowest or highest value in a range of cells and they appear in the drop down list on the AUTOSUM button.

Drop down next to AUTOSUM

Many beginners often ask in training sessions when they would be likely to use these functions, but once you know them, it’s surprising how much use you can actually get out of them.

The other two functions, SMALL and LARGE also return lowest and highest values respectively but with the added option to find the 2nd , 3rd, 4th, 5th lowest/highest ….etc.

The basic syntax for MIN and MAX is;

=MIN(range to test)

=MAX(range to test)

If we look at a basic table with a range of values…

Basic table to use MIN, MAX etc.

You might be able identify the lowest or highest values as this is only a small number of cells, but try doing that with 1,000s or more – it would be very easy to get the wrong answer.

In this example, to find the lowest single value our formula would be;

=MIN(A2:C15) giving us the answer 45.

To get the highest;

=MAX(A2:C15) giving us the answer 975.

Simple, quick…enough said.

Now let’s take a look at SMALL and LARGE.

The basic syntax for these is;

=SMALL(range to test, nth value)

=LARGE(range to test, nth value)

Returning to our data table above, we could extract the following lowest values;

=SMALL(A2:C15,1)
giving us the answer 45…exactly the same as MIN i.e. lowest value

=SMALL(A2:C15,3) giving us the answer 124 (3rd lowest value)

=SMALL(A2:C15,12) giving us the answer 239 (12th lowest value)

And to get the largest values;

=LARGE(A2:C15,1)
giving us the answer 975…exactly the same as MAX i.e. highest value.

=LARGE(A2:C15,5) giving us the answer 863 (5th highest value)

=LARGE(A2:C15,9) giving us the answer 787 (9th highest value)

So there you have finding lowest and highest values. As with all Excel functions, combining these with other functions will give you a lot more power to analyse and find values in your spreadsheets. Look out also for my blogs on DATE and TEXT functions.

 

Excel VBA – Looping Part 2

Looping2

In Looping Part 1 ( http://wp.me/p2EAVc-aW ) I showed you how to write LOOPS using DO UNTIL/WHILE…LOOP. They are relatively simple to put together and easy to test and debug when things go wrong.

In this blog I am going to show you the FOR…NEXT loop.

This type of loop lets you control the number of loops more easily, as well as working out by itself when to stop rather than setting an end point in the DO type loops. They tend to be more efficient and therefore quicker. The downside to them for novices is that the cursor does not physically move from one cell to another, or from one sheet to another as all the movement is done in the background. This means you can’t run the DEBUG tool as normal and watch what it does to see where it goes wrong and you have to rely on the IMMEDIATE window to question the VB EDITOR to find out what is going on.

Creating a loop with a set number of loops;

Let’s say I want to generate random numbers for my weekly lottery ticket for which I only need 6 numbers. I need a loop that only goes round 6 times and shows me a number each time.

First we need a suitable variable to count the loops.

Dim x as Byte

Now for the loop;

For x = 1 to 6 ‘(stop after 6 loops)

Next x (automatically adds 1 to the variable at the end of each loop)

In terms of coding this is very simple. All I need to do now is add my code to generate random numbers and display them in a message box;

For x = 1 to 6

MsgBox “Lucky number ” & WorksheetFunction.Randbetween(1,49),vbInformation,           “Lottery Numbers”

Next x

As numbers are generated randomly we would need to build into the code something to handle duplicate values but to keep it simple here I’m just generating 6 random numbers without any further checks.

Note that normally the message box code would all be on one line or perhaps on a couple of lines with an underscore (line continuation marker) at the end of line 1.

If you are unsure about how message boxes work refer to my blog Excel VBA Message Boxes ( http://wp.me/p2EAVc-3 )for more information.

So that is one simple application of the FOR…NEXT type loop. How about scrolling through a bunch of cells?

Looping through a range of cells;

Let’s say I have some data and I want to apply a bit of formatting. I could create a DO UNTIL type loop moving from row to row and then column to column, but a FOR…NEXT loop will be far more efficient.

I want to highlight all cells with a value under 100 by making the font red. I need code that will select the entire table, and then check each cell to see if each cell is under 100 and change values to a red font.

Dim c as Range   ‘create an object variable to represent each cell in the selected range

Range(“A1”).CurrentRegion.Select  ‘(selects all contiguous cells around A1)

For Each c in Selection

          If c.Value <100 then ‘(tests if the value in each cell is less than 100)

                   c.Font.Color = vbRed

          End If

Next c ‘(loops through all cells in the selected range until it runs out)

So this is much easier than trying to work out when to go back to the top of a column and then move across one and so on and so forth.

Below is a video showing a For Each…Next loop in action showing how to use the IMMEDIATE WINDOW to find out what the code is doing and where.

Looping through multiple sheets;

I can apply the same logic to any number of sheets in a workbook.

Dim Ws as Worksheet

For Each Ws in ActiveWorkbook.WorkSheets

If WorksheetFunction.Counta(Ws.Cells) = 0 Then
‘(test if all cells are blank/empty)

                   Ws.Delete

          End If

Next Ws

So how does this work?

First we need an object variable (Ws) to represent any worksheet in the workbook. This is needed because we don’t necessarily know how many worksheets are in the current workbook or what they are called, so we need a way of referencing each sheet without being specific (i.e. hard coding names/numbers). And this is one of the issues you will have if anything goes wrong. The worksheet you are in is the active sheet, but you never move away from it. All the movement from sheet to sheet happens in the background, so in STEP INTO DEBUG mode you can’t see which sheet is being tested at any point while the macro is running. You have to rely on the IMMEDIATE WINDOW. To open the IMMEDIATE WINDOW select it from the VIEW menu or press CTRL + G.

The IMMEDIATE WINDOW lets you question the VB EDITOR. The only downside to this is you have to ask the questions in VBA! So, to find out which sheet is currently being checked the code above you would need to ask;

?Ws.Name

Remember you cannot ask about the ActiveSheet as it is always the same one – whichever sheet you started in.

Looping through multiple workbooks;

Having seen how to loop through multiple worksheets, this logic can be extended to do the same with multiple workbooks.

Dim Ws as Worksheet, Wb as Workbook

For Each Wb in ActiveWorkbooks

          For Each Ws in Wb.Worksheets

                   If WorksheetFunction.Counta(Ws.Cells) = 0 Then

                             Ws.Delete

                   End If

          Next Ws

Next Wb

Now we have one object variable for the worksheets and one for the workbooks. The code will look for any open workbooks, and for each one it finds it will scroll through all the worksheets to see if they are blank or not and then move on to the next open workbook until it runs out of workbooks to check. By using object variables there is no need to hard code names etc. relating to the open workbooks or worksheets. Remember, if you need to check which workbook or worksheet is being checked by the code you will need to do this in the IMMEDIATE WINDOW.

One word of warning: if PERSONAL.XLSB is open it will treat it as an open workbook and test its sheets too. Problem is PERSONAL.XLSB only has one sheet and it is blank as this workbook is never used other than to write code to. If the code tries to delete its one and only worksheet it will crash as workbooks cannot exist without any worksheets. To get around this you need an IF statement before starting the loops to test the name of the current workbook.

So there you have the basics of loops in VBA:

  • DO UNTIL/WHILE…LOOP
  • FOR…NEXT

Both have their uses. DO loops tend to be easier to create and test, but FOR…NEXT loops are more efficient but tend to require object variables to get the most out of them and are a little trickier to test using the IMMEDIATE WINDOW.

It’s now down to you to put these into practice and apply them to whatever project you are working on.

Excel VBA – Looping Part 1

shutterstock_173122277

One thing you cannot record as part of an Excel macro is looping i.e. moving from one row/column, worksheet or even workbook to another. You can record copying a formula down a column or similar, but it records it as AUTOFILL and therefore records the cells as absolute references meaning that if you have any variation to the number of rows etc. when you recorded the macro, it won’t work properly. So this is where you need to do LOOPS in VBA.

There are two basic loops you can use;

  • Do While/Until…..Loop
  • For each…..next

In this first blog I am going to go through the DO WHILE/UNTIL type loop.

Let’s take a simple example, where I want to add a formula in column C adding up values in columns A and B.

 

Simple table to add a formula column to

Simple table to add a formula column to

The code required to do this would be as follows;

First we need a start point i.e. where do we want to start the loop from? In this example, the first cell is C2.

Range(“C2”).Select

or

Cells(2,3).Select (use whichever method you prefer)

Then we need to give an instruction when to stop looping. Here, we want the code to stop running when the cell(s) to the left of the formula cell is/are empty. You would need to decide how to handle this depending on the data you have – are all columns full of data? Are there any blanks in either or both columns? This will determine what you need to put in your DO UNTIL/WHILE command.

Do Until ActiveCell.Offset(0,-1).Value = “” (stop when cell to left is blank)

Or

Do Until Activecell.Offset(0,-2).Value = “” And ActiveCell.Offset(0,-1).Value = “” (stop when both columns A and B are blank)

If you are not familiar with the OFFSET function in VBA, it allows you to reference cells based on their distance in terms of ROWS and then COLUMNS from the selected or active cell.

Positive numbers refer to ROWS down and COLUMNS to the right, whereas negative numbers refer to ROWS up, and COLUMNS to the left of the active cell. If you had cell C5 selected, for example, then;

ActiveCell.Offset(2,-1) would refer to cell B7 – two rows down & 1 column to the left.

The alternative to DO UNTIL is DO WHILE. Works the same way, just a question of logic to tell the code when to stop. If using DO WHILE in our original example the code would be like this;

Do While ActiveCell.Offset(0,-1)<> “” (keep going as long as the cell in the column to the left is not empty)

Personally, I tend to go with DO UNTIL…seems more logical to me, but the choice is yours.

Get into the habit of adding in the word LOOP to make sure you close off the pairing of code words. This will give us the following code so far;

Range(“C2”).Select

Do Until ActiveCell.Offset(0,-1).Value = “”

Loop

One thing you will always need in a LOOP, is movement. You need to add a line that tells the editor which direction to travel in as part of the loop. If you don’t do this, then you stay permanently in the start cell and therefore in a permanent loop. If that happens you can use CTRL + BREAK to stop the loop and show the END/DEBUG window. If it is the LOOP causing the problem the code word LOOP should be highlighted in yellow.

In this example, I need to move down a row each time so I will add the line;

ActiveCell.Offset(1,0).Select

This should be just above the LOOP line. If you go on to do more complex loops, think carefully where the movement comes in. I’ll show you a simple example later.

The final part of the code is to tell the code what to do with each loop. In this example it is to add a formula. Here, I will be using R1C1 cell referencing style. If you don’t know what that is, or how it works check out my blog about R1C1 reference style ( http://wp.me/p2EAVc-4I ). Good to know when it comes to VBA formulas!

My final code will therefore be;

Range(“C2”).Select

Do Until ActiveCell.Offset(0,-1).Value = “”

          ActiveCell.FormulaR1C1 = “=SUM(rc[-2]:rc[-1])”

          ActiveCell.Offset(1,0).Select

Loop

When you have a loop set up, it’s always worth a look step by step using the DEBUG tools to see the loop in action and to check that it’s doing everything it’s meant to be doing, especially if any variables are concerned.

I mentioned earlier about being careful where you put your movement line in your code. Let’s take a look at a bit of code that removes blank lines from a worksheet;

Range(“C2”).Select

Do Until ActiveCell. Value = “End”

          If ActiveCell.value = “” then

                   Rows(ActiveCell.Row).Delete

          Else

                   ActiveCell.Offset(1,0).Select

          End if

Loop

Here, the movement is built into the IF statement, rather than at the end just before the LOOP. This is necessary because when you delete a row, all rows underneath automatically move up to “fill the void”. If you move each time you delete a line, and there are two or more blank rows in succession, the next blank row would move up to your current location, and then the cursor would move down a row, completely missing out testing the row that had just moved up. The video below will show clearly what happens if the movement is just before the loop, and then with the movement in the IF statement.

So there you have some basic code to create a loop. Looping Part 2 will look at the FOR EACH…NEXT type loop which is a quicker and more efficient way of looping, letting you loop through multiple worksheets and workbooks in seconds, but do get used to the DO UNTIL/WHILE loop. It has its uses and is easier to debug and test than the FOR…NEXT type loops as you will see in Part 2 of looping.

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

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

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

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 +

Using Ctrl + Shift + Arrow Right

Using Ctrl + Shift + Arrow Right

Using Ctrl + Shift + End

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

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

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

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.

Excel – Protecting Your Worksheets and Workbooks

Having spent a lot of time creating the perfect workbook, setting it just how you need it ready to do other important work, you send it out to people around the office or business, or perhaps you place it on a shared drive for everyone to access, only to find that within days, or even hours, some bright spark doesn’t like your layout and changes it around to suit them regardless of the fact that it was set up in a particular way for a specific reason.

This is where we need PROTECTION.

To protect a workbook there are number of steps to follow:

  1. Unlock any cells that the user(s) need access to – this will apply to all visible sheets.
  2. Protect any sheets users will have access to, and hide any sheets users don’t need access to.
  3. Protect the workbook itself

A quick word first about the effectiveness of protecting workbooks. Basically, it’s not very secure. There is software readily available for free off the internet that will quickly hack into a workbook – I use one myself by Rob Bovey at www.appspro.com called Excel Utilities v.7 (check out my video on WordPress/YouTube – “Clearing Workbook & Worksheet Passwords” showing how well it works http://wp.me/p2EAVc-3u).

You’re probably thinking “so what’s the point of protecting my work if someone can hack into it so easily??”, but in the majority of cases, especially in the work environment, many companies don’t like or even allow the use of freebie add-ins off the internet and to be fair, most people couldn’t be bothered hacking into a workbook at work…..why create more work for yourself?

Step 1: Unlocking Cells

When you open the FORMAT CELLS dialog box you may have noticed a tab on the far end called PROTECTION.

The protection tab in Format Cells

The protection tab in Format Cells

You may have tried ticking and unticking the options and seen no difference whatsoever to your spreadsheet, but notice the comments below the tick boxes – “Locking cells or hiding formulas has no effect until you protect the worksheet”. However, this step is vital once protection has been applied to control what people have access to in the worksheet.

All cells, by default, are locked. Therefore, select ALL cells that you want people to be able to edit or fill in and untick the LOCKED option. Any cells you don’t want people to change or enter information into, leave as is – locked!

Using our timesheet example that we used to create VALIDATION rules, the cells highlighted in blue have been unlocked in preparation for PROTECTION.

ScreenHunter_112 Sep. 30 11.02

Once you are satisfied that all the correct cells have been unlocked go on to step 2…

Step 2: Protect Your Worksheets

Before protecting every sheet in the workbook, stop and think about which sheets people actually need to use and/or see. For example, you may have a sheet where all the lists that are used in your DATA VALIDATION lists are kept. Do people need to see those? Probably not. So rather than set up a password etc. to protect the sheet, hide it instead. Do the same for any other sheets that people don’t really need to see or access. Protecting the workbook in step 3 will prevent people gaining access to all your hidden worksheets.

Go to the REVIEW tab and click on PROTECT SHEET.

ScreenHunter_112 Sep. 30 11.09

In the following dialog box you have the option to create a password. Personally I don’t set a password until I have tested everything to make sure it works how I want it to. If you set a password while still testing, it just means you have to type in the password every time you want to make a change and reset it again afterwards. A minor pain….your choice.

You can also decide what freedoms you are going to give to your users. Do you want people to be able to add or delete rows or columns etc.? The only option you must leave ticked is SELECT UNLOCKED CELLS. If that is unticked, you can’t even select the cells you want to edit!

ScreenHunter_112 Sep. 30 11.11

Test your settings. Can you get to all the cells that need to be filled in or edited? Are all the cells you want to block access to safely locked? Lock or unlock cells as appropriate and re-test.

Once fully tested and working OK, set a password, confirm the password in the next dialog box and click on OK.

ScreenHunter_112 Sep. 30 11.12

Now that the worksheet(s) are protected you’d think that would be enough. A lot of options, including DATA VALIDATION have been made inactive and appear greyed out in the ribbon.

ScreenHunter_112 Sep. 30 13.14

However, if you right click on any of the worksheet tabs you can still rename, delete and move worksheets.

ScreenHunter_112 Sep. 30 13.17

To deny all access to this you now need to complete step 3 – protecting the workbook.

Step 3: Protect Your Workbook

Click on your REVIEW tab again, and click on PROTECT WORKBOOK.

ScreenHunter_112 Sep. 30 13.21

Protect the workbook, with or without a password, and test it. Try to delete or rename sheets, try to get to validation lists etc. Make sure you check EVERY sheet. Protecting a workbook does not give any cell protection to a sheet that has not been protected, leaving users free to edit any content on that sheet.

Once fully tested, apply passwords and you are ready to unleash your workbook on the world.

Unless you have Rob Bovey’s Excel Utilities and the break passwords function (or equivalent) DON’T FORGET YOUR PASSWORDS! Making copies of the workbook or other similar tricks won’t work – it will copy all the passwords across at the same time.

As an optional extra, you can also apply protection to individual cells or ranges of cells that require a password to gain access.

Let’s take out timesheet example again. We have unlocked all the cells that people need access to, but there is a column called “Approval Status”. This is where a manager has to approve/reject the line recorded in the timesheet. If I leave this unlocked, then the person filling in the timesheet would have access to these cells and self-approve their timesheet.

By leaving this set of cells locked, the general user cannot access them, but I need the cells to be accessible by the manager. For that, I can use ALLOW USERS TO EDIT RANGES.

Select the range of locked cells and on the REVIEW tab, click on ALLOW USERS TO EDIT RANGES. Click on the NEW button on the dialog box that appears. On the next dialog box give your range a TITLE (e.g. Approvers). If you have highlighted the cells, the REFERS TO CELLS box should show the cell range. If not, simply click on the range selector button and highlight the range you want. Finally set a password, confirm it and click on OK.

ScreenHunter_112 Sep. 30 15.18

Now if anyone tries to type into one of these cells they will be prompted for a password.

ScreenHunter_112 Sep. 30 15.41

So with a combination of validation rules and protection your workbook should be fairly safe from interference from the majority of people.