Brand it like Beckham...

Brand Beckham. Known worldwide. He is handsome but rugged, a man's kind of man on the field, a woman's kind of man with children and fashion. She is forever pouty, perfectly groomed, and her fashion label is growing beyond anyone's expectations...besides maybe her own.
 
So how do they do it? I'm no marketing guru, but what strikes me most about the couple is that there is almost a uniform look about them. Their looks never change too drastically, they have a style and they stick to it. Victoria's fashion lines are instantly identifiable as hers with their clean cut lines and simple, yet always smart, designs. I mean, who doesn't immediately recognise these two?
 

 
 
So well done them...branding done right. But do you know that you can brand your own Excel work? You can, I've done it. Most of my colleagues could open one of my workbooks and instantly say, "Oh Vicky, this is one of yours, isn't it?" How did I do it? I certainly didn't have "Vicky's workbook" emblazoned across the top. I try be a bit more subtle than that....
 
You may have noticed through my blogs that I like the colour blue. Well, most of my worksheets have dark blue headings, with white bold text. Any other lines that need highlighting underneath will be highlighted in lighter shades of blue. Borders will be done in blue. And there will be NO GRIDLINES!! (I hate gridlines, truly I do).
 
These simple things managed to somehow "brand" my workbooks so that they became easily identifiable as mine. Now, this may not seem important to you, but having workbooks always look the same will a) let your bosses know that the brilliant analysis floating around the company is yours, and b) make your spreadsheets easy to read.
 
You're probably thinking though that you couldn't be bothered re-doing all that formatting for all your new spreadsheets. The good news is, you don't have to. To have all your favourite settings lined up each time you open Excel is easy. Here is how:

Open a new, blank workbook and choose the way you would always prefer your workbooks to look by:
1) Selecting a theme. This decides the colours, fonts etc for your entire workbook. For more on this, read my earlier blog on formatting here.
2) Using your Styles toolkit. On your Home menu tab, you will find Styles midway across, with maybe 4 coloured boxes. Click on the downward arrow and you'll get a full choice like this:

Now, depending on the theme you have chosen, your Titles and Headings colours will be different.
If you prefer to always have any text in your workbook aligned to the middle of the cell and wrapped, or in Italics, or Bold, I would recommend making these changes to your "Normal" cell choice.

To adjust your "Normal" cell choice, just right click on the box, and select modify:

Click on the Format button that comes up, and adjust as you please. Click OK, and any cell in your workbook which is classified as Normal (which is all of them at this point), will be adjusted to your new settings.

You can adjust any of these cell types, so you could choose a heading type that you will always use, and make the font Bold. To then apply that formatting to your workbook, click on a cell you want to use as a heading, and click on that Heading button in the style box.

If you want to change it back to normal, click on the cell, and click on the Normal button again. (Note - you will need to change the alignment etc on your Heading buttons too).

3) Decide whether you want a header or footer on all your workbooks. If so, go to Insert - Text - Header & Footer. So for example, if you wanted the date and the file directory as a footer on each of your workbooks, click on Go to Footer in the Header & Footer toolbox, and you will get something that looks like this:

Click in the box where you want your footer to go (left, right or centre) and then click on your options above - so for our example you would click on Current Date and File Path.

Once you are done, click anywhere else on the sheet, and got to View - Normal to take your sheet out of the Page Layout mode.

4) Make any other changes you would like and make sure you are happy with the way your workbook now looks.

All these changes you have made now apply to the workbook you have open. To make all your new workbooks look like this, save your workbook as an Excel Workbook into your XLStart folder.

To find the address for this folder, go to File - Options - Trust Center - Trust Center Settings - Trusted Locations. The address will be the file path with XLStart in the file name. Click on the path, and the full address will appear below the box.

Once you have saved your workbook into that folder, everytime you open Excel, your new, branded Workbook will appear. And you can start spreading your own unique brand across the office!


A big thank you to the reader who sent in this week's request. Hope you found it useful. If you have specific query you would like covered, please leave a comment below.

