Monthly Archives: March 2014
In Excel 2013, amongst all the other amazing features that have been added since 2010 is a new add-in that can analyse your workbook for errors or inconsistencies.
The problem with this add-in, is that it has not been particularly well promoted and it is not in the standard add-in list that most of us go to turn on add-ins.
Firstly, note that this add-in is only available in the Professional Plus version of Excel. If you do have this version, then go to FILE, OPTIONS, ADD-INS. Go to the bottom of the screen and click on the drop down where you see EXCEL ADD-INS and click on COM ADD-INS and then click on GO.
Tick the box next to INQUIRE to activate it and click on OK.
You should now see a new tab on your Ribbon.
You will now access to a range of functions that can help you to examine your workbooks. If you don’t have this tool, you can still use some of the FORMULA AUDITING tools in the FORMULAS tab such as SHOW DEPENDENTS etc. but of course this is a more manual process and won’t give you the same level of detail that the new add-in can.
Make sure that the workbook you want to analyse has been saved. This will not work on an unsaved workbook. Click on the WORKBOOK ANALYSIS button and let it do its thing. After a few moments (this will vary depending on how big and complex your workbook is) you will see this window;
You will see an overall summary of the workbook contents and if you click on each element in the ITEMS window, you will see the details in the RESULTS window on the right hand side.
OK, you may not necessarily be interested in how many cells contain formulas etc. but there is some useful information in here such as external references, cells containing errors etc. As you click on each element, you will see exactly which cells on which sheet meet those criteria.
Click on EXCEL EXPORT to create a copy of the report.
As the name suggests, this will show you a diagrammatic view of how your workbook is connected to external data sources – other workbooks, databases etc.
Use the buttons at the bottom of the window to print the diagram, preview the diagram, or simply to refresh it.
If your workbook relationships are particularly complex you can rearrange the various workbooks and databases by clicking and dragging the icons in the window into an easier to read layout.
Similar to above, but this time looking at how the worksheets are connected rather than at workbook level.
Same buttons at the bottom of the window and the layout can be changed too just as before.
When you run this you will get a few options to select from in terms of searching for precedents and/or dependent cells. Pick whichever options suit your requirements best and click on OK.
And it does exactly what is says…looks at precedents/dependents of any selected cell (rather than every single cell in your workbook). As I mentioned earlier, this can be achieved using the FORMULA AUDITING tools but this is a more convenient way of seeing everything. As with all the other relationship diagrams, same options are available.
In Excel you have had the option to look at workbooks side by side for some time but again this was a very manual process scrolling through your workbooks carrying out visual checks to see any differences between workbook versions.
This option gives you an automatic check.
Click on COMPARE FILES and use the drop downs to select the correct workbooks to compare. Note that both workbooks must be open in order to run this. Click on COMPARE to run the comparison showing differences
This will produce a full screen report. Where there are variances the cells will be colour coded depending on the type of variance.
You can review each worksheet independently. Check the lower half of the screen to see the colour coding and the details about the differences.
There is also a small chart to show you how many of each variance type it has detected. Not the most essential bit of information but looks pretty in the exported report as a sort of dashboard.
COMPARE FILES also compares VBA code between workbooks if it is present, so quite a useful tool.
Clean Excess Cell Formatting
You well find that a workbook is slow to open, or may take a while to refresh every time you make a change. This could be for a variety of reasons, but one of those may well be excessive cell formatting. When you set up a worksheet it may be convenient to apply conditional formatting, or just simple formatting to entire rows or columns. This can make your workbook quite bloated. CLEAN EXCESS CELL FORMATTING looks at empty cells beyond the last cell that contains data and removes any formatting that may be applied.
When you run this you get the option to run on the active sheet or the entire workbook. Click on OK once you have made your choice. You may be prompted to save changes but otherwise no other windows to interact with.
This is used to store passwords for workbooks so that next time you open the workbook to run a comparison analysis you don’t have to enter the password. This probably has only limited appeal but is useful if you run any analysis or comparison when accessing the INQUIRE add-in via the START menu.
Click on START, go to the MICROSOFT OFFICE folder and open OFFICE 2013 TOOLS.
From there click on SPREADSHEET COMPARE 2013. This will open a new window. Use the COMPARE FILES button to select the two workbooks you want to run the comparison on. If passwords have been stored in the PASSWORD MANAGER then you won’t have to enter any passwords to open or access the file(s).
Click on OK once you have chosen your files and continue as above to review your files.
So next time you need to run a quick analysis of your workbook, or need to compare two files, (and you have Excel 2013 Professional Plus) remember to turn on your INQUIRE add-in and give it a run…much quicker than doing it all manually.
Cut, copy, paste must be the bane of some people’s working lives. Whether you use right clicks or keyboard shortcuts to do it, chances are this is one of the most common repetitive actions you do at work, whether it is in Excel, Word or pretty much any application you care to think of.
A large proportion of what we copy and paste will be one off events, but those of you who do monthly reporting, or any type of reporting for that matter, you probably find yourself doing a lot of copying and pasting of the same data, month after month.
In this blog I will show you some simple code that will automate the whole process. I will break this down into sections;
- Within the same worksheet
- Between different worksheets
- Between different workbooks
- Using the clipboard
- Using paste special
Copy/Paste in the same worksheet
We have some data in cells A1:A11 and want to copy it across to another part of the same worksheet. For this example, let’s say I want to start pasting the data starting from cell E12.
Let’s just think about how we would do this manually first – highlight the cells A1:A11, right click, select COPY or press Ctrl + C, click into cell E12 and press Ctrl + V or right click and select PASTE. I don’t need to select a range of cells to paste into, just pick the top left hand cell to start pasting from.
It’s exactly the same in VBA:
Say what you want to copy, what you want to do to it i.e. copy and tell it where to start pasting from.
Note, I do not need to use the word PASTE anywhere in my code. By keeping all the code on ONE line, the cell reference that follows the space after COPY is the DESTINATION cell to start the paste operation from.
The key to keeping this simple is to ensure the copy/paste instructions are kept on the same line. If you move to a second line of code, the data is stored in the CLIPBOARD and has to be extracted again. I’ll show this a bit later but most of the time it is unnecessary.
If you want to CUT and PASTE then replace the word COPY with CUT – simple as that!
This code assumes that we are copying and pasting within the ActiveSheet. If we want to be a little more precise to avoid any errors then we can specify the name of the worksheet we want to copy and paste in;
By being that little more specific we don’t have to worry too much about which worksheet we are in when the code runs. We can be even more specific by naming the workbook too and that avoids any issues of current workbook or current worksheet.
Workbooks(“Demo”).Worksheets(1).Range(“A1:A11”).Copy _ Range(“E12”)
In keeping with writing code I have used a space followed by an underscore as a continuation marker. This tells the VB editor that this is a continuous line of code and not two separate lines. This is useful when you write code as it means you do not have to keep on scrolling across to the right to read a long line of code…particularly handy when writing long lines of code for copy/paste!
Copy/Paste between different worksheets
Basically you need to reproduce what we did in the example above only now you have to specify a source sheet and destination sheet. Think of it a bit like an equation that needs to be balanced either side of your copy/cut command;
As in the previous example I can be a little more precise by naming the workbook at the beginning just in case I don’t happen to be in the workbook when the code is running.
Copy/Paste between different workbooks
Same again, but this time I need to make sure I reference the source workbook name and the destination workbook name as well as the sheet and range references;
Remember to keep the code on one continuous line or use the line continuation marker ( _).
When using the CUT/COPY code, because you can be very precise with the workbook, worksheet and range references, as long as those workbooks are open you don’t have to make either one of the workbooks active. The code will run irrespective of which workbook you are in, making this code very efficient as you don’t have to activate or select anything for it to work.
Move/Copy/Paste entire worksheets
Rather than just CUT/COPY a range of cells, you may want to do it to an entire sheet.
Virtually no difference with the code above, only now we don’t need to reference the cells, only the worksheet.
The first line will move sheet 1 to the LEFT of worksheet 3 and the second line of code, the addition of the COMMA moves the sheet to the RIGHT of worksheet 3. You could of course COPY the sheet instead…works exactly the same way.
The make the code a little clearer you can use the word BEFORE:= or AFTER:= rather than no comma, or comma respectively to place the worksheet.
Similar to copying cells between workbooks, you have to specify the source and destination workbooks.
Workbooks(“Source”).Worksheets(1).Move _ Workbooks(“Destination”).Worksheets(1)
The absence of a comma after MOVE will place the worksheet to the LEFT of sheet 1 in the destination workbook, and a comma will move it to the RIGHT.
Note there is no reference made to cells. When you copy or move an entire sheet you cannot paste it into a cell or range of cells unless you copy all the cells in a sheet rather than the sheet itself. It’s a bit like tearing a sheet out of a book…you don’t stick it on top of a sheet covering what is already there, you slide it in before or after an existing sheet.
Copying to the clipboard
There may be times when you don’t want to CUT/COPY/PASTE immediately to a known destination as you may need to do some work first in a sheet and paste some information below it.
…do whatever else needs doing with your code
Application.CutCopyMode = False
Unlike the earlier examples the COPY and PASTE actions are now two separate lines of code. When you COPY it is put on the clipboard. Eventually you will be in an ActiveCell and that is where you use the ActiveSheet.Paste command. The final line above is used to clear the clipboard.
Note that this method only allows you to copy one thing at a time to the clipboard. There are ways of working with multiple items but it’s beyond the scope of this blog.
Using Paste Special
If you are used to Excel 2010 or 2013 you will know how easy it is now to access the features of Paste Special. To write this in VBA is not that difficult.
Copy your cells exactly the same as before, go to another cell, same sheet/same workbook/other workbook and use the .PasteSpecial command followed by a bracket. When you type the bracket you will see the prompt to show all the parameters and the first one is the paste TYPE. Choose from:
You then have two more optional parameters you can set;
And finally, decide whether you want to SKIP BLANKS or not. Set this parameter to TRUE or FALSE.
You can use the CutCopyMode line of code again to clear the clipboard and remove the dashed lines (2013) or the marching ants around the copied cells.
The one option that is missing from here is PASTE LINK.
Follow the code to do COPY and PASTE using the clipboard but on the PASTE line of code add a comma and TRUE. This will then paste in as a link.
Note that you will not be prompted with anything when you enter the comma…you just have to know.
So there you have CUT/COPY/MOVE/PASTE/PASTESPECIAL/PASTE LINK in VBA for Excel. No more manual cut/copy/paste on those monthly reports!
To animate or not…always a tricky one. The general feeling in a business environment is to not add any sort of animation. I have come across some customers whose house rules are so restrictive that most of the functionality within PowerPoint has been rendered redundant.
The problem with animation is that people can get easily carried away when adding animation to their slide and simply add animation because they can rather than use it purposefully. You may well see so much animation and varied transitions between slides that it actually detracts from the content of the presentation…or makes people feel ill because they feel more like they are on a rollercoaster as images go flying past their eyes!
So it’s probably safe to say that too much animation is not a good thing. But what is too much? Most presentations are made using bullet points…too many bullet points. This makes it boring and people tend to read on if all the bullets are shown on screen straight away. Bullets can now be easily replaced with SmartArt objects, and these can be animated to show one segment at a time. If you have not tried this before, this is how it is done;
Assuming you have a slide already set up with a number of bullet points, click on the text box to select it and go to CONVERT TO SMARTART on the HOME tab in the PARAGRAPH group.
Click on one of the SmartArt diagrams available or if nothing catches your eye, click on MORE SMARTART GRAPHICS to see the whole list of graphics split into various categories.
Once you have chosen one you can use the edit panel to the left of the graphic to alter your text. Depending on the graphic selected you can type additional text in other areas such as the grey area in the example above. If you are not happy with
The chosen graphic pick another until you have the best that matches your requirements.
Immediately you can see that you have something a little more interesting than a handful of basic bullet points.
To add a little more interest you can animate the graphic.
Go to the ANIMATIONS tab and pick from a wide range of animation effects. This is where a little self-restraint comes in handy. You may want to consider if a particular movement emphasises your point or is it just animation for the sake of animation and picking something a bit wild. This is something you have to decide for yourself I’m afraid!
The main types of animation you can apply are:
- Motion paths
You can control your animation through the ANIMATION PANE, setting timings, how the object appears etc. If you are going to have a lot of animated objects on one slide it may be worth naming each object as it helps you identify them easily within the animation pane. From the HOME tab, go to the EDITING group and click on SELECT and turn on the SELECTION PANE. In here you can rename each object on the slide to something a little more meaningful that just Diagram 1 etc.
So at the moment we have given the whole SmartArt graphic an animation which is fine but it would be better to animate each part of it separately to add more interest and to not reveal the entire plan in one go. In the animation pane select your object and click on the arrow on the right hand side to reveal all the options available to you to customise the animation.
Choose whether the object is animated by a click or whether this happens automatically…whichever you prefer.
The option that is important regarding animating SmartArt is EFFECT OPTIONS.
Go to the SmartArt Animation tab and choose from the options in the drop down list. Depending on the type of graphic selected and the number of elements that make it up, the animation may vary slightly in terms of how it works. Again, if the animation is not to your liking pick another graphic and see how that one behaves using the various options.
Here is one example. Each element is animated by a click.
And the same bullet points but using a different animation and graphic. Again, each element appears on a click.
So next time you are preparing a presentation, spare a thought for your audience. If you were watching your presentation would you be happy sitting through 10, 20, 50 slides with nothing but bullet points? Probably not. So add a little interest by having a number of animated SmartArt graphics. It’s not OTT and can actually help to get your message across.
In another blog I’ll show you how to add single and multiple animations to objects on your slide.