Monthly Archives: March 2015

Excel – Using the Formula Auditing Tools

Once you’ve created a spreadsheet or you get one from someone else, one of the more painful things you end up doing at some point is trying to work out which cells feed into any of the formulas throughout the workbook or alternatively, which cells are dependent on any given cell(s).

Finding which cells feed into a single formula is easy, just by clicking on the formula cell which then highlights all the cells that feed into it.

Checking a single formula's data source

Checking a single formula’s data source

However, this is very limited as you can only check out one formula at a time and there is no way of knowing if any of the cells that feed into your formula are in turn dependent on other cells for their values short of checking each and every cell which of course no sane person would do.

So how can you check this without spending hours looking at all the cells in your worksheet/workbook?

This is where the FORMULA AUDITING tools come in. On your FORMULAS tab you will find the FORMULA AUDITING group.

Formula auditing group

Formula auditing group

In this blog I am going to concentrate on TRACE PRECEDENTS and TRACE DEPENDENTS, ERROR CHECKING and will write separately about EVALUATE FORMULA.

I will briefly mention SHOW FORMULAS before starting on the main topics of this blog. As the name suggests, this will convert all cells with formulas on the current worksheet from displaying their values to displaying the formulas contained within them.

Viewing all formulas on a worksheet

Viewing all formulas on a worksheet

