Search Not Just Numbers


Tuesday, 9 February 2016

Excel Tip: Controlling what is printed from a sheet

One of the challenges that many Excel users face, is ensuring that the printed version of a spreadsheet looks as good as it did on the screen.

There are a few simple things that you need to know to help to organise what actually gets printed and how it is spread across pages.

I will cover the first of these today, and revisit some of the others in future posts.

Today we will look at how to determine how much of the sheet is actually printed.

Left to its own devices, Excel will print all of the used area on the sheet, Specifically, it will print the whole area from cell A1 to the rightmost column and the last row that are not empty.

This may not be what is required, and it is easy to change. Simply highlight the area that you do want to print, then go to the Page Layout ribbon, click the Print Area icon, and select Set Print Area.

You can even select multiple ranges (holding down the Control key, while you select the subsequent ranges) which Excel will print on different pages.

For example, you may have a Profit & Loss and Balance Sheet alongside each other on one worksheet. Just highlight the area of the Profit and Loss (say A1:H100) and then hold down the Control key while selecting the Balance Sheet range (say J1:P80) and Set Print Area as described above.

Once a Print Area has been set in this way, you can see (and edit it) using the Name Manager on the Formulas ribbon. You will see all of the Print Areas in the workbook listed there and (scope to their particular worksheet). You can then edit these ranges like any other.

You can even make them a dynamic range using functions like OFFSET and INDEX, so that the area that gets printed is determined by values in the workbook.

A particularly useful technique is to combine this ability to set a dynamic Print Area with a formula to determine the last row of data. I explained a formula to do this in this earlier post.

Let's say we put this formula in a cell that we name LastRow.

If we want the print area to be columns A to J but the height of the print area to flex to the amount of rows occupied, we can enter the following as the Print Area (in the Names Manager):


This will print a range starting at A1, with a height of LastRow, and a width of 10 (A to J is 10 columns).

Free Excel Dashboard Webinar
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".

Tuesday, 12 January 2016

Excel Tip: An easier way to change cell colours

Before I begin, I would like to wish a Happy New Year to all of my readers and subscribers - as this is my first post of 2016.

I am starting the year in humble mode, as I have had something very simple pointed out to me that I can't believe I had never spotted, despite it being right under my nose!

In my last post of 2015, I showed you some code to swap one cell colour for another throughout a worksheet. I then received a comment from a reader that I only know as Jim, pointing out that Excel already has a very simple way of doing this!

It can in fact be done using Find and Replace.

This amazed me because I use Find and Replace all of the time and didn't know you could do this!

It is simple as this:

  1. Highlight the range which you want to apply the changes to
  2. Click Find and Select (the binoculars icon) on the Home Ribbon and choose Replace from the dropdown menu
  3. Don't enter anything in the Find or Replace boxes, but click the Format button next to the Find box and on the Fill Tab, select the colour that you wish to change
  4. Click the Format box next to the Replace box and on the Fill Tab, select the new colour that you wish to swap for the colour you selected above
  5. Click Replace All
You can even start with the colour of an existing cell, using the dropdown on the format button, however be sure to remove any other formats other than Fill colour.

The thing that really shocked me is that these two big Format buttons have been right there in front of me every time that I've used Find and Replace since Excel 2003! And that's a lot of times!

In 2016, I am going to try to make sure that I constantly keep my eyes open for new ways to do things in Excel, instead of just relying on what I think I already know! And thanks again, Jim.

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

Tuesday, 22 December 2015

Excel Tip: Merry Christmas with a quick macro to swap one colour for another

First of all I would like to wish all of my readers and subscribers, a very Merry Christmas and a Happy 2016.

I know we all work hard, and I hope, like me, you all get a chance to spend some time with those you love in the coming days, whether Christmas is a festival you celebrate or not.

One hassle I sometimes have when building spreadsheets for others is changing colours.

The spreadsheet may work perfectly, but the end user might not like the colours I have chosen. This isn't too much of a problem if we are talking about large blocks of colour, but it can be a time-taking process, if I have used yellow to denote input cells throughout the whole worksheet, and the client wants orange, for example!