If you would like to sign up to receive our weekly reminder of the blog, click here.


Happy spreadsheeting!

Saving you time, saving you money....

There is a company in South Africa that has been around since I was a just a sprog. It is called Nashua and provides business solutions in terms of printing equipment. And it's tagline has always been "saving you time.saving you money.putting you first".

This has to be, in my mind, a better tagline than "Just do it" or "I'm loving it". Because it has stuck in my mind since I was a small child, and every time I think the words "Saving you time", the rest of the tagline immediately pops into my head.

So yes, its a brainworm. But its also a tagline that makes sense, because saving time in business almost always equates to saving money. So here is my way to "put you first". I am going to share my top 5 time-saving tips for Excel.

Yes, some of these are basic, but its normally the basics we do everyday...so save time on those and you'll have more time for the fun stuff.

Tip 1 - Learn your shortcut keys for the tasks you do most often.

Shortcut keys are basically a specific keyboard sequence you press, normally whilst pressing Ctrl or Alt, to perform a specific task. So for instance, the shortcut key for Print is simply Ctrl P.
To add filters to a page, you would hold down Alt and push D then F then F.

Now, instead of giving you a ridiculously long list of shortcuts, you can find out your favourites on your own. The best way to do this is to work your way through the menu ribbon as you normally would, but push Alt before you start.

Here is an example...my all time pet hate of gridlines, and how to get rid of them. So first off, click on your sheet, in the right cell if your task is cell specific, and then click Alt, and you'll get all these little letters in boxes all over the menu bar, like so:

Click on W for View, and more letters will pop up, as below:

As you can see, you need to press VG to either untick or tick the gridlines box. Now, you can follow the letters each time you do it, but if it is something you do regularly you will soon learn the sequence, and will type in ALT W,V,G automatically. Practice forms habits, and this is a good one to learn!

Two side points:
1) If you have a dropdown list in your menu, look at the underlined letters in each selection - those are your shortcut keys.
2) As you can see from my first image, the quick access toolbar at the top has numbers assigned to each icon. Again press Alt, then the number, and the specific item on the quick access toolbar is activated.

Which leads me beautifully to my next tip:

Tip 2 - Add your favourite menu items to the Quick Access toolbar.

If you're not strong enough just yet to break the bond with your mouse for a full time relationship with your keyboard, then use the Quick Access toolbar. That is the thin strip across the top of your menu ribbon, and you can add whatever menu items you like to it. To do this, simply click on the little black downward arrow icon to the right of the bar, and you'll get a dropdown menu like this:

The most basic are provided for you to pick, but if you want more choices, click on "More Commands..." and you'll get pretty much every menu item you can think of. Click on the one you want and press add, and you'll see the menu icon up on your Quick Access Toolbar. Again, easy stuff, but will save you scrolling through menus.

Tip 3 - Use your autofill to create lists

If you are creating a list of, say, months, instead of typing in each month in a new cell, just type in January in the first cell, February in the next, and then highlight both, and drop your mouse to the bottom right hand corner. A little black cross will appear. Drag this down and your list will appear automatically.

The little black cross also works effectively for filling in lists. You don't even need to drag it down. So imagine you had a formula next to your newly formed list of months. If you type your formula into the next column on the first cell, you can again go to the right hand corner, and double click the little black cross. This will copy down your formula to the bottom of your list.

A big time saver!!

Tip 4 - Always use formulas where possible

Imagine you had a complicated spreadsheet calculating various cost and sales prices based on specific VAT rates and exchange rates. While you were creating your spreadsheet you happily typed in your formula, for example = (B4 x C4) x 0.14, because you assumed your VAT rate would stay at 14% forever. But the taxman is nothing if not fickle, so when, not if, he changes the VAT rate, you would need to change each formula manually (or at least use a FIND and REPLACE function).

If you set up your spreadsheet from the start to have your VAT rate in one cell, and you referenced all your formulas to that cell instead, you would only need to change one cell and all your formulas will automatically be updated.

