Automate Excel

This is a simple tutorial on how to create and display a bar chart in a cell; a technique that works very well when creating management reports.

Steps:

1. In column A enter the values you want to display i.e. in cell A1 enter the value 10, in cell A2 20 etc.

2. In column B1 enter the following formula: =REPT (”n”, A1). This formula simply tells Excel to repeat the value stored in between “ “ by the number in cell A1.

3. Change the font to “Wingdings”.

4. Please refer to example 1 in the attached Excel file.

5. Should you wish to decrease the length of the bar chart simply divide “A1” in the above formula by 10 or by whatever number makes the most sense. By way of example, the formula would look like this =REPT (”n”,A1/10). See example 2 in the attached Excel file.

It should be noted that by changing the “n” in the above mentioned formula you can display different images. For example, capital “J” will display a smiling face while a capital “L” will display a sad face. See example 3 in the attached Excel file.

Dealing with Negative Values

The above formulas work well when you are dealing with positive values. However, if the value in column A is negative the graph in column B will change to a string made up of a number of different symbols thereby loosing the desired effect (See example 4 in the attached spreadsheet).

One way to overcome this limitation is by way of an IF statement like:

=IF(A21<0,REPT(”n”,ABS(A21/10)),REPT(”n”,A21/10))

Explanation of the above formula:

1. Assume the value you are trying to show in a bar graph is located in cell A21. This value is also negative.

2. The formula begins by saying if the value in A21 is less than 0 i.e. negative, then repeat “n” by the absolute value (ABS) contained in cell A21 and then divide this number by 10. By using the absolute value you are tell Excel to ignore the negative sign and treat the number as a positive value.

3. The next part of the formula tells Excel what to do if the value is greater than 0.

4. Please refer to example 4 in the attached file.

Interesting additions to the above would be to use conditional formatting to change the color of the graph to say red for negative values and to blue for positive values. Let your imagination guide you!

Excel has an abundance of string functions that can be used to manipulate data. In this article we will look at how to convert somebody’s full name into their initial and surname. So for example Mark Doppler becomes M Doppler.

Lets say that we have a series of names like so:

It is possible to do this all in one LONG formulae but instead we will build it up - step by step.
The full name is entered in Column A. So we first need to find the person’s Initial. Excel provides us with a function called LEFT:

= LEFT (Cell Reference, Number of Characters)

So LEFT(B4,4) will return the 4 leftmost characters of value in cell B4. In this case we just need the first character of each string. For cell C4:

C4 = LEFT(A4,1) will return the H in Hunter

And then copying down over the entire range we have:

Now we need to “break” the name into its first and last name. This can be done by looking for the space character which separates out these two. The FIND function allows us to find the position of one string within another:

FIND(Search String, Source String, Starting Position)

So we need to find the position of the space in the name “Hunter Davies”:

E4 = FIND(“ “,A4,1)

Entering this formulae and copying down:

We now need the length of the name – which will help us to break the string. The function LEN gives the length of a string. E.g LEN(H22) will give the length of the string in Cell H22. So in cell G4 we type:

G4 =LEN(A4) so that we have the length of contents of cell A4 (our original name):

Just as Excel has the LEFT function, also has a RIGHT function – to the rightmost characters of a string. Now if we look at the difference in the last two columns e.g 7 and 13 we get 6. The 6th rightmost characters of the person’s name are its surname So we have the surname as:

I4 = RIGHT(A4,G4-E4) and then copying down:

So we’re almost there – we’ve worked out the Initial and the last name. Now we just have to combine them together. This can be done by using the CONCATENATE function – which just joins up all the strings:

K4 = CONCATENATE(C4,” “,I4) – as we need to join up the initial, then have a space and then the last name:

And we see how step by step we have gone from the full name to the desired result. This is a generally an approach that I recommend for Excel – small discrete steps rather than one big clunky formulae.

An introduction to Dynamic Ranges

The VLOOKUP function is often used to find information that is stored within tables in Excel. So for example if we have a list of people’s names and ages:

And then we can in a nearby cell use the function VLOOKUP to determine Paul’s age:

So far this is a fairly standard. But what happens if we need to add some more names to the list ? The obvious thought would be to modify the range in the VLOOKUP. However, in a really complex model, there may be several references to the VLOOKUP. This means that we would have to change each reference – assuming that we knew where they were.

However Excel provides an alternative way – called a DYNAMIC range. This is a range that expands an updates automatically. This is perfect if your lists are for ever expanding (e.g month on month sales data).

To set up a dynamic range we need to have a range name – so we’ll call ours AGE_DATA. The approach for setting up dynamic ranges differs between Excel 2007 and earlier versions of Excel:

In Excel 2007, click on “Define Name” under formulae:

In earlier versions of Excel click on “Insert” and then Names” .

In the pop up box, enter the name of our dynamic range – which is “AGE DATA”:

In the box labeled “Refers To” we need to enter the range of our data. This is will be an achieved used by an OFFSET function. This has 5 arguments:

