If you have ever looked around for tutorials on charts or read through books on charting techniques, one thing that pops up regularly is the X Y Chart Labeler by Rob Bovey.
This is a great little add-in, and best of all it’s free. You can download it from http://www.appspro.com/Utilities/Utilities.htm
So what does it do, and why is it so great?
When you create a chart in versions up to 2010, you can add labels to your series with one of the following;
- Series name
- Category name
In most cases this is ample, but what if you wanted to put something different? You could re-label everything in your data table but then it probably won’t make sense to anyone else. If you use scatter diagrams or bubble charts you can’t even do that as you need both x and y coordinates to place your markers and these act as labels. Identifying the individual scatter points or bubbles becomes quite difficult. You could add text boxes manually to everything but if the data point moves the text box doesn’t, so you have to move everything again by hand. All quite painful really.
So this is where the X Y Chart Labeler comes in.
Let’s take a simple example using a table showing sales by customer. What might be of interest is to show the name of the account manager against the customer sales. I could show sales for each customer or sales for each account manager but not both at the same time. Not easily, anyway.
You create your chart as normal, but then you create a set of custom labels, or as in this case the names of the account managers. You will need one label per data point/series in order for it to work, but there is functionality within the tool to show/hide individual labels.
Assuming you have installed and turned on the add-in, click on the X Y CHART LABELS tab and click on ADD LABELS.
Pick the series you want to label from the drop down list, then select the cells that contain your new custom labels. Select where you want them to appear (top/bottom/centre) and click on OK.
Your new labels will now appear on the chart. Job done!
If the position is not quite right, click on MOVE LABELS.
Set the number of points you want your labels to move with each click of the arrows, and using the arrows, relocate your labels. You can apply the move to all labels in one go, or select individual labels to apply the movement to.
Other functionality within this add-in;
Manual labeller: allows you to manually label individual data points using either already existing values in cells or by typing a value directly into the form.
Delete labels: as the name suggests, you can delete labels for a whole series or all labels in the chart.
Help: it comes with a searchable help file, although to be fair, you can’t really go wrong using this add-in.
If you have Excel 2013 (and I’m guessing that this will continue to apply to later versions too) you do have the option to attach custom labels, using cells from your worksheet.
Click on the chart to activate it and click on the plus symbol that you see top right hand corner of the chart area. Select CHART LABELS then click on MORE OPTIONS. Near the top of the window that appears on the right hand side, you will see the option VALUE FROM CELLS. Tick the option and then select the cells containing your custom labels.
Knowing that this functionality exists in 2013+, is there a need for the add-in? Probably yes! You still need the add-in to move, show/hide labels etc. more easily than doing it all manually, so from point of view it’s worth having. Perhaps later versions will include this sort of extra functionality, but until then use the add-in.
Now…some of you will be looking at this and thinking “wow, this is brilliant….but they’ll never let me download it at work”. We all have over zealous IT security police and if they don’t know what it is or can’t support it (even though it needs no support) they block it. The add-in runs off VBA code and Rob Bovey, the developer, has not locked the code down, so you, or any despotic IT jobs worth can look at the code quite easily and read all the helpful notes and comments that are in the modules and forms.
The add-in is perfectly safe. Rob Bovey is a recognised MVP or Microsoft Most Valued Professional, so he’s not some random programmer who is dumping dodgy code on the internet. The add-in is also mentioned by the likes of Mr Excel, Jon Peltier and other leading lights in the Excel world as the go to tool for this job. So the only reason the IT bod will refuse to load it is because he/she knows nothing about VBA, not because the code is seen as a justifiable threat!
So if you need to add custom labels to a chart, get this add-in. Once you have it you’ll be able to apply it to all sorts of custom charts. It’s quick and easy to use.
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.