I know it sounds simple, but it is such an easy mistake to make because when you are creating a spreadsheet you are normally focusing on the output and not too much on the design. Try get into the habit of stopping at each new formula you write and say "Have I made this as easy as possible to adjust in the future?".

Tip 5 - Get to know your Paste Special options

Most people know about Paste Special to paste something as values. But this lowly little box of options has so much more to offer, so take the time to get to know it.

These are fairly self explanatory, but I'll go over my favourite ones:

Formats - does what it says on the tin - another way of copying formats of cells from one spot to another.

Multiply - this may sound strange, but I use this as a nifty trick to change data from text to numbers. What you do is type a 1 somewhere else in your spreadsheet, copy it, and then highlight your list of text and Paste Special -Multiply. This will multiply the text numbers by 1, turning them into numbers you can actually use.

If you ever need to take your numbers into thousands, you can do the same thing, just type in 1000 into a cell and Multiply it across your range. You can also Divide by 1000 to bring your figures into more reportable size numbers.

And what if you create a lovely long list of items going down your sheet and then realise that you actually want them going across your sheet as headers? Easy, just highlight them, Cut them, click on the cell where you want them to start going across, and then Paste Special - Transpose.

That's a massive time-saver all on its own.

So there you have it. My 5 top tips on saving yourself time, and hopefully money, whilst using Excel.

Why not share one of your favourites on the comments below? I'd love to hear them.

Don't want to miss out on the blogs? Why not sign up and we'll send you a friendly email to remind you!

Happy Spreadsheeting!



Struggling to get a date? It's all in the set up....

So, hands up - who has been set up on a blind date by mutual "friends"? And how did that go for you? Disastrous? Embarrassing? Live happily ever after?

Going on the film versions, blind dates never work out that well. Because everyone has a different idea of what an 'ideal date' is. And two people, although they may seem to want the same thing, will have different preset ideas of what that 'thing' is. I think it is safe to say that in general, blind dates should be approached with as much care as you would use when trying to tackle a rabid tiger. Who has a sore tooth.

The same goes for Excel (not the tackling the tiger bit). Two people will have a different approach to a problem in Excel, because they have different preset ideas, or ways of working. As I keep on repeating, there is always more than one way to find a solution in Excel. A perfect example landed in my email box this week. In my newsletter last week, I pointed out that I was kinda running out of ideas for this blog.

(Side note - please note this point and feel free to comment below with topics you would like covered or problems you would like solved....)

Anyway, I got a lovely email from Mr B, who provided me with a variety of solutions on how to calculate the difference, in hours, between two dates and times. Now, working with dates and times in Excel is tricky. I have already done a basic blog on this (click here for a reminder), but Mr B reminded me of a brilliantly handy formula - the DATEDIF function. What this formula does is calculate the difference between two dates, but for specific criteria. The best way to explain this is to show you. Take note of the letters in inverted commas in the formulae, that is what defines the result you get:

The last 3 need a bit of explanation. Days only simply looks at the 'day' part of the date and subtracts one from the other (so 16 - 2). It ignores the months and years. The same goes for month only (so 8-5 in our example). Dates, ignoring years, assumes both dates fall into the same year and just calculates the difference in days.

Note when using DATEDIF that your Start Date comes first in the formula, and must always be earlier than the End Date.

So, after that whirlwind tour of the DATEDIF function, lets look at what Mr B sent me. Solution 1:

Here Mr B uses the DATEDIF, HOUR and MINUTE functions to break down the difference into days, then minutes, and then takes the answer back up to hours. Go through it at leisure...

Solution 2:

Again, go through this at your leisure, but he has shortened the equation by using the IFERROR and  MOD functions, which in this case basically say, if the answer is negative, then subtract the answer from 1 to get a positive.

These are both good solutions, and my thanks again to Mr B for sending them on. Clearly though, I have a female brain, so immediately thought of a different solution. I would use formatting to get this right. Under custom formats for numbers, you can select the format dd/mm/yyyy  hh:mm. So go to Home, Format, Number, Custom and scroll down till you find this beauty.