=OFFSET(Reference, Rows, Cols, Height, Width)

- The Reference is the address of the TOP LEFT corner of our range – in this case cell B5
- The Rows is the number of rows from the TOP LEFT that we want that range to be – which will be 0 in this case
- The Cols is the number of rows from the TOP LEFT that we want that range to be – which will be 0 in this case
- The Height of the range – see below for this
- The Width of the range – this is 2 has we have TWO columns in our range (the persons name and their age)

Now the height of the range will have to vary depending on the number of entries in our table (which is currently 7).

Of course we want a way of counting up the rows in our table that updates automatically – so one way of doing this is to use the COUNTA function. This just counts up the number of non blank cells in a range. As our names are in column B, the number of entries in our data is COUNTA(B:B).

Note that if you were to put this in a cell you would get the value 8 – as it includes the header Names. However, that it is immaterial.
So in the “Refers To” box we put:

=OFFSET($B$5,0,0,counta(B:B),2)

And click the OK button. Our dynamic range is now created.
Now return to the VLOOKUP formulae and replace the range $B:4:$C11 with the name of our new dynamic range AGE_DATA so we have:

So far nothing has changed. However if we add a few more names to our table:

And in the cell where we had Paul, replace it with a new name such as Pedro (that wasn’t on original list):

And we see that Excel has automatically returned Pedro’s age – even though we haven’t changed the VLOOKUP formulae. Instead the scope of the dynamic range has increased to include the extra names.
Dynamic ranges are very useful when we have increasing volumes of data – especially when VLOOKUP and PIVOT tables are required.

The Excel IF function is one probably one of the most powerful formulas within Excel. IF() allows you to take control based on the outcome of decisions.  The basic syntax of the Excel function is:

=IF(TEST, OUTCOME A, OUTCOME B)

Where:

•    “TEST” is a condition  that we wish to evaluate
•    “OUTCOME A” is what will happen if the condition is TRUE
•    “OUTCOME B” is what will happen if the condition is FALSE

So here some quick examples of tests that could be performed:
—————————————————————————————
IF(A19 > 1000,  …., ….) which says check if the value in cell A19 is greater than 1000

IF(B25 <= 2000, …..,….) which says check if the value in cell B25 is less than 2000.

IF(LEN(W20)=10,……,….) which says check  if the length of the value in W20 is 10.

IF(ISNUMBER(A5),…….,….) which says check if the contents of cell A5 are a number.
—————————————————————————————

Now for some concrete examples of how the IF function works in practice:

1. Start a New Work book.

2. In Cell A1 enter the value 10 (and press Enter)

3. Then in Cell B1 enter the following formula:

=IF(A1 > 5,”BIGGER THAN 5″,”LESS THAN 5″)

4. The screen should look like this now:

5. If you’ve entered the formula correctly, you will see the message “Bigger than 5” appear in cell B1.

6. The formula you’ve entered in Cell B1 performs the test “A1> 5” i.e it checks if the value in Cell A1 is greater than 5.  Currently the value in Cell A1 is 10 – so the condition is TRUE and the message “BIGGER THAN 5” appears

7. If we now change the value in cell A1 to 2:

Thtn the message in cell B2 is now “LESS THAN 5” as the condition is FALSE.

8. You can keep changing the value in cell A1 and the message in cell B2 will adjust accordingly.

9. Of course there are situations where the condition could give mischievous results:

•    What happens if we enter the Value 5 in Cell A1 ?
•    What about if we leave Cell A1 blank ?
•    What about if we put some text in Cell A1 e.g  the phrase DOG

Very basic stuff here, click “Next” below for some more advanced examples.

I’m getting a lot of requests to post some links for excel inventory templates. Inventory templates could be used for home, business or personal uses. Helps keep track of all sorts of stuff.

Inventory/cost of goods sold analysis

Allows you to track costs and profits for a couple specific products. You can also use the template to calculate cost of goods sold, provide basic inventory valuation metrics, and provide a breakdown of the composition of ending inventory.

This version only works for Microsoft Excel 2003 or higher, and you need to use Internet Explorer to download it. It also checks to see if you have the real version.

Download Inventory/cost of goods sold analysis

Not really a playing game, but more of a quiz test.

The Simpsons game tests your knowledge of character names. As a long-time Simpson fan, I was surprised by how little names I remembered. Seriously a shameful moment, only remembered main characters and a few others.

Download the Simpsons game

We all know this game, or should know it. I grew up on this game, and love it. This Sonic game, created by menace.ch, allows you to load the game within your Excel spreadsheet.

Sonic the HedgeHog, a excel game is about an arcade/action game where you drive Sonic through the different stages in which, in the final stage we will fight against Dr. Robotnick. In Sonic Excel we can play at least with 4 different characters : Sonic, Tails , Knuckles y Cream.

The game is a simple fun game. You run along and use Sonic to spin and get points. Great way to unwind.

Download here