When this happened recently on a particularly complex spreadsheet, I decided to write a little macro to help, which turned out to be pretty straightforward and does the job in seconds.

Here is the code of the macro.
Sub ColourSwap() 
Dim Source As Variant
Dim R As Integer
Dim G As Integer
Dim B As Integer
Dim NewColour As Variant
Source = ActiveCell.Interior.Color
If MsgBox("Switch to no colour?", vbYesNo) = vbYes Then
NewColour = 0
For Each cell In ActiveSheet.Cells
If cell.Interior.Color = Source Then cell.Interior.ColorIndex = NewColour
R = InputBox("R?")
G = InputBox("G?")
B = InputBox("B?")
NewColour = RGB(R, G, B)
For Each cell In ActiveSheet.UsedRange.Cells
If cell.Interior.Color = Source Then cell.Interior.Color = NewColour
End If 
End Sub
The easiest way to include it in your spreadsheet is to hit Alt-F8 and type the name you want to call the macro in the box at the top of the dialog. I have called it ColourSwap. Click create and you will see the green sections above already showing in the window (with your chosen name replacing ColourSwap). Just paste the yellow section in between.

You can run the macro by hitting Alt-F8 again, selecting it and choosing Run. If you prefer, you can click options instead of Run, and assign a shortcut key to run it in future.

The macro works as follows, and is applied to a single worksheet at a time.

  1. Click on one of the cells that contain the colour that you wish to swap;
  2. Run the Macro
  3. You will be asked if you wish to "Switch to no colour?", if you select "Yes", then all of the cells on that worksheet that are the same colour as the cell you selected, will have any Fill Colour removed. This is the same as choosing No Fill if you were colouring the cell manually.
  4. If you select "No", you will be faced with 3 prompts, requesting the R, G and B values for the colour you wish to swap to. Once you enter these, all of the cells on the worksheet that are the same colour as the cell you selected, will be filled with this new colour.
NB: The macro only checks the Used Range of the worksheet, so will not change any coloured cells below, or to the right of the last populated cell on the worksheet. This is to save time, as it can take quite a while to loop through every cell, when this is not necessary. If (for some reason) you have coloured cells outside of the Used Range, then these should be very easy to change manually, as they will almost certainly be in a large block.

Note that if you wish to save the Macro with the spreadsheet, you will need to save the Workbook as Macro-enabled Workbook, however this will usually be unnecessary, as once the colours have been changed, the macro is no longer needed, so that you can let it save without the macro.

I hope you find this useful, and could maybe use it to make some tired old spreadsheets look a bit more festive!

Merry Christmas!

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

Tuesday, 1 December 2015

Excel Tip: How many calendar months does a date range affect?

This week I responded to a client request for a formula that seemed straight-forward, but it took a few emails backwards and forwards to establish exactly what was required.

My answers at each stage, provide a number of alternative versions of what appear to be the same thing - but aren't!

The question itself was essentially "How do I calculate the number of months between two dates?"

My first response to this question used the DATEDIF function as featured in this earlier post.

Assuming that the start date is in cell A1 and the end date is in cell A2, then:


will return the number of WHOLE months between the two dates.

My client then said that they wanted to always round up the number of months.

I then used the "MD" argument of a DATEDIF to identify the remaining days after calculating the whole months, so that I could use and IF statement to add 1 if this remainder was greater than zero:


Finally, it transpired that what was really required was the number of calendar months touched by the date range, e.g. if the start date was 31st January 2015 and the end date was 1st February 2015, the answer should be 2, as both January and February feature in the date range (with the same dates, our first example would return zero, as there are no whole months and our second would have returned 1, as we rounded the 2 days up to a whole month).

This required a completely different approach, by using the month function to pull out the month from each date, and the year function to pull out the year. The answer would then be the difference between the month numbers, plus 1, plus 12 * the difference in the years, i.e.


Depending upon your specific needs, any one of these formulae might be correct for your requirement!

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