Then, to get the date and time into one cell, you would add the 2 together. So in the example above, to get the first start date and start time combined into one cell, it is as simple as =B4+A4. Doing this, you can get the table below:

To get the difference in hours, simply use =C3-C2, and keep the result cells formatted as numbers.

So, like I said, different strokes for different folks, but it all comes to the same answer.

Job done, another dating issue solved....

If you're new to this blog, and would like a reminder every time a new blog is posted, why not sign up here?

Happy spreadsheeting, and remember to send on any queries!

Failing to plan is planning to fail....

Who hasn't had that old nugget thrown at them by a parent or teacher or any other busy body? And who hasn't inwardly (and maybe outwardly - depending on bravery levels) rolled their eyes heavenwards in exasperation? Why are we exasperated to hear that? Because we are normally hearing it too late. When to go back and re-plan and re-do and make it right would take just as long as fixing the problem would.

I am on the fence on planning, personally. I tend to go to extremes...either going with the flow and dealing with whatever comes my way, or following that other well-loved bit of advice - "hope for the best, plan for the worst", and getting myself into knots with too many plans.

However, I am fairly adamant on planning in Excel. Proper planning can make your spreadsheets, and work, so much more efficient. Also being able to see the woods for the trees, and selecting the right approach to a problem, helps immensely.

Take this example. My sister is in IT and was recently battling through a  merger of a couple of systems. She emailed me asking for some help on a VLOOKUP formula. Basically, they were running tests on various areas. Lets say that one test result would look like this:

She was getting each set of test results back and saving them as a new sheet into one summary workbook. Each sheet would have a name of Test1, Test 2 etc... She now wanted to create a summary that would look up all the results on one page. However, if she used a normal VLOOKUP, she would need to change each new column's formula to a different sheet name (Test1, Test 2 etc). Not the end of the world, but time consuming. So I suggested she use this instead:

As you can see, I have inserted an INDIRECT formula into the VLOOKUP. By doing this, when she drags the formula across the columns, the VLOOKUP will automatically pick up the new sheet name, which is also in the header row 2, saving her having to replace the sheet name for each column of results.

Side note on INDIRECT formulas - these are very handy little formulas, but pretty tricky to explain. Basically, an INDIRECT formula inserts the results of the cell that you reference in your INDIRECT formula. This is very useful if you are going to be changing things, like in the example above. So we reference the cell C2, to bring back 'Test1' into our VLOOKUP formula.

Anyway, back to the problem. Well, it was no longer a problem, it was solved. And I did exactly what she asked, I adjusted her VLOOKUP formula to change the sheet names.

However, with hindsight, a possibly better solution would have been to use a consolidation. It would have required more work at the set up, and I suspect she was beyond that stage, so I wasn't brave enough to throw the quote at her!

How I would have used a consolidation for this testing is as follows:
1) I would have created all the test sheets before hand.
2) Instead of Pass or Fail, I would assign a number to each status. So 100 for a Pass, 50 for a fail and 0 for not tested. This will make consolidating easier at a later stage.
3) I would then have created my consolidation summary sheet, using the Excel consolidation tool.

How?
Here goes...
1) Go to a blank sheet.
2) Go to Data - Data Tools - Consolidate
3) A pop up box will appear. I have already filled this one in, and will talk you through it below:

 

Function: - You can choose a function like Sum, Count etc. I have chosen Average for this example...you'll see why later.

Reference: - Click into this box and then go the sheet you want to include in your consolidation. It is better to include the entire column or row if you are going to be adding in more data.

Click Add and it it will add the referenced cells into the box below, which contains all the references you want consolidated.

Use labels in - I have ticked the Left Column, as this is what Excel will use to consolidate the data. This is very handy because you don't have to have all your sheets identically laid out, and you don't need to have an identical list of items. Excel just matches what it can.
It works the same for labels in the top row.

Create links to source data - this is very important as it brings back an actual formula showing all the sheets linked. It also ensures your data will be updated when the source sheets are changed.