You can achieve the same effect using CTRL + ` (on an English – UK QWERTY keyboard). It’s a quick way of viewing all formulas on a worksheet without the need to view each cell separately. Can’t say any more than that!

So how do you find out which cells feed from or into another cell?

Let’s start by finding out which cells supply a formula cell with its inputs. Click on any cell containing a formula and then click on TRACE PRECEDENTS.

ScreenHunter_191 Mar. 29 13.13

Showing precedents feeding into a formula cell

Any cell(s) that provide data to your formula should now be highlighted. But do any cells feed those before they feed into the formula cell? Simply click the TRACE PRECEDENTS button again.

Tracing inputs further back

Tracing inputs further back

Keep on clicking until it can go no further i.e. you have reached the original source to all the data feeding into your formula.

Now that you’ve found out all the cells that feed into your formula, is the result of your formula used anywhere else. In other words, are cells DEPENDENT on this one?

Staying on the same formula cell, now click on TRACE DEPENDENTS.

Tracing cells that are dependent on the current cell

Tracing cells that are dependent on the current cell

As with TRACE PRECEDENTS if you continue to click the TRACE DEPENDENTS button you will eventually get to the final cell in the chain that is influenced, however remotely, by the currently selected cell.

There will be times when you check for PRECEDENTS or DEPENDENTS and you will see this;

Showing data comes from outside the current worksheet

Showing data comes from outside the current worksheet

This tells you that data from an external source is feeding into the cell. This can mean from another worksheet in the same workbook, or from another workbook altogether. Double click the dashed line and it will open the GO TO dialog box.

Finding external cells that make up part of the formula

Finding external cells that make up part of the formula

This will show you the source of the data and if you click on the reference and click on OK, it will take you the feeder cell if it’s in the same workbook, or if the other workbook is open at the time.

There comes a point where there are too many lines/arrows to follow. To remove any lines/arrows click on REMOVE ARROWS to remove all visible lines/arrows, or select REMOVE PRECEDENT ARROWS or REMOVE DEPENDENT ARROWS depending on what you want clear off screen.

Remove trace arrows

Remove trace arrows

Alternatively, you can trace precedent and dependent cells by using CTRL + [and CTRL + ] respectively which simply highlights any cell(s) that either precede of follow the currently highlighted cell.

If your formulas produce error messages such as #DIV/0! And you have eliminated syntax errors, it could be that one of the feeder cells somewhere in the chain of cells has an error. Use ERROR CHECKING to find and correct those errors.

Error checking options

Error checking options

Click on TRACE ERROR to show all cells feeding into the formula producing the error. This works just like TRACE PRECEDENTS showing any external links.

Error tracing arrows

Error tracing arrows

For more information select ERROR CHECKING. This will show you where the error is and what type of error it is. You then have access to a number of options such as EDIT IN FORMULA BAR or IGNORE ERROR in case the problem is simply due to incomplete or missing data which will correct itself once it is updated.

Error tracking options

Error tracking options

So next time you are not sure where a formula gets its inputs from, or you need to trace an error you can now use your AUDITING TOOLS to help you out a bit.

 

 

Advertisements

PowerPoint – Working with Master Slides/Templates

Both screenshots below were taken from presentations with a .potx or template file extension. However, which one of these is a true PowerPoint template?

Tepmplate #1

Tepmplate #1

Template #2

Template #2

The correct answer is the second one (Template #2). How should you know this? Both files have the .potx file extension so surely they are both the same?

When you open a new PowerPoint what do you see? All you see is the TITLE SLIDE layout. This is a template. All blank presentations are based on a basic template…just like Word bases all new documents on a template with pre-set styles, fonts etc.

If you see anything more than this on screen…it’s not a real template!

If I had a pound for every time someone has asked me sort out their template which turned out to be a presentation with a .potx file extension I’d be a rich man. This is a very common mistake and stems from people not understanding how PowerPoint templates work, and for these to work you need to set up your MASTER SLIDES.

MASTER SLIDES are key to how your presentation looks and behaves, in terms of slide layouts, fonts, how bullet points (shudder!) work.

Let’s build a simple example…

Open a new BLANK presentation and go to the VIEW tab and click on SLIDE MASTER. You can now see the bare bones of the presentation and this is where you will do all your set up.

Open or view the Slide Master

Open or view the Slide Master

Basic slide master view

Basic slide master view

Note that whatever slide layout you are in, you will be taken to that slide layout in the SLIDE MASTER.

So this is where the magic takes place. The golden rule here is don’t add any content!!! Many people make the mistake of creating their presentation here in the MASTER SLIDESDON’T! This is where you set formats, font sizes and even custom layouts.

If you look at your SLIDE MASTER layouts down the left hand side of the screen you’ll notice one layout that is bigger than the rest.

The "Master Master" slide

The “Master Master” slide

For wont of a better name, I refer to this as the MASTER MASTER slide. Anything you add here such as logos, images etc. will appear in every layout. Use the normal tools to add images, and as soon as you add something you will see it appear in every slide layout below.

Background image copied down to all layouts

Background image copied down to all layouts

If you want something to ONLY appear in a specific layout then click on the layout and add whatever you need there. Whatever you add will only appear in that layout and not in any of the other layouts.

Individually customised title slide

Individually customised title slide

Continue to customise each layout as necessary until all your slides have the required logos, images or designs.

At any point you can come out of the MASTER VIEW by clicking on CLOSE MASTER VIEW.

Close the Master Slide view

Close the Master Slide view

If you click on the layout button on the HOME tab you will now see the various layouts with your newly created design.

Layout options on the Home tab

Layout options on the Home tab

Once the design work is complete then it’s time to set up fonts and bullet styles. Go back to your MASTER SLIDE view and click in one of the layouts.

Let’s assume that I want to set styles for all slides rather than adjust each one individually. Make sure the top slide in the MASTER VIEW is selected. Click on the title box and using the options on your HOME tab, set the font, size, colour etc. Note that as you set this title, all other titles on the other layouts are automatically updated to the new font style.

Title styles copied to all slides

Title styles copied to all slides

If you intend using bullet points, click in the box below to set the MASTER TEXT STYLES.

Font styles set

Font styles set

In this example I have set a different font at each bullet level. I would never do this in a real presentation, nor would I recommend it, but have done it here just to show what can be done. I can now also set the appearance of my bullets.

Bullet styles set

Bullet styles set

Again, this just to demonstrate what can be done rather than what you should do. If you take a look now at any of the other layouts you will see that the fonts and bullet styles have been changed throughout the presentation.

Formatting applied to one of the layouts from the Master Master slide

Formatting applied to one of the layouts from the Master Master slide

Remember that you can set each slide layout individually to have its own style, font size, colours, bullets and background images. Bear in mind that too many style changes etc. in any presentation are distracting and can make it look messy.

Once you have set everything up, save it as a template. When you reopen the presentation you will only see the main title slide and as you start typing the content, it will appear in the pre-set format you created in the template.

Using the template

Using the template

You might be thinking, why go to all this trouble setting up fonts etc. in a template when I can do all this manually on the fly as I create my presentation? Well…the first thing is consistency. Every slide you create will have identical text, backgrounds etc. and you won’t have to think about the font size, colour or anything else for that matter which ultimately will save you a lot of time. All the points I have just mentioned are even more valid when applied to a business context where branding and consistency of branding are all important. Relying on users to apply the correct font, colour or images is very risky. So use a template…and make sure it is a REAL template and not a presentation masquerading as a template. Now go check out all your office “templates” and see if they are real or fake!

Excel – Dynamic Range 12 Month Rolling Horizontal Chart

Horizontal 12 Month Rolling Chart

After publishing my blog on dynamic ranges to create a 12 month rolling chart (http://wp.me/p2EAVc-ah), I have received a number of enquiries about how to apply this to a horizontal table of data.

Personally, I prefer to have my data tables set vertically as it gives me far more options to slice and dice my data, apply filters, pivots etc. Based on the number of questions I have had there seem to be quite a number of businesses out there using a horizontal data layout.

So here is how it’s done;

The formula you will need will be as follows (bearing in mind you may need to adapt this to your own data table);

=OFFSET(Sheet1!$B$2,0,COUNT(Sheet1!$2:$2)-1,1,-12)

Our anchor point is $B$2, and always starting in the same row (0). The COUNT function counts the number of cells in row 2 and takes away 1 to give the correct end cell i.e. the last cell in the row to contain a numerical value. The height of the range is 1 row but the width is 12 columns back from the last cell that was identified by the COUNT function.

As we are applying this to a chart, we also need to create a dynamic range to pick out the correct labels;

=OFFSET(DynHorizData,-1,0)

As explained in the earlier blog, this dynamic range is based on the dynamic data range we created above. “DynHorizData2 is just the name I assigned to the range.

And there you have it…a 12 month rolling chart based on a horizontal data table.

To see an example using the formulas above follow the link below to open a workbook (Excel 2013) called “Horizontal Dynamic Chart.xlsx”. The workbook is read only, so click on FILE and Save As to download the file and then you can edit the data and see what happens.

https://onedrive.live.com/redir?resid=2C036E200F2C8BCF!242&authkey=!AKh4mjBK2AWbTnw&ithint=file%2cxlsx

Excel – Introduction to Recording Macros

In this blog, I am going to introduce the concept of macros in Excel, and show you how to record and run basic macros – if you already write your own code, then read no further.

Macros are a great feature in Excel (and any other Microsoft application for that matter). They are simply a series of steps which have been automated. So instead of you clicking several buttons, or highlighting data and formatting it in some way, a macro can complete all of those actions with the single click of a button – so they are potentially huge time savers.

The recording of macros is very simple, but it is limited, and ultimately, if you want to automate your work in Excel you need to write code from scratch.

The simplest way to create a macro is to record it. You can do this via the VIEW tab and click on MACROS, and select RECORD MACRO.

Record new macro options

Although you can access everything through this button, I would recommend turning on the DEVELOPER tab. This give you access to more things relating to macros and VBA as well as form controls. If you’re using 2007, click on the OFFICE button and click on EXCEL OPTIONS. From the POPULAR tab tick the SHOW DEVELOPER TAB IN THE RIBBON option. For those on 2010 or 2013, right click on the RIBBON, select CUSTOMIZE THE RIBBON and tick the DEVELOPER box in the right hand window.

Time to start recording…

First of all plan what you are about to record. I say this because the recorder records everything you do…including mistakes, re-edits etc. It just means that every time you run the macro it will repeat all the mistakes and the consequent corrections (albeit at high speed), so plan and if necessary do a couple of practice runs so you know which command/icon to click on, what to select etc. The recording is not done in “real time”, so if you pause to answer the phone, or have a cup of tea in the middle of your recording it won’t record several minutes of inactivity. It only records actions on screen, and an important thing to bear in mind here, it records ONLY the actions you perform in Excel. Opening Windows explorer, or Word or anything else for that matter will not be included as part of the recorded macro. Remember, I said it was limited.

Assuming I have planned and rehearsed where necessary, it’s time to do the actual recording.

Click on the VIEW tab, MACROS, RECORD MACRO, or go to the DEVELOPER tab and click on RECORD MACRO.

You should then see the following window;

Naming and storing macro options

Give your MACRO a NAME. Using the default name is fine, but once you have a collection of macros all called Macro1, Macro2 etc. it will be very hard to tell one from another unless you look at the code to work out what each one is doing. So give it a helpful name. Note that no spaces are allowed in macro names. If you do want a space of some sort, use an underscore (_). Personally, I use something that is called Camel Hump Text i.e. capiltalise the first letter of each word that makes up the name of your macro. YouWillBeSurprisedAtHowEasyReadingTheNameIsDespiteBeingContinuousText…I rest my case.

The choice as always is down to your own personal preference.

You can assign a shortcut key to your macro. All I will say here is “good luck” finding a free combination of keys. Easier to just add a button to your Quick Access Toolbar or create new tabs on your Ribbon (2010+) and add buttons there.

Now for the important bit: STORE MACRO IN. You will be presented with 3 options;

  1. Personal Macro Workbook
  2. This Workbook
  3. New Workbook

If the macro is going to work exclusively in, or from within the current workbook, then select THIS WORKBOOK. So if the macro is unique to the current workbook and won’t be used elsewhere or the macro (for example) imports data from other sources, but ultimately does all the work in the current workbook then THIS WORKBOOK is the best choice.

NEW WORKBOOK is a bit of a pointless choice in my opinion as the new workbook effectively becomes the new THIS WORKBOOK. End result is same as above.

If, however, you want your macro to work in any workbook at any time, or it involves opening a workbook for example, then the best place is in your PERSONAL MACRO WORKBOOK. This is a blank workbook that sits in the background to Excel and is used purely to store your macros in “folders” called MODULES. By default, PERSONAL does not exist, you need to record something in it to get it kick started. Once created, you can either record or write code directly inside the modules. PERSONAL, is as the name suggests, personal to your Excel. It resides on your computer alone.

If you choose the “wrong” place by mistake you can easily copy/cut and paste code from one workbook to another (assuming PERSONAL has been created).

The final box is for comments. This serves no purpose other than to add a note to say what the macro can do or just some information that may be of use to you or any other user of the macro.

Click on OK to start recording.

Do whatever it is you need to do, and remember to STOP RECORDING once you have done . Click on STOP RECORDING in the DEVELOPER tab or the small square in the bottom left hand corner of the screen.

Stop recording button on the Ribbon

 

or

Stop recording macro button on the status bar

 

 

To run the macro you have just recorded, go to the DEVELOPER tab and click on MACROS, click on the macro name and then click on RUN.

Run macro window

As this is quite a long winded way of running a macro, add a button to your Quick Access Toolbar (QAT) or add a new tab to the Ribbon.

Here is a quick video showing a macro being recorded and a shortcut button added to the QAT.

If you want to view the code that has been recorded then go to the DEVELOPER tab and click on VISUAL BASIC or use ALT + F11. This will open the Visual Basic editor. Take a look at the code. It’s not as difficult to decipher as you may think –

  • Font.Color
  • Cells.Select
  • Font.Bold = True

Without knowing any code, these sort of bits of code are self-explanatory. You may not know the details or the why but you can get a pretty good idea of which bit does what in your recorded code. Like any language, you need to practice it to become “fluent” and VBA is no different. The more you see it, write it etc. the better you will get…simple as that.

So what are the downsides to recording macros?

  • Redundant code – the recorder has a tendency to record a lot more than is necessary. Try recording a macro where you do a bit of PAGE SETUP for printing. The amount of stuff it records is ridiculous.
  • Absolute references – unless you click on USE RELATIVE REFERENCES (and even that has limited use in the real world), the recorder does everything in absolute terms. So if you record putting in AUTOSUM in line 100, the next time you run the macro and the worksheet has 500 lines in it, it still places the AUTOSUM in line 100. So it’s not dynamic or clever enough to work out there is additional data.

So yes it does have limitations but recording macros does have its uses. If you have a report that is in a fixed format but needs to be adjusted each time it is run, then a recoded macro will do a great job as the report will be in the same format each time. Recording is also very useful if you are unsure what code is needed. Just record a couple of clicks, stop recording and view the code. This limits the amount of possible redundant code that can be generated and helps you focus on just two or three lines of code that will help you.

Your long term plan with macros though should be to learn how to write code from scratch. Like that you get exactly what you want and with practice you may well find that writing a 3-5 line macro and running it is quicker than doing some jobs manually.