Excel Tip: Add a Christmas background to your spreadsheet

Well, this is my last post before Christmas, as I am taking some time off with the family over the Christmas break, so my next post will be in the New Year.

I thought I'd sign off with a simple, but seasonal post.

But, in case you don't read on, I would first of all like to wish all of my readers a very Merry Christmas and I look forward to working with many of you in the New Year.

If you want to add a bit of seasonal spirit to your spreadsheets, why not add a Christmas background?

You can easily add something like the Christmas Tree picture on this post as a background to any worksheet.

All you need to do is go to the Page Layout ribbon and click "Background". You can then browse to image you want to use and click OK. That worksheet will then use that picture as it's background, visible in cells that have "No Color" as their background colour. Note that a fill colour of White is therefore different to "No Color". A cell with a white background will hide the picture.

The picture will "tile", i.e. repeat itself indefinitely, and any cell contents will appear on top of it.

When the Christmas period is over and you want to go back to your boring old spreadsheet, just go back to the Page Layout ribbon and click "Delete Background".

Have a great break and see you in 2015!



If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Excel Tip: Calculating Net Present Value (NPV)

Last week we covered the IRR function, which enabled us to calculate the Internal Rate of Return of a project.

This week I am going to introduce the NPV function which allows us to calculate Net Present Value - a function closely related to IRR.

The Net Present Value of a series of cash flows, gives a value of those cash flows today discounted by a required rate of return.
The required rate of return represents represents the investor's time value of money. This is often the rate of return achievable in alternative investments.

So, for example, if we have the following series of cash flows (the same example as used for the IRR post):

Initial outlay  £20,000
Year 1 positive cash flow  £500
Year 2 positive cash flow  £5,000
Year 3 positive cash flow  £8.200
Year 4 positive cash flow  £9,000
Year 5 positive cash flow  £9,000

and we require a 10% annual rate of return.

The syntax of the NPV function is:

=NPV(rate,value1,[value2],.....)

where rate is the required rate of return and value1, value2, etc. are a series of cash flows at the same regular interval as the rate, so if the rate is an annual rate, then the cash flows should be a year apart.

value1 is required, whereas [value2] onwards are optional. These can be entered as values or refer to a range. Note that these cash flows are assumed to be at the end of each period.

So we could write the function for our example as follows:

=NPV(0.1, -20000,500,5000,8200,9000,9000)

which returns £2,257.25.

More likely though, we will have the values held in a range of cells as we did in the IRR example:

B2  -20000
B3   500
B4   5000
B5   8200
B6   9000
B7   9000

In this case our function would be:

=NPV(0.1,B2:B7)

Obviously, we would normally also refer to a cell to provide the rate as well, rather than enter it directly into the formula.

NPV relates to IRR  because IRR is the rate that produces zero NPV, so:

=NPV(IRR(B2:B7),B2:B7)

will always return zero.




If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Excel Tip: Calculating the internal rate of return for a project

Last week I introduced the PMT function for calculating payments on a loan.

This is one of the many functions under the Financial heading in Excel. This week I want to introduce you to another. One that allows you to calculate the internal rate of return (IRR) for a series of cash flows.

The IRR is a method often used to compare alternative projects or investments. and is stated as a percentage return, within a specified period.

As with the PMT function, it is worth starting by stating the assumptions used in the calculation. The IRR function requires the cash flows to be at regular intervals and the return percentage it calculates is  for the length of that interval. So, for example, if the cash flows fed to the function are annual cash flows, then the IRR returned will be a percentage per annum.

Also, the cash flows involved must include both positive and negative cash flows. If you think about this is self-evident, as any return percentage would be infinite if there was no outlay and all cash flows were positive!

Typically this will involve an initial outflow (or investment) followed by a series of projected inflows.

For example, a business investment opportunity might be presented as follows:

Initial outlay  £20,000
Year 1 positive cash flow  £500
Year 2 positive cash flow  £5,000
Year 3 positive cash flow  £8.200
Year 4 positive cash flow  £9,000
Year 5 positive cash flow  £9,000

We might want to compare it to another project and a useful piece of informationin doing this would be to calculate the IRR for each project.

So, how do we do it.

The IRR function syntax is as follows:

=IRR(values,[guess])

Most of the time you won't need the optional [guess] argument, but I will come to that in a minute.

values is the range of cells that show the cash flows in chronological order.

So, for our example we could enter the cash flows in cells B2 to B7 as follows:

B2  -20000
B3   500
B4   5000
B5   8200
B6   9000
B7   9000

Our function would then be:

=IRR(B2:B7)

which returns an IRR of 14% (rounded to zero decimal places). This means that over this 5 year period, the project has an internal rate of return of 14% per annum.

The [guess] argument is there because Excel uses an iterative process to calculate IRR and will give up if it has not arrived at it after 20 iterations. To do this it must start with an estimate. If the [guess] argument is omitted, an estimate of 10% (or 0.1) is used. For most projects this will work with no problems, however with cash flows that fluctuate wildly and/or have an unusually high or low return, it may not get there in the first 20 iterations, if it starts at 10%.

If this happens it will return a #NUM! error. You can then add a guess to the function so that it starts closer to the true answer and therefore can get there within the 20 iterations.

Next week we will look at calculating Net Present Value, which is the flipside of IRR.




If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Excel Tip: The PMT function - calculating loan payments in Excel

Excel has a baffling array of functions under the heading of "Financial", many of which are only likely to be used by financial analysts. However, over the next few weeks, I will tell you about a few that can be very useful to the rest of us.

This week, I want to introduce you to the PMT function, which allows you to calculate payments on a loan.