Click OK, your pc will whirr for a second, and then hey presto, you get this:

 

These are the results of four different test sheets, averaged out. To see the full detail, click on the little 2 on the top left hand corner, and you get the works:

Now, if you did all this before you started testing, you could send out the links to the individual sheets to each tester. As they completed their own tests, you would get a running update on how your tests are looking. And you can keep on kicking butt until the averages are all 100!

So that is a quick overview on the consolidation tool. Some notes:

1) If your worksheets are all laid out identically, you don't need to use labels in the consolidation.
2) Try to get your consolidation perfect the first time. Making adjustments afterwards will probably require you to restart your consolidation.
3) Plan, plan, plan.

Don't want to miss out on the weekly tips? Why not sign up here? We'll remind you...in the nicest possible way!

Happy spreadsheeting!

More vocabulary lessons before school starts...(and this long non-summer holiday ends)

Man, is this 'summer' holiday dragging on forever? In the height of summer, the bridge outside our house has been flooded twice. The rainfall carries on, the children get more cranky and bored, and parents slowly, but surely, lose their minds.

In a rare moment, I found my sense of humour, and have tried to track the effect of long summer holidays on a parent's patience levels. In continuation of our series on improving your Excel vocabulary, we will look at 2 ways we could calculate this - the easy but long winded way, and the short but slightly more complicated way.

First up though, lets look at some made up figures, formulas and assumptions:

Let me explain my thinking on the figures above:

Variable Factors:
 - We have 8 weeks of summer holidays. Cell C3 shows us what week we are in out of those 8 weeks.
 - We assume the maximum number of outings/activities a child can attend a week is 14 (2 a day). Cell C4 shows us how many outings the child has actually attended.

Parameters:
 - The average assumed cost of an outing is €15.
 - The calculation of a child's boredom level is =(C3/C8)*(1-(C4/14)). This is saying the longer the holidays go on, the higher the boredom level will be, and can only be mitigated by more and more outings.
 - The weekly cost of outings is simply C7*C4
 - The % weekly cost of total possible is C10/210
 - The parent's patience levels is calculated as such =1-(((C3/C8)*0.45)+(C9*0.25)+(C11*0.3)). Before you lose your temper trying to figure this out, I have simply given different weightings to the current week in the holiday period, the child's boredom level and the cost of keeping them entertained. Added them all together and inverted.

Method 1: Manual What If Analysis

In this method, you could use the table above and change the variable factors (i.e. Cells C3 & C4) to obtain individual answers for various scenarios. So you could change the week to 6 and the number of outings to 4 and the parent's patience levels would be sliding down to 44%.This is great if you needed to get a once off answer every now and again, but what if you wanted to get the bigger picture? You would use:

Method 2: A What If Data Table

This is the simplest of the What If tools Excel provides. It is a data table that you can either use one or two variables on. We will go straight to using two variables. You need to start by setting up your table like so:

You will need to add in the variables in cells F4 to F17, and G3 to N3. The headings (Weeks in holiday and Weekly outings taken) are optional and should not be included in your range when creating your data table. The formula in F3 is = C12, which is our calculation of our parent's patience level.

Once you have done this, highlight the range F3 - N17 and go to Data - Data Tools - What If Analysis - Data Table. You will get a little pop up box that you need to complete as follows:

Press ok, and your results are displayed as below:

Add in a bit of conditional formatting, using the colour scales, and the problem becomes immediately apparent:

By week 8, a parent is ready to explode...which begs the question as to whose great idea it was to have a 8 week long holiday anyway?

Now, you may be wondering why this is at all helpful to add to your vocabulary. Well, you may not need this exact calculation (unless you belong to the Board of Education, and then please feel free to distribute to the powers that be!). But you may want to calculate the profits to be obtained if you sell a product that has reduced production costs per product the more units that are produced. Or calculate various payment options on mortgages with different interest rates.

 My newsletter this week will have some more examples....why not sign up here?

Happy spreadsheeting!

Don't underestimate the seductive power of a decent vocabulary

