Back to Excel Newsletter Archives
EXCEL TIPS AND TRICKS | EXCEL VBA TIPS AND TRICKS
Got any Excel Questions? Free Excel Help
This month we thought we would show you a nifty trick to highlight x axis labels when data drops below a certain range. Notice that the base for this article is Excel 2007, although instructions to make this work in prior versions are included. This article was supplied byAndy Pope and also features in our second edition of Excel Hacks – 100 Industrial Strength Tips & Tools, to be released around mid 2007.
There are 2 Excel files in the download, one is for 2007 and the other for all prior versions
When you create a chart, you can highlight X-axis category labels in a certain color when the data drops below a certain range. You can do this by creating a clustered column, then adding the data labels of two extra data series and plotting these extra series as lines.
Let’s say you had created a chart and you wanted to show horizontal (x) axis value labels in red when your data drops below 25 and in blue when the data is above 25. Here is how you can do this.
Set up a spreadsheet like in the attached workbook which shows the data and formulas used to build the chart. The actual data for the column chart is in the range C3:C14. The formula in columns D and E test the data value and either output a zero or #N/A depending on the value typed in column C and therefore determining whether a red or blue label should be displayed.
Using this data will highlight horizontal (X) axis labels in red when the monthly data drops below 25.
First we need to create the basic chart, so highlight the data in C3:C14 and go to the Insert tab. Under Chart options, select Columns and choose the first column chart (pre 2007, use the Chart Wizard). Go to the Layout tab and choose Series Red Label from the drop-down menu under the current selection. Now, go to the Design tab and choose Type>Change Chart Type. Select a Line chart from the menu presented. Then, do the same thing for the Blue Label series. (In versions of Excel prior to 2007, you will need to highlight the series, right-click, and select Chart Type).
Once again, go to the Layout tab, and select Series Red Label from the drop-down menu. Choose Labels>Data Labels>More Data Label Options (pre 2007, double-click on the series and select Data Labels), and check Category under Label Options. Then, do the same for the Blue Label series. Make sure that Value is not selected under Category name.
Yet again, go to the Layout tab, and select Series Red Label from the drop-down menu. Select Data Labels>Below (pre 2007, right-click on the data series, select the Alignment tab, and change the Label Position: to Below), and then do the same for the Blue Label series.
Highlight your original horizontal (X) axis, right-click your mouse, and select Format Axis (pre 2007, double-click the horizontal axis and select the Patterns tab). Under Axis Options, set the Major tick mark style to None and the Axis Labels to None. This will clear the original axis.
Finally, highlight the Red Labels data labels and go to the Home tab (pre 2007, use the Formatting toolbar). Under Font, change the Font color to Red and then do the same with the Blue Labels, formatting the font color to blue. Your final chart, with conditional highlighting, will show the x axis labels for the months May, September, October, December in red, as the values for these months fall below 25. All other axis labels will be blue, as the values for these months rise above 25.
Excel Dashboard Reports & Excel Dashboard Charts Half Price & Money Back
This month I thought we would look at coding to the VBE (Visual Basic Editor), in particular the Sheet and Workbook Objects. Perhaps the most common need for this is when you wish to write code using code. However, in this case, it is often better to create a template Sheet and/or Workbook Template and simply add/insert when/as needed.
The 2nd most popular reason for coding to the VBE could be when we need to delete/remove existing code in either the Sheet Object, or Workbook Object.
The macro below can be used to delete Sheet Code based on the Sheets CodeName supplied to theSelect Case Statement within the Sheets Collection Loop. It's ideal to use when you need to copy Sheets from a Workbook but do not want the code to copy with the Sheets.
You will have to go to Tools>Macro>Security - Trusted Publishers and check Trust access to Visual Basic Editor before running the code. You must also set a reference to the Microsoft Visual Basic For Applications Extensibility. This is done while in the VBE and going to Tools>References.
Sub DeleteSheetEventCode()''Needs Reference Set To _ "Microsoft Visual Basic For Applications Extensibility"'Tools>References.Dim sSheet As Object, strName As String For Each sSheet In Sheets Select Case UCase(sSheet.Name) Case "SHEET1", "SHEET2", "SHEET3" strName = sSheet.CodeName With ThisWorkbook.VBProject.VBComponents(strName).CodeModule .DeleteLines 1, .CountOfLines End With Case Else 'Whatever End Select Next sSheetEnd Sub
In the case of running this code from a different Workbook than the Sheet event code resides in, you must specify the Workbook by name and BOTH Workbooks must be open. That is, replace
For Each sSheet In Sheets
With code like;
For Each sSheet In Workbooks("Book1.xls").Sheets
Excel is rich inEvent Code for both the Workbook Object and the Sheet Object. For example, theWorkbook Open Event is possibly the most popular of
the Workbook Events (ThisWorkbook).
Delete Workbook Event Code With Code.
You will have to go to Tools>Macro>Security - Trusted Publishers and check Trust access to Visual Basic Editor before running the code
Sub DeleteWorkbookEventCode()''Needs Reference Set To _ "Microsoft Visual Basic For Applications Extensibility"'Tools>References. With ThisWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule .DeleteLines 1, .CountOfLines End With End Sub
Again, in the case of running this code from a different Workbook than the one the Workbook event code resides in, you must specify the Workbook by name and BOTH Workbooks must be open. That is, replace
With code like;
Free Excel Help .
Until next month, keep excelling!
Got any Excel Questions? Free Excel Help
Instant Download and Money Back Guarantee on Most Software
Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!
Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft
FREE Excel Help