Range Names List

If you use range names then it is a good idea to include a list of all the range names in a file for documentation purposes.

There is a shortcut that does all the work for you.

Select the cell where you want to enter the list.

Press F3 and then click the Paste List button.

Paste_List

The list has two columns. The range name and it’s reference.

The list is not dynamic. If you add, delete or change the names you will need to do the paste again.

“Why,” said the Dodo, “the best way to explain it is to do it”

Lewis Carroll (1832-1898) from Alice in Wonderland (1865)

VBA – Easy Way to Handle TRUE/FALSE Tests

Using a boolean variable

Let’s say you have VBA code that handles a budget and a forecast. There is a cell B2 on the Input sheet that contains the word Budget or Forecast. Based on that cell the macro with do different things. You may need to test for Budget/Forecast a few times within the code. There is an easy and flexible way to handle this.

M is for (Data) Monkey (Holy Macro! Books, 2015)

Don’t be put off by the title, this book is full of gold. It is chock full of tips, tricks and traps.

Power Query allows you to automate data cleansing operations on all sorts of Excel data – both internal and external.

Power Query can replace copying and pasting. No need for formulas and macros to get the data right.

Well worth purchasing – it will save you hours of work.

 

FREE Recording – Recorded Macros – tips, trick and traps

I have posted the video link of my most recent free Excel webinar – Recorded Macros Tips, Tricks and Traps. This is the first session in my macro series. This first session is now free. It covers recording macros, the terminology, and background – what you need to know before starting with macros. Also how to make more flexible and re-usable macros.

Recording Macros

FREE Recording – More Excel Financial Functions

I have posted the video link of my most recent free Excel webinar – More Excel Financial Functions. I cover four functions plus answers to two question from the previous free webinars. Average feedback score was over 88% from over 130 responses.

Latest Free Excel Webinar Recording.

Right Click – Filtering

One of my favorite and frequently use right click tips is the Filter option.

You can click a cell within a table that contains the entry you want to filter by, then click the Filter option and then Filter by Selected Cell’s Value.

right click filter  Note: you can also filter by font and fill colour.