Isn't that just brilliant? I saw that quote on Facebook this week, from LawsofModernWoman. It went straight into first place on my list of favourite quotes, kicking the long-standing favourite off it's perch - "Never use a big word when a diminutive alternative will suffice".

Both of these quotes segue nicely into what I would like to cover today... there are sometimes many different ways to do the same thing in Excel. To have a decent 'vocabulary' of Excel tricks will make your day easier, and will increase your seductive power (to your PC and Excel geeks, at least)! But to remember that the most complicated method is not always the best method, well, that might just take you into hero status.

So here is Part 1 of a series in how to increase your Excel 'vocab' and hopefully teach you a couple of  easy to use tricks or shortcuts. The data we will be using is from a list of the 'supposed' best selling books of all time in the UK. Just a detour here - this list is provided by Nielson Bookscan, via the Guardian (click on the link for the full list of the Top 100. I haven't included it here!) I am having serious trouble believing this is the best selling list of all time, so am hoping it applies to maybe the last 10 years. Anyhow, it had good data layout so I used it. Suspend your belief when you look at the results. If nothing else, it will give you ideas for your beach reads....

Tip 1 - Filters are sometimes better than pivot tables.

I know that just 2 weeks ago I was singing the praises of pivot tables in my blog post, but there are times when you just need to have a quick check on something or you don't want to create a completely new table of data. If so, use the filter.

To get the filter onto your data, click somewhere in your table, and go to Data - Sort & Filter - Filter. This will apply the arrows to your header row.

Important point 1 - You can't have blank rows or columns in your table. If you have blank rows, the filter will only apply down to the last row before the blank row. If you have blank columns, the filters won't always go the whole way across. So have a complete, space free table of data before you begin.

Then select which column you want to filter on. In our data below, you may want to check how many of the top 100 books are in the Children's and Young Adult Fiction categories. So you click on the little downward arrow in the Genre column, and get the filter dialog box. At the bottom, all the genres will be selected, so first click on the tick mark next to Select all, to deselect them, and then choose the topics you want to look at:

Press ok, and your table will look like this:

That is 17 out of the Top 100, excluding Annuals and Picture books. How do you know 17 without counting each line? Look at the bottom left hand corner, and it tells you:

Now, what if you wanted to know how many of these 17 books had volume sales of over 1.5 million? You click on the arrow on the Volume Sales column, and select Number Filters - Greater than, and type in 1500000 in the box that appears, like so:

The answer to that is 11, all the Harry Potter and Twilight books. So, you can apply filters to more than one column at a time. Note, too, from the box above, that you can apply more than one filter to the same column. Just select your choices from the drop down boxes.

Important Point 2 - Always be aware of when you have a filter applied. You can easily assess this by looking at the colour of the row numbers. If they are blue, you have a filter applied somewhere. The arrow icon on the column header changes if you have a filter applied so you can easily assess which column is filtered.

To clear a filter, go to Data - Sort & Filter - Clear.
You can also filter by colour. You may have noticed some of the book titles are blue. I have highlighted all the books I have read. To apply this filter, do as shown in the image below:

This is ridiculously handy if you are fond of using colour to highlight cells. Sadly, I appear to choose all my books from best seller lists. Happily, I still have 48 to get through before I exhaust the list.

Anyway, onto to shortcuts:

Shortcut number 1: To put on, or take off, filters in a hurry, press and hold ALT, whilst pressing D F F

Shortcut number 2: To clear all filters that are currently applied, press and hold ALT, whilst pressing D F S

Tip 2 - You don't always need a formula in a cell to calculate something

Don't worry, this is a quick tip. Using our example above, with all the books I have read filtered, how would you calculate the total number of books sold? Or the average sales volume? Without typing in a formula?

Easy - highlight all the values in the Sales Volume column, and look at the bottom right hand corner. You will see this:

That's your answer. Nice and simple, no need for a formula. If you right click on the toolbar next to these numbers, you can select some other options, like minimum and maximum.

Very useful if you're in a hurry and you need to shout an answer to a irate / impatient / cranky boss!

Our newsletter this week will cover how to remove duplicate data using 2 different methods....sign up here.

This was part 1 of 'Improving your Excel vocabulary.' Part 2 will follow next week. Have a good one!


Happy spreadsheeting!

Excel is not all about the numbers.....

What? What do I mean, Excel is not all about numbers? Surely it is the most formidable number crunching tool available? Well, that's debatable, but Excel does more than crunch numbers. It plays with words, and I thought I would share some of my favourite word / text formulae with you.

Before I start, I just want to point out that although these formulae may manipulate text in Excel, you can also use some of them in number formulae, date formulae etc. I'll give you some examples of how, but these little gems can make your life much easier, so take heed.

As we are now in the thick of the 2012 games, I thought I would use some of our data from last week. USA, while I write this blog, has the highest number of medals, so we'll look at their 2008 stats:

Now, imagine you had some inanely boring job where you had to type out how many medals each country had won, by medal type, using this data table. You could type out each sentence or you could use a formula to create the sentences as shown below:

All this formula does is combine text (which is typed in between the quotation marks and highlighted in blue) and the data from various cells (highlighted in red). To combine it all together, we use the & symbol between each different element of the sentence. So in this formula we use the year, the number of total medals and the medal type, and throw some text into the mix to make it sound sensible.

You could also use the concatenate function to do this, but I prefer to just use the &. It makes for shorter, and easier to follow, formulae.

If you're pedantic, like me, and you don't like the fact that the medal type is all upper case, use the PROPER function, as highlighted in red below:

The PROPER function changes text to the Proper format, so each new word starts with a capital letter, followed by lower case. If you want all lower case, use the LOWER function, all upper case, you guessed it, use the UPPER function.

Another handy formula to know for text data is the LEN function. This will tell you the number of characters in a cell. So if the Gold Medal sentence above is in cell A1, the formula =LEN(A1) would give you an answer of 34. Note that spaces are included in the character count.

But my absolute favourite 3 text formulae can be used on one single cell. Imagine Cell A1 contains GOLD 16 USA. You need to split this up between Medal Type, Medal number, and Country. You can do this using the 3 formulae shown below:

These formulae are pretty straight forward. The LEFT function above says, "go to A1, and bring back the first 4 characters". The RIGHT function says, "go to A1, and bring back the first 3 characters, starting from the end and working backwards." The MID function says, "Go to A1, go to the 6th character, and bring back 2 characters".

Now obviously, these aren't ideal to use if your data is constantly changing length, but if you have constant data, these are brilliant. Or, when used in combination with pretty much any other formula, they are more powerful than Michael Phelps himself.

Now for some fun stuff....

A client received a file from a third party in a excel file which had been formatted from a csv file. This file contained product codes, some of which are, for example, 02-10. Now, open any Excel spreadsheet and type this code in. What do you get? Yes, Excel immediately thinks you're typing in a date, and shows you 02-Oct. So you change your number to Text, and Excel kindly gives you the serial number of the date 02-Oct, which is 41184. By which stage you have completely lost your original product code.

I thought, I pondered, I googled, and I couldn't find a quick and easy way to solve this ( if you do know a quick way to solve this, please share as a comment below!). So I came up with the formula below, which gives us the correct answer of 02-10:

I have colour-coded this formula to try simplify it. Starting with the DAY formula, this takes cell A2, and brings back the day from the full date, ie - the 2nd or 2. Using the TEXT formula, I have said take the answer, and format it as a two digit number ("00"), giving us 02.
I did the same with the MONTH formula, which brings back the number month, or 10 in this example. Again, I have set the format to a two digit number. I then inserted a dash into the middle, and hey ho, we have a code 02-10.

This is just one example of how you can combine non-numeric formulas with numeric or date formulae. The possibilities are endless, don't be afraid to experiment with formulae, just keep track of your logic as you go.

In our newsletter this week, we'll look at some more text functions...sign up here.

Have a great week!