Search Not Just Numbers

Tuesday 27 January 2015

Excel Tip: Keep an eye on multiple cells

Do you ever have those situations when you are trying different scenarios and constantly checking to see what effect your twiddling is having on your results?

Wouldn't it be great if you could keep an eye on all of the cells you were interested in, without having to scroll all over the place?

Well, Excel has a nice simple tool to allow you to do exactly that.

The Watch Window tool is accessed from the Formulas ribbon, over to the right hand side of the ribbon.

When you click on the tool, it opens the Watch Window which hovers over the spreadsheet like any other toolbar.

Once the Watch Window is open you can add the cells that you want to keep your eye on by clicking Add Watch... and selecting the cells, either one by one or by selecting a whole range of cells. If you do this, all of the individual selected cells will be listed in the Watch Window. These cells can be on different worksheets and even in different workbooks.

For each cell, the Watch Window lists the Workbook, Worksheet, Name (if you have given the cell a name range), Cell reference, Value and Formula.

As you update the spreadsheet, the Watch Window will provide a live update of the effect on those cells. No more scrolling around!

Before I go, don't forget that Mynda Treacy's Excel Dashboards course is closing on Thursday, so get over there now if you don't want to miss out.

Excel Dasboard Course

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 20 January 2015

Excel Tip: Have your buttons stopped working?

Today's post refers to a problem that started occurring in early December but I have had a number of clients raise the problem in recent days, as I think the Christmas break has probably meant the problem wasn't noticed by many.

As a result, I thought it was probably still worth letting readers know about it in case they were experiencing it for the first time, or just had not managed to correct it yet.

The most common symptom of this problem is any ActiveX buttons in your spreadsheets ceasing to work. If this has happened to you and you haven't yet found a solution, read on.

The problem stems from a Microsoft security update on 9th December and applies to the 2007, 2010 and 2013 versions of Office. If you have automatic updates switched on, you will have had this update.

The update has caused problems for ActiveX controls and the most visible problem is that existing ActiveX buttons, which you may have in your spreadsheet to run macros for example, just do nothing when you try to click on them. Note that Form Control buttons are unaffected.

Full instructions from Microsoft to deal with the problem are available here:

http://support.microsoft.com/kb/3025036/EN-US

In most cases, the following will work:

In File Explorer, search for all files ending in .exd, then delete these files (they are temporary files anyway and new ones will be created next time you use  the ActiveX controls).

At the Microsoft link above there is also a script that you can run to do this if you prefer.

Before you go, just a quick reminder that the 20% discount offer on Mynda Treacy's Excel Dashboards course runs out on Thursday, so enrol now if you don't want to miss out.


Excel Dasboard Course

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 13 January 2015

Excel Tip: Enter the same data in multiple cells at once

Before I get into this week's post, I'd like to let you know that Mynda Treacy's very popular Excel Dashboards course is once again open for enrolment. If you haven't already taken this course, I'd highly recommend it, and you can get 20% off if you enrol by 22nd January. I'll also throw in my Introduction to Excel PivotTables course free of charge, just enter FEECHAN in the Coupon Code field in the Cart or Checkout.

Anyway, on with this week's post. Just a quick one this time.

In the comments of last week's post, Bill Simpson of Texas mentioned a simple little tip that I hadn't seen before. We're all still learning!

I thought I'd share it with you all too.

If you want to enter the same thing into multiple cells, you can enter it into one and copy it into each of the other cells, however a quicker solution allows you to enter the same thing into all of those cells at once.

Just take the following steps:

  • Highlight all of the relevant cells (you can use the mouse to highlight a range, or click on each of the individual cells while holding down the Ctrl key)
  • While they are all highlighted, type what you want to
  • Instead of hitting Enter, hit Ctrl + Enter, the information will appear in all of the selected cells.
That's it. Don't forget to take a look at Mynda's course while you can get 20% off.


Excel Dasboard Course

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 6 January 2015

Excel Tip: The Greatest Excel Tip of All Time?

Happy New Year everyone!

Welcome to the first post of 2015! I hope you've had a great break and are ready to explore more of what Excel has to offer in the New Year.

Many of you will know Bill Jelen from his many Excel books and/or his excellent (no pun intended) website, www.mrexcel.com. Well Bill is compiling a new book, MrExcel XL - 40 Greatest Excel Tricks and he sent me the following tweet over the Christmas break:



This was a tough challenge and the only way I could really think to address it was to look at the popularity (website traffic) of each of the tips that I post on here. This simplified the task hugely as there is one post that is consistently in the top three posts each month, even though it was written over three years ago. It is also something that I use in almost every formula I write!

My Greatest Excel Tip of All Time would be....

FANFARE....

"Learn how to use absolute references using $ and F4"

I have copied the information from my original post below, but if you like, you can still see the original, The dollar sign ($) in a formula - Fixing cell references, here.

Relative cell references
When you copy and paste an Excel formula from one cell to another, the cell references change, relative to the new position:

EXAMPLE:

If we have the very simple formula "=A1" in cell B1 it will change as follows when copied and pasted:

Pasted to B2, it becomes "=A2"

Pasted to C2, it becomes "=B2"

Pasted to A2, it returns an error!

In each case it is changing the reference to refer to the cell one to the left on the same row as the cell that the formula is in, i.e. the same relative position that A1 was to the original formula.

The reason an error is returned when it is pasted into column A, is because there are no columns to the left of column A.

This behaviour is very useful and is what allows a sum to be copied across or down the page and automatically refer to the new column or row that it finds itself in.

But in some situations, you want some or all of the references to remain fixed when they are copied elsewhere.

The dollar sign ($)
This is where the dollar sign is used.

EXAMPLE:

Take an example where you have a column of Sales values in Pounds Sterling in column A and a formula to convert these into US Dollars in column B. You could enter the actual exchange rate into the formula but it would be more sensible to refer to a cell where the exchange rate is held, so that it can be updated whenever it is needed.


The simple formula for cell B2, would be "=A2*E1", however if you copy this down, then the formula in cell B3, would read "=A3*E2" as both references would move down a row as described above.

This is where the dollar sign ($) is used. The dollar sign allows you to fix either the row, the column or both on any cell reference, by preceding the column or row with the dollar sign. In our example if we replace the formula in cell B2 with "=A2*$E$1", then both the "E" and the "1" will remain fixed when the formula is copied. i.e. in cell B3, the formula will read "=A3*$E$1", still referring to the cell with the exchange rate in it.

In this example we have fixed both the row and the column, but in other situations, you may just want to fix one or the other, for example:


Above we have a spreadsheet calculating the times tables where we want to every cell in the white area to be the product of its row and column heading. This is easy using the dollar symbol. In cell B2, the formula without dollars would be "=A2*B1", but for this formula to work when copied to each column, we need it to always look at column A for the first reference and to work for each row, we need to always look at row 1 for the second. Using the dollar sign to do this, it becomes "=$A2*B$1". This can then be copied to every cell in the white area.

Quick Tip
You can speed up entering the dollar signs by using the function key F4 when editing the formula, if the cursor is on a cell reference in the formula, repeatedly hitting the F4 key, toggles between no dollar signs, both dollar signs, just the row and just the column.



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