Before we get into how the PMT function works, it is useful to note what types of loan it will work for (and what types it won't!).

The function works for loans with a constant interest rate and regular payments of the same amount. The only exception to this is that it does allow for a 'balloon payment' at the end of the loan.

If this is the nature of the loan payment you want to calculate, then the PMT function will work for you.

Essentially, you feed the function the loan details, and it returns the regular payment amount.

The syntax of the PMT function is:

=PMT(Interest Rate,No of Periods,Present Value,[Future Value],[Type])

The last two arguments are optional and we'll come to those later in this post.

The three mandatory arguments are explained below:

Interest Rate: This is the interest rate per payment period, so if you have an annual interest rate of 6% with monthly payments, the Interest Rate entered shound be 0.5% (i.e. 6% divided by 12).

No of Periods: This is the number of periods or payments of the loan. A 5 year loan with monthly payments would be 60, for example.

Present Value: This is the value, today, of the loan and for a new loan will be the loan advance amount.

So, say we have a £10,000 loan over 5 years with monthly payments and an annual interest rate of 12%:

Interest Rate = 12%/12 = 1% or .01
No of Periods = 5 x 12 = 60
Present Value = £10,000

So our function becomes:

=PMT(.01,60,10000)

and returns a monthly payment of £222.44. Note that the function actually returns -222.44 as we entered Present Value as  positive figure. If the receipt of £10,000 is positive, then for consistency, the payment returned is negative. We could enter the £10,000 as a negative number (i.e. a negative payment), which would return a positive payment.

In reality, we wouldn't normally enter these figures into the function directly, more likely we would hold them in cells so that we could experiment with changing their values. and the function would be more like =PMT(B1,B2,B3), where Interest Rate, No of Periods and Present Value were held in cells B1, B2 and B3 respectively.

The final two (optional) arguments work as follows:

[Future Value]: This is where you would enter a 'balloon' payment, or any balance that you wish to remain outstanding at the end of the period you are looking at. You might have a final 'balloon' payment of £5,000, and therefore the payment calculated would need to leave exactly £5,000 outstanding when No of Periods comes to an end. Alternatively, you might recalculate payments annually to hit a specific target balance at the end of each year. This should be entered with the same sign as the payments (i.e. the opposite sign to Present Value). This arguments is treated as zero if omitted, i.e. the loan will be completely paid off after all payments are made.

[Type]: This is a 1 or a 0 and indicates when in each period the payments are made. If this is 0, or omitted, the calculation assumes that payments are made at the end of each of the periods in No of Periods. Enter 1 for this argument if payments are made at the start of each period.


If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Excel Tip: Using formulae in text boxes and chart titles

Do you have charts with titles that regularly need to be changed? Or text boxes in diagrams that need to be edited to reflect the numbers shown?

It is really easy to have these update automatically, but it is not obvious how you are supposed to do it in Excel.

Say you have several charts that feature in a monthly pack and each chart refers to the current month in its title. "Cumulative Profit to November 2014" for example.

In a normal cell we can create this sentence in a formula. If A1 contains the period-end date, then we can use the following:

="Cumulative Profit to "&TEXT(A1,"mmmm yyyy")

Unfortunately, we cannot just type a formula like that into a chart title or a text box,  but we can do the next best thing.

We could type our function above into, say, cell B1, then go to edit the chart title or text box (so we can see the cursor ready for us to type). We can then click in the formula bar and type =B1. The chart title or text box will then always show the contents of cell B1.

No more editing charts each month!

We can use exactly the same technique in a text box, which might refer to this month's sales figure (held in cell A2). Our function would be something like:

="Your sales this month are "&TEXT(A2,"£#,0")

Once the text in our charts and diagrams is automated like this, they can simply be an output from the numbers, requiring no manual input.

Addendum: There is a little oddity when it is a chart title (not a text box). If the reference is on the same sheet, it must still have it's full name, e.g. =Sheet1!A1 rather than =A1. This is not an issue when the data is on a separate sheet.




If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Excel Tip: The Camera Tool - using parts of your spreadsheet in graphics or charts

This week, I just have a short post about a simple tool that is a bit of a hidden gem in Excel.

We know we can add graphics to an Excel spreadsheet by using the Insert ribbon to add images and shapes, as well as the many charts that we can create in Excel.

But what about when, instead of adding a bit of graphics to a spreadsheet, we want to add a bit of a spreadsheet to some graphics?

This might be having a table appear within the area of a chart, or in front of an image, for example.

We can do this easily using the Camera tool.

Before we can do anything, we need to make the camera tool accessible. Microsoft don't make it easy to find!

To do this we need to add it to a ribbon. We can do this as follows:

• Select File, Options and Customize Ribbon
• From the "Choose commands from:" drop-down, select "All commands"
• Scroll down to find "Camera" (they're alphabetical)
• Highlight any custom tab on the right hand side (the simplest is Home, Custom Edit)
• Click "Add>>"
• Click "OK"

You will now see this at the right hand end of the home ribbon.

Now we have the tool, it is really easy to use.

Say we have a small table in cells B3:D5 that we want to use within a chart, in front of an image, or within a set of shapes.

All we have to do is select cells B3:D5 and click the Camera Tool. The cursor turns into the same cross hairs you see when creating a shape, but this time you can draw the shape within your graphics (which don't have to be on the same sheet as the original table), and the result becomes an object (just like the shapes) that you can manipulate in the same way.

It's that simple!

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Excel Tip: Copying and pasting a row automatically

Regular readers of my blog will know that I am not normally a big fan of Macros. I would argue that in the majority of situations that I see them used, they are unnecessary and often result from a lack of understanding of how Excel can address the problem using its standard functionality.

Naturally this is not always the case and one particular situation that cannot easily be addressed using standard functionality is automatically copying a line of data from one row to another.

Before we get into it, I think it is important to point out that there are many times that I speak to a client who thinks this is what they need, when what they really need is a link between the two lists, or one list with a category field that can be used to report two lists from the same data.

I came across an example of when this was genuinely need just last week. In automating an accountancy practice's job tracking, I had brought all of their data together into three distinct lists - Clients, Recurring Jobs and actual Jobs.

This allowed them to store information at the right level:

Clients - One row per client (e.g. XYZ Ltd), holding all information pertaining to that client.

Recurring Jobs - One row per each recurring job (e.g. XYZ Ltd Annual Accounts), holding the current information relating to that particular recurring job.

Actual Jobs - One row for each actual job, which could be a one-off job or an instance of a recurring job (e.g. XYZ Ltd Annual Accounts 2014). This holds all information for that specific job.

Each Client, Recurring Job and Actual Job then has its own unique reference that is used to link them and access information from the other lists (using lookups and SUMIFs, etc.)

However, on the Actual Jobs list, we need a new instance each time a recurring job comes around. We also want to copy the recurring information over, with the ability to edit it just for this instance. That means copying and pasting a number of columns from Recurring Jobs to Actual Jobs each time.

This can, of course, be done manually, however that can be cumbersome and prone to error - not least in that in this particular instance I wanted to use "paste as values" rather than a full paste, as this could override conditional formatting that I had used on the Actual Jobs sheet.

However we can create a relatively simple macro to do it for us.

To add a macro you will need the Developer Ribbon which you can enable by selecting File, Options, Customize Ribbon and ticking the Developer option on the right hand side.

From the Developer Ribbon, click Macros and enter the name (e.g. CreateJob) that you want to give this Macro and click Create.

Then paste the following code between the lines already there, being Sub CreateJob() (or whatever name you gave the Macro) and End Sub:

    If ActiveSheet.Name = "Recurring Job Data" Then
    Range(Cells(ActiveCell.Row, "A"), Cells(ActiveCell.Row, "F")).Select
    Selection.Copy
    Sheets("ActualJobs").Select
    If ActiveSheet.AutoFilterMode = True Then
    ActiveSheet.AutoFilterMode = False
    End If
    Range("A10001").End(xlUp).Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Rows("1:1").Select
    Selection.AutoFilter
Else
Exit Sub
End If

The yellow items will need to be changed for your individual requirements:

Recurring Job Data is the name of the worksheet that we want to copy from. This part of the code, checks whether we are on this worksheet when the code is run, and exits the code if we are not. That stops the code running when we are on the wrong worksheet.

The A and F are the columns (from and to) that we want to copy over.

ActualJobs is the worksheet that we want to copy to.

A10001 is a cell in the first column of the worksheet that we want to copy to, beyond the bottom of the range of the existing data.

The code works as follows:

• Check the active worksheet is "Recurring Job Data" and exit the Macro if not
• Select columns A to F of the current row (the row that that the currently selected cell is in)
• Copy these cells
• Switch to the "ActualJobs" worksheet
• Switch off Autofilter (as if the list is filtered this may cause the code to incorrectly determine the bottom of the existing list
• Select cell A10001
• Go up column A until it finds some data and selects the cell below this
• Paste as values
• Switch Autofilter back on (assuming that the headings are in row 1)

Note that the code requires all rows that have data to have row A populated.

Once we have entered the code, we can select File, Close and return to Microsoft Excel.

Finally, we want to attach this code to a shortcut key. we can do this by clicking Macros again selecting our Macro from the list and choosing Options. Here we can enter the key that when pressed with the Ctrl key will activate the Macro. I chose J for job.

Then all we need to do to copy a recurring job to the ActualJobs sheet is click any cell in the row on the recurring job sheet and press Ctrl J.

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Excel Tip: Replacing parts of text strings

I have previously posted about using the Find and Replace facility to replace parts of text strings or formulae. But what if you want to be able to do this kind of thing automatically?

You're going to need to do it with formulae. The good news is that Excel has a couple of functions to do this.

Say you need to replace a section of a nominal ledger code (maybe a section the denotes a department or cost-centre).

If this part of the code will always be in the same place then we can use the REPLACE function.

The syntax for the REPLACE function is:

=REPLACE(OldText,StartNumber,NumberOfCharacters,NewText)

where,

OldText is the text we want to amend
StartNumber is the position in OldText at which we want to start replacing
NumberOfCharacters is how many characters of OldText we want to replace
NewText is the text we want to replace them with

So, say we wish to replace characters 4 to 6 of the string in A1 with the letters SAL, then we can use:

=REPLACE(A1,4,3,"SAL")

We may, however, not be able to rely on the department being in the same place. We can use the SUBSTITUTE function to replace a particular string with another.

The syntax of the SUBSTITUTE function is:

=SUBSTITUTE(Text,OldText,NewText,[Instance])

where,

Text is the text string that we want to amend
OldText is the text string (within Text) that we want to replace
NewText is the text string that we want to replace OldText with
Instance is an optional field that can be used to specify which instance (as a number) of OldText should be replaced. If this is omitted all instances of OldText are replaced with NewText

So, if in our earlier example we know the existing code has a department of ADM, then we can use:

=SUBSTITUTE(A1,"ADM","SAL")

This will replace every instance of ADM with SAL, so be careful.

We can use the Instance argument, if we know that it is always the first instance for example:

=SUBSTITUTE(A1,"ADM","SAL",1)


If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Excel Tip: Eliminating #DIV/0! and other errors - an update

A while back, I wrote a post explaining an approach to eliminating #DIV/0! and other errors in Excel.

Although the information in the original post is still valid, there is a more streamlined approach you can use in more recent versions of Excel (Excel 2007 onwards).

But before we get into that, a quick reminder that Mynda Treacy's Excel Dashboards course is closing shortly, so get yourself over there if you don't want to miss out!

Anyway, on with the post...

If you want to read the original post (out of curiosity or to stay compatible with Excel 2003 and earlier), you can do so here,

Where the original post used a combination of IF and ISERROR, the newer approach uses the IFERROR function.

The syntax for the IFERROR function is as follows:

=IFERROR(value,value if error)

The value argument can be any calculation that may result in an error message, and the value if error argument is what you want to replace the error with.

So, say you have a margin percentage calculation that divides Profit in cell B5 by Sales in B2. You might use the following formula formatted as a percentage:

=B5/B2

However if, in some instances, there might be zero sales, then this function will return the #DIV/0! error.

Instead of seeing #DIV/0! in this case, we can choose what should appear. We may prefer to show zero percent, or a dash, or nothing, or a message such as "No Sales". We can do any of these with the IFERROR function as follows:

=IFERROR(B5/B2,0)

=IFERROR(B5/B2,"-")

=IFERROR(B5/B2,"")

=IFERROR(B5/B2,"No Sales")




If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Excel Tip: Using an Override List to Tweak Imported Data

This week's post is not so much learning a new Excel function, as learning a concept that you can use to enable you to import data and correct it, without overwriting the imported data. This is important, so that you can refresh the imported data later!

Before we get into that though, just a quick reminder that the 20% discount offer on Mynda Treacy's Excel Dashboards course expires on Thursday, so if you're interested you need to get over there now.

Excel is a great tool for reporting on data held in an external database (this could be anything, such as an accounting system, ERP/MRP software or maybe a CRM system). However, sometimes we need the ability to edit or override the imported data with some tweaks that we wish to see included in the final reports.

In most cases, I would encourage any changes to be made in the external database, then the data to be refreshed, however this is not always possible. This can particularly be the case with transactional data (such as orders, invoices, etc.). Once an order is complete, most systems won't allow you to edit it, so if you need the order to be reflected differently in the final reports, this will need to be done in the spreadsheet.

You could over-type the imported data to make the changes, but this would be overwritten next time you refreshed the link to the external data. Far better to hold the changes in the spreadsheet to be applied to the data as it comes in. This is where I use what I call an override list.

As long as the imported data has a unique reference that you can link the adjustments to, you can use this approach. Simply have a separate sheet in the spreadsheet where you enter the references of any records that you want amended alongside the amendments. You can look up these amendments  in calculated columns alongside the data.

Say that you are importing a list of sales orders but that they are not always allocated to the correct salesperson in the external database. You could have an override list with the following two columns:

• Sales Order No
• Salesperson

Then for any orders that you needed to correct, you could type that order number and the salesperson that you wish to be allocated to that order.

Then all you need is a calculated column alongside the imported data that uses the new value if the Sales Order in question is on the list or the original salesperson if not.

Assuming that the imported data is in a table where two of the headings are Sales Order No and Salesperson, and the override list as described above is in the first two columns of a sheet called Override, then the following formula will work in the calculated column (which we might call CalcSalesperson:

=IF(COUNTIF(Override!$A:$A,[@[Sales Order No]])=0,[@Salesperson],VLOOKUP([@[Sales Order No]],Override!$A:$B,2,FALSE))

This uses an IF statement to check whether this row's Sales Order No appears in the first column of the Override sheet (The COUNTIF counts the occurrences of the Sales Order No in that column). If this returns zero, then the Salesperson field is used, otherwise the amended Salesperson is looked up (using VLOOKUP) from the override sheet.

Any references to the data (in PivotTables or SUMIF formulae for example) can now refer to CalcSalesperson, rather than Salesperson.

The specific formula will vary depending on what you are trying to adjust, but the principle remains the same - and the changes will be applied every time that particular Sales Order No appears in the imported data.


If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Excel Tip: Using the HYPERLINK function

This week, I want to introduce you to the HYPERLINK function.

This function allows you to add hyperlinks to your spreadsheet for both navigation, and to link to external websites or files.

And, because it is an Excel function, you can use cell contents elsewhere in the spreadsheet to determine the hyperlink.

The syntax of the HYPERLINK function is as follows:

=HYPERLINK(Link,[Friendly Name])

Link can be a URL or a file reference. So, some possible examples are:

"http://www.notjustnumbers.co.uk"
"C:\My Documents\SalesSpreadsheet.xlsx"
"[C:\My Documents\SalesSpreadsheet.xlsx]Consultancy!A1" (note that this one will go to cell A1 of the Consultancy tab of SalesSpreadsheet.xlsx)

Friendly Name is optional and is the text that you want to use as the hyperlink. So, for example:

=HYPERLINK("[C:\My Documents\SalesSpreadsheet.xlsx]Consultancy!A1","Consultancy Sales")

will show a clickable hyperlink that says Consultancy Sales - and clicking on it will take you to cell A1 of the Consultancy tab of SalesSpreadsheet.xlsx.

If this optional argument is not entered, the Link itself will appear as the hyperlink.

The function really comes into its own though when we use other cell addresses in the arguments. If you have two columns of data being company name and website address, for example, then you could have a third calculated as

=HYPERLINK(B1,A1)

which will show the company name (from column B) as a clickable hyperlink that will take you to the company website (from column A).

Or you could use text functions such as the ampersand to create the links. For example you could have a list of sales transactions and the documents stored as Word documents with the invoice number as the file name.

Say column A contains the invoice number and the directory the invoice documents are in is C:\Invoices\, The following HYPERLINK formula will give you a clickable version of the invoice number that takes you to the document:

=HYPERLINK("C:\Invoices\"&A1&".docx",A1)

If A1 contains the invoice number 324, then the function will return a clickable 324 that takes you to the document C:\Invoices\324.docx.


Before I go, I have some great news. Mynda Treacy's Excel Dashboards course is once again available - for a limited time.

Order before 16th October 2014 to get 20% off and, as a special offer from me, enter the code FEECHAN when you order and I will send you my Introduction to Pivot Tables course absolutely free.

Click here to find out more about the Excel Dashboards course and to take advantage of this offer.


If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Excel Tip: What's the difference between Delete and Clear?

There was a great response to my Filtered Excel Training offer last week. If you missed it you can still sign up to get 53 Online Excel Training modules absolutely free.

Also, if you are looking for an Excel training solution across your business, take a look at Filtered's business offering and receive 20% discount as a Not Just Numbers reader.

On with today's post. Whether we have made a mistake or are tidying up a spreadsheet, there are many occasions where we wish to delete things that we, or someone else, has already done.

Excel offers a few ways to do this - and they're not all the same. Do you know what each of them do? If not, read on.

The most common method of deleting in Excel is to use the Delete key on your keyboard. This will delete the contents of the selected cell or cells, however it will leave any formatting intact. This makes it very useful for correcting incorrect data input, but not ideal when you are tidying up a spreadsheet and you don't want any old formats hanging around.

The next option (also called delete) is when you select Delete from the right-click menu or the Home ribbon. This option deletes the actual cell itself, giving you the option to move cells left or up, or to delete the entire row or column. This is quite different to the delete key as this is changing the structure of the spreadsheet.

Finally, there is the Clear option. This is available from the Home ribbon and offers the following options:

• Clear All
• Clear Formats
• Clear Contents
• Clear Comments
• Clear Hyperlinks

These options are pretty self-explanatory and "do what it says on the tin", but it is worth commenting on the Clear All, as this is very useful when cleaning up a spreadsheet as it gets rid of any traces of what was there before.

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Online Excel Training - Not Just Numbers readers can get 53 modules from Filtered

We've got a slightly different post this week. I want to tell you about a new exciting partnership I have agreed with Filtered, providers of excellent on-line training in Excel and Office.

And to cement this partnership, Filtered have agreed to provide Not Just Numbers readers with the first two sections of their Excel course completely free. That's 53 modules giving you a very decent grounding in Excel - absolutely FREE!

To take up this offer, just click here (all you need to do to set up your account is enter your name and email address and to choose a password).

To ensure that you get the most out of your training in as little time as possible, Filtered provide you with a unique test which is a combination of questions about your own perception of your Excel aptitude and actual questions about Excel. This then tailors the training to suit your specific needs - no need to trawl through all the stuff you already know to get to the useful bits.

Even without this offer, I would recommend Filtered as a great way to plug the gaps in your Excel knowledge, but getting so much of it free, in my opinion, makes it a no-brainer!

So, don't forget to sign up for your free account before you leave this page.


If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Excel Tip: Knowing where you are in your spreadsheet, using the ROW and COLUMN commands

Sometimes in your spreadsheet, like in life, it is good to get your bearings to establish where you are.

This can be very useful in a formula if you want the result to be dependent on the location in the spreadsheet.

For example, you might want to:

• format odd and even rows (or columns) differently, or
• apply a different formula depending on how far down a list an item appears, or
• populate cells with a particular value, formula or format based upon  an entered width and height.

To do any of these types of things with a formula, the formula will have to know its own location (i.e. its row and column) first.

The ROW and COLUMN commands do exactly that.

The syntax of the ROW command is:

=ROW([Reference])

where Reference is the cell reference that you wish to return the row of. So,

=ROW(A5)

for example, will return 5.

But notice that Reference is in square brackets which, you may remember form earlier posts, means that it is an optional argument. So, the function can be written as just:

=ROW()

This will return the number of the row in which the formula sits, i.e. giving us the row information that we would need for all of our examples above.

Typically we will use it in an IF statement to drive the different outcomes in our examples, for example:

=IF(ROW()<=10,1,2)

will return a 1 in rows 1 to 10 and a 2 thereafter.

The COLUMN function works exactly the same way so:

=COLUMN(A5)

returns 1 (the column is returned as a number not a letter, so A is 1, B is 2, etc. - this does make it easier for adding and subtracting column positions). Also,

=COLUMN()

returns the column number of the column in which the formula sits.

One word of caution. You need to remember that you have used these functions if you start inserting or deleting rows and columns. Most functions will adjust accordingly and maintain the same relative references, but because these refer to the actual cell positions, their results will change if their position changes.

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Excel Tip: SUBTOTAL and SUM - what's the difference?

I'm sure most of you are familiar with the SUM command for totalling ranges of cells, but have you heard of the SUBTOTAL command? - and if so, do you know what it does?

I must admit that I had only looked at the SUBTOTAL command in any detail this week - prompted by a question from a reader. I had never really used it, because I normally advocate doing any analysis of a data list separately using PivotTables, rather than within the list itself and didn't realise that it offered some interesting differences to just using the SUM command.

At first glance it would appear to be the same, but it has a number of additional powers!

I am aware that most of you will know this next bit, but I have included it for completeness:

The SUM command is used as follows:

=SUM(number1,[number2],....)

only number1 is required and should be a number, or a reference to a number or range of numbers.

So, number1 could be, for example, 3, A4, A1:A4 or a named range.

The same rules apply to number2 and number3 all the way up to number255, except these are all optional.

The SUM function then returns the total of all of these numbers.

The SUBTOTAL command has very similar arguments except it has one additional argument in front of them:

=SUBTOTAL(function_num,ref1,[ref2],....)

ref1 and the optional arguments ref2 to ref254 are very similar to number1, etc. in the SUM function, however they must be references to cells or ranges of cells - i.e. not actual numbers.

The function_num argument must be between 1 and 11 or between 101 and 111. This argument determines how the function is to summarise the numbers. The Excel function that will be applied for each of function_num 1 to 11 is given below:

1 AVERAGE
2 COUNT
3 COUNTA
4 MAX
5 MIN
6 PRODUCT
7 STDEV
8 STDEVP
9 SUM
10 VAR
11 VARP

This is the first real difference between SUM and SUBTOTAL. SUBTOTAL can mimic any of these 11 functions.

The second difference is more subtle. You would expect the following two functions to return the same result:

=SUM(A1:10)
=SUBTOTAL(9,A1:10)

and in most cases they will. The difference becomes apparent when you apply a filter to the data. This will have no effect on the SUM result but the SUBTOTAL will exclude any rows hidden by the filter from the calculation. This could be very useful if you regularly work with a data table that you filter in place using the AutoFilter facility, as it will show you the total of the displayed rows.

You can further affect the result by using function_num 101 to 111. These work exactly the same as 1 to 11 but exclude rows hidden using the Hide command as well.

One final difference is that the SUBTOTAL command will ignore any other SUBTOTAL commands in the range being summed, thereby avoiding double-counting. Thanks to Jeremy for pointing out that I'd missed that one!

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Excel Tip - counting workdays allowing for holidays

Please accept my apologies for the lack of posts these last couple of weeks - I've been away on holiday, chilling out on the Isle of Skye (that's me in the picture relaxing on the coral beach near Claigan).

I had intended to write some posts in advance but it didn't exactly work out as planned!

Now I'm back, I thought I'd write a (vaguely) holiday related post.

If you want to know how to work out the number of working days between two dates (allowing for weekends AND holidays), read on.

To do this, we can use the NETWORKDAYS function.

The syntax for this function is:

=NETWORKDAYS(StartDate,EndDate,[Holidays])

I would not recommend entering the dates directly into the function. This is not only good advice for making this particular function work, but it is best practice when referring to any variable in Excel - enter its value in a cell and refer to the cell.

Ignoring the optional argument, if A1 contains 1/8/2014 and A2 contains 31/8/2014, then

=NETWORKDAYS(A1,A2)

returns 21, being the number of working days in August 2014 (if you ignore public holidays).

The best way to use the optional third argument is to refer to a range, where you can enter holiday dates. So, say we add the third argument as follows:

=NETWORKDAYS(A1,A2,B1:B20)

We can now enter the dates of any holidays in cells B1 to B20 and these will be excluded from the calculation.

So, in the UK, last Monday was our August Bank Holiday, so if we enter 25/8/2014 into any of the cells from B1 to B20, the formula returns 20.

A practical way of using this functionality would be to enter the whole year's holidays into the range referred to (B1:B20 in our example), any of those dates that fall between the start and end dates would then be excluded from the calculation.




If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Excel Tip: Add a simple tick box to your spreadsheet - and use its result!

Do you ever wonder how you get those professional looking tick boxes (or check boxes) in your spreadsheet?

They're easier to add than you think, and it's pretty straight-forward to have calculations depend on the result of the tick box.

In this post, I'll show you how to do both.

Before you can add a tick box, you need the Developer Ribbon enabled. If you don't see it as one of the named Ribbons at the top of Excel, then you can add it by selecting File - Options - Customize Ribbon and then ticking Developer in the list of Main Tabs on the right-hand side.

To insert a tick box, go to the Developer ribbon and click Insert in the drop-down menu that appears, click the Check Box (Form Control) icon. This is the tick box under the heading Form Controls - if you hover over it you will see the name "Check Box (Form Control)" appear.

Your cursor will become a cross and you can now click where you want the tick box to appear.

The box will appear with some default text to the right of it (this will usually be Check Box 1 if this is the first one that you have inserted into the spreadsheet. This text can be edited (or deleted) by double-clicking on the text and editing or deleting as required.

This is your tick box created and you can now click it to toggle between ticked and unticked.

However, I did say that I would show you how to use the result of the tick box.

If you right-click on the tick box you will see the menu option "Format Control". Click on this and go to the Control tab.

You will see a box entitled Cell Link. Enter a cell reference here, where you wish to store the result of the tick box (let's say C3). Now when you click the tick box, cell C3 will switch to show TRUE, and when you untick the tick box, C3 will show FALSE.

You can then use cell C3 in a formula. The most common way to use it would be as the criteria argument of an IF function. As C3 contains the logical value TRUE or FALSE, then this is all you need as your criteria argument, so:

=IF(C3,100,0)

will return the value 100 if the tick box is ticked, or 0 if not.

It's as simple as that!



If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Excel Tip: Identifying your financial period from a date

When working with financial transactions, we often want to analyse them between our financial periods rather than just calendar months.

This is pretty easy if your financial year is January to December and  your periods are the calendar months, but what if your year starts in April or you have four and five week periods - or both?

I'll explain how to address each of these scenarios in this post.

In each case, let us assume the date we want to identify the period for is held in cell A2.

Calendar Year with Calendar Months
This is by far the simplest scenario as we can use the MONTH function to strip the month number from the date, so =MONTH(A2) will do the trick, returning 1 for January, 2 for February, etc.

Non-Calendar Year with Calendar Months
This situation is slightly more complicated but we can use our knowledge of the IF function to address it, along with the MONTH function used above.

Let's say our year end is March So the calendar month 4, is our period 1. So deducting 3 from the month will give us our period, however this will only work from April onwards. If our date is in February and we deduct 3 from the month, we will have -1, rather 11, which is what we require. This is where the IF statement comes in.

If the month is greater than 3, then we want to deduct 3, otherwise we want to add 9 (which is 12 -3).

So, our formula becomes:

=IF(MONTH(A2)>3,MONTH(A2)-3,MONTH(A2)+9)

The 3 is the month of our year-end and the 9 is 12 minus the month of our year-end, so if our year ended in October (month 10), then the formula would be:

=IF(MONTH(A2)>10,MONTH(A2)-10,MONTH(A2)+2)

Non-Calendar Year with Non-Calendar Periods
For this situation, we need to take a different approach. We don't have a simple rule for the periods, so we will need to tell the spreadsheet them. We can do this with a LOOKUP table.

We should have a two-column table with the first column for the  start date of each period (earliest to latest), and the second column for the period number.

We can then use VLOOKUP with a TRUE (or omitted) fourth argument to look up the correct period.

Say the lookup table is in cells D2:E13 (when doing this for real, I would recommend putting this on a separate tab), then our formula would be:

=VLOOKUP(A2,$D$2:$E$13,2) will return the period number.

I have applied the dollar signs to the range so that it stays fixed when copied to other rows.

This last approach is obviously the most flexible and can be used for many other scenarios, e.g. identifying VAT quarters for example.




If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

What would be in your Excel Survival Kit?

This weekend, I went on a bushcraft training weekend with my fifteen year-old son, building our own camp in the woods, etc.

This was an amazing experience and is still fresh in my mind as I write this blog post. So, it got me thinking, "In the Excel jungle, what would I have in my survival kit?"

I thought I'd ask myself the following question (and I'd love to hear your answers too, in the comments) - If I could only have three functions/features in Excel, what would they be? I am going to assume that basic mathematical functions like adding and subtracting and SUM are there, but what are the top three additional features that I wouldn't want to be without?

So, here are my top three (in no particular order):

1. VLOOKUP - Having Excel look up information from tables elsewhere in the spreadsheet is an incredibly useful facility. I did, however, think for a while about this as I would have preferred INDEX and MATCH, but that would have taken up two of my choices!
2. PivotTables - I use them all of the time and they remove the need for formulae in many cases.
3. The IF statement - being able to get Excel to essentially make decisions based upon the information it has available is too useful a feature to give up!

Please let me know yours in the comments.

By the way, although you've missed the discount, you can still get Mynda Treacy's Excel Dashboards course until it is taken down on Thursday night.

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Excel Tip: Quickly name and access ranges

A quick reminder before we start, that the 20% off deal on Mynda Treacy's Excel Dashboards course expires on Thursday. So, if you want to take advantage, hop on over there now while you've still got the chance.

This week, I've got a simple tip to quickly name and access ranges in Excel.

You can use the Name Manager on the Formulas ribbon to define range names, however I will show you a much quicker way to name fixed ranges, as well as go to them and highlight them once they exist.

This tip uses the Name Box at the top left of your screen, just below the ribbon to the left of the formula bar.

This box normally shows the cell reference of the active cell (the cell that is currently selected). If you click in cell B6, for example, you will see the name box shows "B6".

While you are in cell B6, click in the Name Box and type the name "Fred". Click in another cell and the name box will change to the cell reference of the cell you have clicked in. Now click back on cell B6 and notice what happens. The Name Box says "Fred"! You have successfully renamed B6, Fred!

You can do the same thing with a range of cells. If you highlight the range C5:E10, for example, and type "Bill" in the Name Box, click in another cell and then re-highlight the range C5:E10, the Name Box will show "Bill" again.

Note that these names follow the same rules for named ranges named in any other way - most notably, they should not include any spaces.

What's more, if you click the dropdown arrow next to the Name Box, you will see Bill and Fred are both listed there. If you select one of them from the list, you will go to that range and highlight it. This works from any of the other worksheets in the workbook too.

You might not find too many reasons to name cells Bill or Fred, but if B6 was renamed TaxRate, then that might be useful. You could use the name TaxRate in any formula (instead of B6) and if you need to check what the TaxRate is, or change it, you can go to that cell by selecting it from the dropdown in the Name Box.



If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Excel Tip: Find a code in among other text

I've got a little bit of news before we get into this week's post.

Regular readers of the blog will know that I am a big fan of Mynda Treacy's Excel Dashboards course. Well, Mynda is making it available once again, for a limited period. You can get it here - and if you sign up by the 24th July, you will not only get a 20% discount, but I will give you a copy of my Introduction to Excel Pivot Tables video course, absolutely free.

So, if you've missed out before, click over there now, before you miss out again.

OK. On with this week's post.

Have you ever had a problem where account codes, or suchlike, are embedded in larger sections of text, when you need them in a field of their own (for lookups or pivot tables for example)?

Well, here's a little trick that will work in certain circumstances.

I had an issue yesterday with a client who needed to analyse transactions by project code, however the project code did not have it's own field in the system and was entered, along with a number of other pieces of information in the description field. What's more, the code could appear anywhere within the description field.

The key to making this work, is being able to identify something within the code that will not appear elsewhere in the text. In my client's example, all project codes started with PROJ.

We can use the FIND function for this:

=FIND(find_text,within_text,[start_num])

The FIND function returns the position of find_text within the larger text, within_text. The optional argument, start_num, allows you to specify at what position to start looking (if this is argument is not entered, the FIND function will start at the beginning of within_text).

Say the cell A1 contains the following text:

"Expenses for PROJ13245 but that is not the only text here"

the function

=FIND("PROJ",A1)

will return 14, the position in the text of the first letter of PROJ.

If the project code is always 9 digits, then we can use the MID function with FIND to pull it out.

The MID function is structured thus:

=MID(text,start_num,num_chars)

This returns the num_chars of text from text, starting at start_num, so:

=MID(A1,14,9)

would return our project code, if we already knew that it started at position 14, however we can replace the 14 with our FIND function, to calculate the start of the Project Code, so:

=MID(A1,FIND("PROJ",A1),9)

will pull out the 9 digit project code from the text.

But what if the length of the code was variable, well, if we can assume that it will always be followed by a space, for example, we can use find again to identify the end, and this calculate num_chars.

We can find the position of the space at the end of the project code, by using the following FIND function:

=FIND(" ",A1,FIND("PROJ",A1))

All we have done is used our earlier calculation of the start of the project code, FIND("PROJ",A1), as the start_num value in a new FIND looking for the space. This will return the position of the first space following the letters PROJ.

To calculate the num_chars we need for our MID function, we just need to deduct the start position, i.e. FIND("PROJ",A1). So,

=FIND(" ",A1,FIND("PROJ",A1))-FIND("PROJ",A1)

will return the length of the project code to replace the 9 in our MID function:

=MID(A1,FIND("PROJ",A1),FIND(" ",A1,FIND("PROJ",A1))-FIND("PROJ",A1))

which will return the project code, PROJ13245.

And that's it. It obviously won't work in every situation, but there are plenty where it, or a version of it, will.

Good luck!



If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

ICAEW Twenty Principles for Good Spreadsheet Practice Launch Highlights (Video)

For those of you who couldn't make it to the launch of the ICAEW's Twenty Principles for Good Spreadsheet Practice, I thought I'd share with you a highlights video that the ICAEW have made of the event.

It will be like you were there. You can even download the Principles document itself, and I would highly recommend that you do, as it is a great starting point for checking (and influencing) the quality and integrity of spreadsheets within your business.

Here's the video:

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Twenty Principles for Good Spreadsheet Practice - get your copy

In earlier posts you've heard me talking about the ICAEW's Twenty Principles for Good Spreadsheet Practice.

This is a set of principles that, if you apply them, should significantly reduce the problem of spreadsheet risk, as well as make your spreadsheets far more robust and efficient.

The principles are now officially launched (to an audience of around 200 people at an event at Chartered Accountants' Hall in London on June 17th) and free to download (I'll give you the link at the end of this post).

The principles themselves are the work of the ICAEW IT Faculty Excel Community Advisory Committee, of which I am privileged to be a member.

The principles in brief are:

1. Determine what role spreadsheets play in your business, and plan your spreadsheet standards and processes accordingly.
2. Adopt a standard for your organisation and stick to it.
3. Ensure that everyone involved in the creation or use of spreadsheets has an appropriate level of know¬ledge and competence.
4. Work collaboratively, share ownership, peer review.
5. Before starting, satisfy yourself that a spreadsheet is the appropriate tool for the job.
6. Identify the audience. If a spreadsheet is intended to be understood and used by others, the design should facilitate this.
7. Include an ‘About’ or ‘Welcome’ sheet to document the spreadsheet.
8. Design for longevity.
9. Focus on the required outputs.
10. Separate and clearly identify inputs, workings and outputs.
11. Be consistent in structure.
12. Be consistent in the use of formulae.
13. Keep formulae short and simple.
14. Never embed in a formula anything that might change or need to be changed.
15. Perform a calculation once and then refer back to that calculation.
16. Avoid using advanced features where simpler features could achieve the same result.
17. Have a system of backup and version control, which should be applied consistently within an organisation.
18. Rigorously test the workbook.
19. Build in checks, controls and alerts from the outset and during the course of spreadsheet design.
20. Protect parts of the workbook that are not supposed to be changed by users.

If you click here, you can read more about the committee and the background to the principles but, more importantly, you can download the Principles document itself, including examples and advice on application.

Just follow the link, and click on the link "Twenty principles for good spreadsheet practice" in the first sentence.

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Excel Tip: Copy and Paste - avoiding unwanted baggage

First of all, it was great to see those of you that made it to last week's launch event for the ICAEW's Twenty Principles for Good Spreadsheet Practice. I hope to have a video of some of the highlights of the event to show you soon.

This week, I want to highlight an approach to copying and pasting that means you only bring across what you want, and don't bring across any nasty surprises for later.

Have you ever opened a spreadsheet and been asked if you want to update links that you didn't think were there?

...or ended up with a selection of different font sizes and formats in a range of data?

These are both signs of unwanted baggage dragged across during a copy and paste exercise, but can be easily avoided.

If you simply copy and paste, this brings across all formats and formulae from the source cells along with the contents, causing the two problems highlighted above.

Where the source cells have formulae that make reference to cells on other tabs of the source workbook, the pasted formulae will include external links back to the source spreadsheet. This causes the prompts about external links mentioned above. And, unless this is what was intended, a far greater problem is that the content of these cells will now update with changes to the source spreadsheet!

The multiple formats in a spreadsheet tend to come from pasting data from various sources with different formats.

Both can be easily avoided by some of the Paste Special options.

You can choose to bring over just the contents of the cells (no formats or formulae) by selecting to Paste Values , indicated by the clipboard showing the numbers 123. Note that the results of formulae are copied across as if they had been entered in the cell.

If you do want the formulae to be copied across, but not the formats, you can select Paste Formulas (the clipboard with fx on it).

You can also copy just the formats (leaving existing cell contents intact) by selecting Paste Formats (the clipboard with  a percentage sign and a paintbrush).

Finally, if you want to copy the cell contents and formats (but leave the formulae behind), you can select Paste Values and then Paste Formats.

There, you can now travel light when you're copying and pasting!



If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Excel Tip: Pump up the volume on your data entry screens with a simple slider control

Sometimes you have a spreadsheet that you want to look good and feel interactive, but might only have a few inputs.

A good example of this might be a loan illustration. You might want to experiment with different loan amounts, and terms for example.

You can of course just have an input cell for each of the variables (loan amount and term for example). But if you want to really jazz up the spreadsheet, what about interactive slider controls to enter the values, with the illustration responding to your movement of the sliders?

This is much easier than it sounds.

First of all, you will need to check that you have the Developer ribbon showing. If you do not have a ribbon called Developer, you can add it by clicking File, Options, Customize Ribbon and ticking Developer in the right hand column (Main Tabs).

Now go to the Developer Ribbon and click Insert (it has a picture of a toolbox above it) and you will see the following options:

Select the option I have circled above and the cursor becomes a cross-hair for you to draw the rectangle that will become your slider.

Once you have drawn it, right-click on it and select Format Control.

The screen above has all of the settings you need to make your slider work. The settings above could be for selection of Loan Amount in the example earlier. I will go through each in turn:

Current value - This is simply the value you want the slider to be set at before it is moved
Minimum value - The value when the slider is at the extreme left
Maximum value - The value when the slider is at the extreme right
Incremental change - How much you want the value to increase/decrease by when you click the arrow at either end
Page change - How much you want the value to increase/decrease by when you click the space between the selector and the arrow at either end
Cell link - The cell that you want the slider to link to. This will be the Loan Amount variable in the example described. In this case cell C7 will  show the value selected on the slider, and if you edit cell C7, the slider will move.

Have a play and see what you think.



If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".