Are you over 18 and want to see adult content?
More Annotations
![A complete backup of thekillingtimestv.wordpress.com](https://www.archivebay.com/archive2/b0d501b6-608c-4625-996b-90cc51b1e6e9.png)
A complete backup of thekillingtimestv.wordpress.com
Are you over 18 and want to see adult content?
![A complete backup of elpuertodesantamaria.es](https://www.archivebay.com/archive2/148ee956-cd38-42a8-bec3-6cdac42ee267.png)
A complete backup of elpuertodesantamaria.es
Are you over 18 and want to see adult content?
![A complete backup of cinechallo.weebly.com](https://www.archivebay.com/archive2/9c120fc1-2dbb-4a9b-b34c-9041a85d6dce.png)
A complete backup of cinechallo.weebly.com
Are you over 18 and want to see adult content?
![A complete backup of plaintext-productivity.net](https://www.archivebay.com/archive2/cc98d348-db13-4b34-8cf5-1e6a9ff3f1c7.png)
A complete backup of plaintext-productivity.net
Are you over 18 and want to see adult content?
Favourite Annotations
![A complete backup of https://teachersnetwork.org](https://www.archivebay.com/archive6/images/067797e2-8ceb-4b42-bd55-ae13b7f04d7c.png)
A complete backup of https://teachersnetwork.org
Are you over 18 and want to see adult content?
![A complete backup of https://bdo.com.au](https://www.archivebay.com/archive6/images/0de3cdf0-8bf5-4a22-9631-321d0a339742.png)
A complete backup of https://bdo.com.au
Are you over 18 and want to see adult content?
![A complete backup of https://wholesalencaajerseyscheap.com](https://www.archivebay.com/archive6/images/164239c9-9320-4754-ad06-93a6e9181db9.png)
A complete backup of https://wholesalencaajerseyscheap.com
Are you over 18 and want to see adult content?
![A complete backup of https://rotpunktverlag.ch](https://www.archivebay.com/archive6/images/3a1b1476-6068-4156-8997-00601399325c.png)
A complete backup of https://rotpunktverlag.ch
Are you over 18 and want to see adult content?
![A complete backup of https://mediumcube.com](https://www.archivebay.com/archive6/images/0dc3b96e-3ce8-481a-83ee-b36dfd759241.png)
A complete backup of https://mediumcube.com
Are you over 18 and want to see adult content?
![A complete backup of https://universalhub.com](https://www.archivebay.com/archive6/images/93c5ece6-9795-474d-af38-8594124caca8.png)
A complete backup of https://universalhub.com
Are you over 18 and want to see adult content?
![A complete backup of https://tacm.com](https://www.archivebay.com/archive6/images/9e0da6b6-73cd-40f0-b8db-9680f360fac6.png)
A complete backup of https://tacm.com
Are you over 18 and want to see adult content?
![A complete backup of https://laphampeakfriends.org](https://www.archivebay.com/archive6/images/e9f79c39-0784-4beb-9f0a-c7ca86a34bf6.png)
A complete backup of https://laphampeakfriends.org
Are you over 18 and want to see adult content?
![A complete backup of https://seeds-market.com.ua](https://www.archivebay.com/archive6/images/051dd750-d29f-42b1-9f9c-b366445dfe01.png)
A complete backup of https://seeds-market.com.ua
Are you over 18 and want to see adult content?
![A complete backup of https://thinktankphoto.com](https://www.archivebay.com/archive6/images/ceddeb86-3e6c-4fe1-8d51-cd187baff0dc.png)
A complete backup of https://thinktankphoto.com
Are you over 18 and want to see adult content?
![A complete backup of https://berkeywater.com](https://www.archivebay.com/archive6/images/3678e46f-192e-4cb3-b3d7-b2f32862f9bc.png)
A complete backup of https://berkeywater.com
Are you over 18 and want to see adult content?
![A complete backup of https://cochran.com](https://www.archivebay.com/archive6/images/863a94e5-94d4-4a4b-b6dd-a98ce3637b90.png)
A complete backup of https://cochran.com
Are you over 18 and want to see adult content?
Text
numerous
PELTIER TECH WATERFALL CHART Waterfall and Stacked Waterfall charts are available in Standard and Advanced Editions of Peltier Tech Charts for Excel. Dual, Rotated, Split Bar, and Paired Waterfall charts are available in the Advanced Edition.. Waterfall Chart Dialog. The Waterfall Chart dialog is rich with options. Many of the options are saved for the next time you openthe dialog.
THE EXCEL CHART SERIES FORMULA Plot Order. Plot Order is a series number within the chart. This is always a number between 1 and the number of series in the chart. “Plot Order” is a bit of a misnomer, because regardless of this number, some types of series are plotted before others. ADD A HORIZONTAL LINE TO AN EXCEL CHART Below is the data for our horizontal line, which will start at 1 (the first category number of 1) and end at 5 (the last category number of 5), without the half-category cushion at either end. Copy the data, select the chart, and Paste Special to add the data as a new series. Right click on the added series, and change its chart type to XYCHART VBA EXAMPLES
Here is the desired outcome: And here’s the chart VBA example: Sub ColorGreenToRed () Dim iSrs As Long, nSrs As Long If ActiveChart Is Nothing Then MsgBox "Select a chart and try again.", vbExclamation, "No Active Chart" Else With ActiveChart nSrs = .SeriesCollection.Count For iSrs = 1 To nSrs ' only format series whose names are found SelectJON'S EXCEL PAGE
Jon's Excel Page. Microsoft Excel Tips and Tricks. Here are a few sample Excel files with some of the tricks I've learned over the years. This is still only about 10% STACKED COLUMN CHART WITH STACKED TRENDLINES Using Actual Chart Trendlines. Here is the data. It plots items 1 through 4 over the years 2010 to 2016. I added four series, Cum1 through Cum4: Cum 1 is simply Item1, Cum2 is Item1 + Item2, etc. The CUM series then plot a line at each level of the stacked column chart: Cum1 plots at the top of Item1, Cum2 plots at the top of Item2, whichis
COPY CHART TO NEW SHEET AND LINK TO DATA ON Now, copy the new data from “New Data”, and paste it on top of the data in “Original Data (2)”. The data arrangements are identical on the two worksheets, so we should be fine. I usually use Paste Special – Values, but it doesn’t seem to matter. Look, the chart now is showing the data we just pasted on top of the copied data: MULTIPLE TIME SERIES IN AN EXCEL CHART I recently showed several ways to display Multiple Series in One Excel Chart.The current article describes a special case of this, in which the X values are dates. Displaying multiple time series in an Excel chart is not difficult if all the series use the same dates, but it becomes a problem if the dates are different, for example, if the series show monthly and weekly values over the same PIVOT CHART FORMATTING CHANGES WHEN FILTERED PELTIER TECH PRODUCTS AND SERVICES Peltier Technical Services, Inc. was founded in 2004 by Microsoft Excel MVP Jon Peltier. Peltier Tech provides consulting services, in the form of custom Excel development, using Excel worksheet, template, charting, and VBA technologies. Peltier Tech has developed Peltier Tech Charts for Excel, an extensive Excel add-in that providesnumerous
PELTIER TECH WATERFALL CHART Waterfall and Stacked Waterfall charts are available in Standard and Advanced Editions of Peltier Tech Charts for Excel. Dual, Rotated, Split Bar, and Paired Waterfall charts are available in the Advanced Edition.. Waterfall Chart Dialog. The Waterfall Chart dialog is rich with options. Many of the options are saved for the next time you openthe dialog.
THE EXCEL CHART SERIES FORMULA Plot Order. Plot Order is a series number within the chart. This is always a number between 1 and the number of series in the chart. “Plot Order” is a bit of a misnomer, because regardless of this number, some types of series are plotted before others. ADD A HORIZONTAL LINE TO AN EXCEL CHART Below is the data for our horizontal line, which will start at 1 (the first category number of 1) and end at 5 (the last category number of 5), without the half-category cushion at either end. Copy the data, select the chart, and Paste Special to add the data as a new series. Right click on the added series, and change its chart type to XYCHART VBA EXAMPLES
Here is the desired outcome: And here’s the chart VBA example: Sub ColorGreenToRed () Dim iSrs As Long, nSrs As Long If ActiveChart Is Nothing Then MsgBox "Select a chart and try again.", vbExclamation, "No Active Chart" Else With ActiveChart nSrs = .SeriesCollection.Count For iSrs = 1 To nSrs ' only format series whose names are found SelectJON'S EXCEL PAGE
Jon's Excel Page. Microsoft Excel Tips and Tricks. Here are a few sample Excel files with some of the tricks I've learned over the years. This is still only about 10% STACKED COLUMN CHART WITH STACKED TRENDLINES Using Actual Chart Trendlines. Here is the data. It plots items 1 through 4 over the years 2010 to 2016. I added four series, Cum1 through Cum4: Cum 1 is simply Item1, Cum2 is Item1 + Item2, etc. The CUM series then plot a line at each level of the stacked column chart: Cum1 plots at the top of Item1, Cum2 plots at the top of Item2, whichis
COPY CHART TO NEW SHEET AND LINK TO DATA ON Now, copy the new data from “New Data”, and paste it on top of the data in “Original Data (2)”. The data arrangements are identical on the two worksheets, so we should be fine. I usually use Paste Special – Values, but it doesn’t seem to matter. Look, the chart now is showing the data we just pasted on top of the copied data: MULTIPLE TIME SERIES IN AN EXCEL CHART I recently showed several ways to display Multiple Series in One Excel Chart.The current article describes a special case of this, in which the X values are dates. Displaying multiple time series in an Excel chart is not difficult if all the series use the same dates, but it becomes a problem if the dates are different, for example, if the series show monthly and weekly values over the same PIVOT CHART FORMATTING CHANGES WHEN FILTERED PELTIER TECH PRODUCTS AND SERVICES Peltier Technical Services, Inc. was founded in 2004 by Microsoft Excel MVP Jon Peltier. Peltier Tech provides consulting services, in the form of custom Excel development, using Excel worksheet, template, charting, and VBA technologies. Peltier Tech has developed Peltier Tech Charts for Excel, an extensive Excel add-in that providesnumerous
PELTIER TECH WATERFALL CHART Waterfall and Stacked Waterfall charts are available in Standard and Advanced Editions of Peltier Tech Charts for Excel. Dual, Rotated, Split Bar, and Paired Waterfall charts are available in the Advanced Edition.. Waterfall Chart Dialog. The Waterfall Chart dialog is rich with options. Many of the options are saved for the next time you openthe dialog.
PELTIER TECH ROTATED WATERFALL CHART Waterfall and Stacked Waterfall charts are available in Standard and Advanced Editions of Peltier Tech Charts for Excel. Dual, Rotated, Split Bar, and Paired Waterfall charts are available in the Advanced Edition.. Rotated Waterfall Chart Dialog. The Rotated Waterfall Chart dialog contains several options. Many of the options are saved for the next time you open the dialog. INSTALL AN EXCEL ADD-IN A lot of Excel procedures are packaged in Add-Ins. An Add-In is basically a workbook containing some code, that has some other unique properties. An add-in usually has some user interface elements, such as buttons on the ribbon or on context (right-click) menus, so you can access its features. The add-in workbook itself is invisible, PELTIER TECH EXCEL CHARTS AND PROGRAMMING BLOG The Peltier Tech Blog contains hundreds of articles and tutorials about Excel Charting and VBA, formatting, data preparation, andreporting.
COPY CHART TO NEW SHEET AND LINK TO DATA ON Now, copy the new data from “New Data”, and paste it on top of the data in “Original Data (2)”. The data arrangements are identical on the two worksheets, so we should be fine. I usually use Paste Special – Values, but it doesn’t seem to matter. Look, the chart now is showing the data we just pasted on top of the copied data: MULTIPLE TIME SERIES IN AN EXCEL CHART I recently showed several ways to display Multiple Series in One Excel Chart.The current article describes a special case of this, in which the X values are dates. Displaying multiple time series in an Excel chart is not difficult if all the series use the same dates, but it becomes a problem if the dates are different, for example, if the series show monthly and weekly values over the same ADD INDIVIDUAL TARGET LINES TO EACH CLUSTER IN A COLUMN In I Keep Saying, Use Bar Charts, Not Pies, I described the process of making better column charts to replace a horrendous pie chart.At one point I said “Let’s add a weighted average of all responses, using a set of horizontal lines.” I neglected to share the technique for adding individual target lines, though I’ve shown similar techniqueselsewhere.
EXTRACT LABELS FROM CATEGORY AXIS IN AN EXCEL CHART (VBA Question: Extract a Category Label for a Point. I came across a question in the Excel Reddit, Is there a way to select a chart series point and have the label name of that point be copied into a cell? Answer 1: Chart with Simple Axis. The short answer is the following function: you get .XValues property of the series, which is an array of category labels, and find the element of the array for PLOT BLANK CELLS AND #N/A IN EXCEL CHARTS Plot Blank Cells in Excel Charts. A common problem around web forums and blogs is how to plot blank cells in Excel charts. There is a lot of confusion about plotting of hidden and empty cells, about what constitutes a blank cell, and about various workarounds that purport to produce blank cells that will or will not be displayed in a chart. PELTIER TECH PRODUCTS AND SERVICES Peltier Technical Services, Inc. was founded in 2004 by Microsoft Excel MVP Jon Peltier. Peltier Tech provides consulting services, in the form of custom Excel development, using Excel worksheet, template, charting, and VBA technologies. PELTIER TECH WATERFALL CHART Waterfall and Stacked Waterfall charts are available in Standard and Advanced Editions of Peltier Tech Charts for Excel. Dual, Rotated, Split Bar, and Paired Waterfall charts are available in the Advanced Edition.. Waterfall Chart Dialog. The Waterfall Chart dialog is rich with options. Many of the options are saved for the next time you openthe dialog.
THE EXCEL CHART SERIES FORMULA Plot Order. Plot Order is a series number within the chart. This is always a number between 1 and the number of series in the chart. “Plot Order” is a bit of a misnomer, because regardless of this number, some types of series are plotted before others.CHART VBA EXAMPLES
And here is my simple chart VBA procedure for this: Sub ReverseDefaultColors() Dim iSrs As Long, nSrs As Long If ActiveChart Is Nothing Then MsgBox "Select a chart and try again.", vbExclamation, "No Active Chart" Else With ActiveChart nSrs = .SeriesCollection.Count ' work backwards from last series For iSrs = nSrs To 1 Step -1 Select Case nSrs - iSrs Case 0 ' last series .SeriesCollectionJON'S EXCEL PAGE
Jon's Excel Page . Microsoft Excel Tips and Tricks. Here are a few sample Excel files with some of the tricks I've learned over the years. This is still only about 10% of what I want to post. CHARTS FOR EXCEL UPGRADE CHECK YOUR DATA: IS IT NUMERIC OR TEXT STACKED COLUMN CHART WITH STACKED TRENDLINES A question on the SuperUser forum was titled Add multiple Utilization (percentage) trend lines to a Stacked Bar Chart with a count.The user wanted to see the trends at each level of a stacked column chart. Of course, Excel disables the trendline feature for stacked series in achart.
ADD A HORIZONTAL LINE TO AN EXCEL CHART A common task is to add a horizontal line to an Excel chart. The horizontal line may reference some target value or limit, and adding the horizontal line makes it easy to see where values are above and below this reference value. VB PASSWORD PROMPT WHEN CLOSING EXCEL PELTIER TECH PRODUCTS AND SERVICES Peltier Technical Services, Inc. was founded in 2004 by Microsoft Excel MVP Jon Peltier. Peltier Tech provides consulting services, in the form of custom Excel development, using Excel worksheet, template, charting, and VBA technologies. PELTIER TECH WATERFALL CHART Waterfall and Stacked Waterfall charts are available in Standard and Advanced Editions of Peltier Tech Charts for Excel. Dual, Rotated, Split Bar, and Paired Waterfall charts are available in the Advanced Edition.. Waterfall Chart Dialog. The Waterfall Chart dialog is rich with options. Many of the options are saved for the next time you openthe dialog.
THE EXCEL CHART SERIES FORMULA Plot Order. Plot Order is a series number within the chart. This is always a number between 1 and the number of series in the chart. “Plot Order” is a bit of a misnomer, because regardless of this number, some types of series are plotted before others.CHART VBA EXAMPLES
And here is my simple chart VBA procedure for this: Sub ReverseDefaultColors() Dim iSrs As Long, nSrs As Long If ActiveChart Is Nothing Then MsgBox "Select a chart and try again.", vbExclamation, "No Active Chart" Else With ActiveChart nSrs = .SeriesCollection.Count ' work backwards from last series For iSrs = nSrs To 1 Step -1 Select Case nSrs - iSrs Case 0 ' last series .SeriesCollectionJON'S EXCEL PAGE
Jon's Excel Page . Microsoft Excel Tips and Tricks. Here are a few sample Excel files with some of the tricks I've learned over the years. This is still only about 10% of what I want to post. CHARTS FOR EXCEL UPGRADE CHECK YOUR DATA: IS IT NUMERIC OR TEXT STACKED COLUMN CHART WITH STACKED TRENDLINES A question on the SuperUser forum was titled Add multiple Utilization (percentage) trend lines to a Stacked Bar Chart with a count.The user wanted to see the trends at each level of a stacked column chart. Of course, Excel disables the trendline feature for stacked series in achart.
ADD A HORIZONTAL LINE TO AN EXCEL CHART A common task is to add a horizontal line to an Excel chart. The horizontal line may reference some target value or limit, and adding the horizontal line makes it easy to see where values are above and below this reference value. VB PASSWORD PROMPT WHEN CLOSING EXCEL PELTIER TECH PRODUCTS AND SERVICES Peltier Technical Services, Inc. was founded in 2004 by Microsoft Excel MVP Jon Peltier. Peltier Tech provides consulting services, in the form of custom Excel development, using Excel worksheet, template, charting, and VBA technologies. PELTIER TECH WATERFALL CHART Waterfall and Stacked Waterfall charts are available in Standard and Advanced Editions of Peltier Tech Charts for Excel. Dual, Rotated, Split Bar, and Paired Waterfall charts are available in the Advanced Edition.. Waterfall Chart Dialog. The Waterfall Chart dialog is rich with options. Many of the options are saved for the next time you openthe dialog.
PELTIER TECH ROTATED WATERFALL CHART Waterfall and Stacked Waterfall charts are available in Standard and Advanced Editions of Peltier Tech Charts for Excel. Dual, Rotated, Split Bar, and Paired Waterfall charts are available in the Advanced Edition.. Rotated Waterfall Chart Dialog. The Rotated Waterfall Chart dialog contains several options. Many of the options are saved for the next time you open the dialog. INSTALL AN EXCEL ADD-IN A lot of Excel procedures are packaged in Add-Ins. An Add-In is basically a workbook containing some code, that has some other unique properties. An add-in usually has some user interface elements, such as buttons on the ribbon or on context (right-click) menus, so you can access its features. The add-in workbook itself is invisible, GANTT CHART WITH NICE DATE AXIS Simple Gantt Charts. A year ago I wrote an article about Gantt Charts in Microsoft Excel.It was pretty comprehensive, showing how to make simple Gantt charts using worksheet conditional formatting and using regular Excel stacked bar charts, and it covered some advanced techniques for adding embellishments, such as a reference line for a given date, multiple bars showing percent CONSOLIDATE TEXT DATA FOR EXCEL CHARTING Pivot Table Approach. Insert a header, "Responses", at the top of the column of data. Select the range and create a pivot table (Data menu). In the sheet shown below left, the pivot table is located in cell C1 of the worksheet containing the data. MULTIPLE TIME SERIES IN AN EXCEL CHART I recently showed several ways to display Multiple Series in One Excel Chart.The current article describes a special case of this, in which the X values are dates. Displaying multiple time series in an Excel chart is not difficult if all the series use the same dates, but it becomes a problem if the dates are different, for example, if the series show monthly and weekly values over the same PIVOT CHART FORMATTING CHANGES WHEN FILTERED Even though the source range of the PivotChart remains the same, the formatting of the chart changes. This is an odd behavior until you understand that the formatting properties are saved and remembered byExcel.
ADD ONE TRENDLINE FOR MULTIPLE SERIES Note: In a recent version of Excel (I don’t recall if it was 2013 or 2007), trendlines changed from black to the color of the points, which was good for visibility, and they also became a dotted line, which wasbad for visibility.
COPY CHART TO NEW SHEET AND LINK TO DATA ON It's not easy to copy a chart onto a new sheet and have it link to data on the new sheet. Here is an easier way to link the chart to thenew data.
PELTIER TECH PRODUCTS AND SERVICES Peltier Technical Services, Inc. was founded in 2004 by Microsoft Excel MVP Jon Peltier. Peltier Tech provides consulting services, in the form of custom Excel development, using Excel worksheet, template, charting, and VBA technologies. Peltier Tech has developed Peltier Tech Charts for Excel, an extensive Excel add-in that providesnumerous
CHART VBA EXAMPLES
Here is the desired outcome: And here’s the chart VBA example: Sub ColorGreenToRed () Dim iSrs As Long, nSrs As Long If ActiveChart Is Nothing Then MsgBox "Select a chart and try again.", vbExclamation, "No Active Chart" Else With ActiveChart nSrs = .SeriesCollection.Count For iSrs = 1 To nSrs ' only format series whose names are found Select THE EXCEL CHART SERIES FORMULA Plot Order. Plot Order is a series number within the chart. This is always a number between 1 and the number of series in the chart. “Plot Order” is a bit of a misnomer, because regardless of this number, some types of series are plotted before others. CHECK YOUR DATA: IS IT NUMERIC OR TEXT GANTT CHART WITH NICE DATE AXIS Format the secondary vertical axis, on the right edge of the chart. Under Horizontal Axis Crosses, choose Automatic. Now double-click on the bottom horizontal date axis to format it. Make sure the minimum and maximum are correct, change Base Units to Days, and change MajorUnits to 1 Month.
CONTROL EXCEL BUBBLE CHART BUBBLE SIZES. In Microsoft Excel's bubble charts, bubble sizes are fixed according to the largest bubble in the chart. You can fine tune this maximum size by double clicking on any series, and on the Format Series dialog, Options tab, select a default multiplier. I like to use 200% to get large bubbles. STACKED COLUMN CHART WITH STACKED TRENDLINES Using Actual Chart Trendlines. Here is the data. It plots items 1 through 4 over the years 2010 to 2016. I added four series, Cum1 through Cum4: Cum 1 is simply Item1, Cum2 is Item1 + Item2, etc. The CUM series then plot a line at each level of the stacked column chart: Cum1 plots at the top of Item1, Cum2 plots at the top of Item2, whichis
ADD INDIVIDUAL TARGET LINES TO EACH CLUSTER IN A COLUMN In I Keep Saying, Use Bar Charts, Not Pies, I described the process of making better column charts to replace a horrendous pie chart.At one point I said “Let’s add a weighted average of all responses, using a set of horizontal lines.” I neglected to share the technique for adding individual target lines, though I’ve shown similar techniqueselsewhere.
WHY ARE MY EXCEL BAR CHART CATEGORIES BACKWARDS? I came across a blog post called Is it just me?(software defaults), which asks the age-old question, Why Are My Excel Bar Chart Categories Backwards? The post was in a new blog by Alex Kerin of Data Driven Consulting.Alex works on projects in analytics and dashboarding. I have been asked this question a number of times, and being a founding member of Chart Busters, of course I know the REPURPOSE THE RED X CLOSE BUTTON ON A VBA The default behavior of the red X in the top right corner is to unload the form, that is, clear it from memory. This is not good, because the calling code will be unable to communicate with the form. This form has code that repurposes the red X, turning a click on that button to PELTIER TECH PRODUCTS AND SERVICES Peltier Technical Services, Inc. was founded in 2004 by Microsoft Excel MVP Jon Peltier. Peltier Tech provides consulting services, in the form of custom Excel development, using Excel worksheet, template, charting, and VBA technologies. Peltier Tech has developed Peltier Tech Charts for Excel, an extensive Excel add-in that providesnumerous
CHART VBA EXAMPLES
Here is the desired outcome: And here’s the chart VBA example: Sub ColorGreenToRed () Dim iSrs As Long, nSrs As Long If ActiveChart Is Nothing Then MsgBox "Select a chart and try again.", vbExclamation, "No Active Chart" Else With ActiveChart nSrs = .SeriesCollection.Count For iSrs = 1 To nSrs ' only format series whose names are found Select THE EXCEL CHART SERIES FORMULA Plot Order. Plot Order is a series number within the chart. This is always a number between 1 and the number of series in the chart. “Plot Order” is a bit of a misnomer, because regardless of this number, some types of series are plotted before others. CHECK YOUR DATA: IS IT NUMERIC OR TEXT GANTT CHART WITH NICE DATE AXIS Format the secondary vertical axis, on the right edge of the chart. Under Horizontal Axis Crosses, choose Automatic. Now double-click on the bottom horizontal date axis to format it. Make sure the minimum and maximum are correct, change Base Units to Days, and change MajorUnits to 1 Month.
CONTROL EXCEL BUBBLE CHART BUBBLE SIZES. In Microsoft Excel's bubble charts, bubble sizes are fixed according to the largest bubble in the chart. You can fine tune this maximum size by double clicking on any series, and on the Format Series dialog, Options tab, select a default multiplier. I like to use 200% to get large bubbles. STACKED COLUMN CHART WITH STACKED TRENDLINES Using Actual Chart Trendlines. Here is the data. It plots items 1 through 4 over the years 2010 to 2016. I added four series, Cum1 through Cum4: Cum 1 is simply Item1, Cum2 is Item1 + Item2, etc. The CUM series then plot a line at each level of the stacked column chart: Cum1 plots at the top of Item1, Cum2 plots at the top of Item2, whichis
ADD INDIVIDUAL TARGET LINES TO EACH CLUSTER IN A COLUMN In I Keep Saying, Use Bar Charts, Not Pies, I described the process of making better column charts to replace a horrendous pie chart.At one point I said “Let’s add a weighted average of all responses, using a set of horizontal lines.” I neglected to share the technique for adding individual target lines, though I’ve shown similar techniqueselsewhere.
WHY ARE MY EXCEL BAR CHART CATEGORIES BACKWARDS? I came across a blog post called Is it just me?(software defaults), which asks the age-old question, Why Are My Excel Bar Chart Categories Backwards? The post was in a new blog by Alex Kerin of Data Driven Consulting.Alex works on projects in analytics and dashboarding. I have been asked this question a number of times, and being a founding member of Chart Busters, of course I know the REPURPOSE THE RED X CLOSE BUTTON ON A VBA The default behavior of the red X in the top right corner is to unload the form, that is, clear it from memory. This is not good, because the calling code will be unable to communicate with the form. This form has code that repurposes the red X, turning a click on that button to PELTIER TECH PRODUCTS AND SERVICES Peltier Technical Services, Inc. was founded in 2004 by Microsoft Excel MVP Jon Peltier. Peltier Tech provides consulting services, in the form of custom Excel development, using Excel worksheet, template, charting, and VBA technologies. Peltier Tech has developed Peltier Tech Charts for Excel, an extensive Excel add-in that providesnumerous
CHARTS FOR EXCEL UPGRADE I wrote about this kind of chart many years ago in Stacked Charts With Vertical Separation.. Another new chart type is a Step Chart, which uses data points to describe horizontal and vertical steps, rather than connecting points directly. ADD A HORIZONTAL LINE TO AN EXCEL CHART Below is the data for our horizontal line, which will start at 1 (the first category number of 1) and end at 5 (the last category number of 5), without the half-category cushion at either end. Copy the data, select the chart, and Paste Special to add the data as a new series. Right click on the added series, and change its chart type to XY CONNECT TWO XY SERIES This is pretty easy by adding a third XY series that includes all of the data for the original two series. The data has to be properly sequenced, with a point from series A, then a point from series B, then a blank row, then the next point from series A, etc. While working on this data arrangement, I realized it was what I needed toenhance a
PLOT BLANK CELLS AND #N/A IN EXCEL CHARTS Plot Blank Cells in Excel Charts. A common problem around web forums and blogs is how to plot blank cells in Excel charts. There is a lot of confusion about plotting of hidden and empty cells, about what constitutes a blank cell, and about various workarounds that purport to produce blank cells that will or will not be displayed in a chart. PIVOT CHART FORMATTING CHANGES WHEN FILTERED Even though the source range of the PivotChart remains the same, the formatting of the chart changes. This is an odd behavior until you understand that the formatting properties are saved and remembered byExcel.
EXCEL PLOTTED MY BAR CHART UPSIDE-DOWN If they sort the data in reverse order, the chart’s categories look right, but now the worksheet is upside-down, and the series are still in the wrong order. The reason for this arrangement is logical, once you figure it out. To illustrate, let’s start with this data. Yes, the data in column A is mixed up. Instead of 1-2-3-4, it goes 1-3-2 BROKEN Y AXIS IN AN EXCEL CHART If you’re looking for a tutorial on breaking an axis scale, you won’t find it here. Instead you’ll read why breaking an axis is a bad idea, and you’ll get a tutorial in Panel Charts, which are a more effective (and easier) means to show your data. WHY ARE MY EXCEL BAR CHART CATEGORIES BACKWARDS? I came across a blog post called Is it just me?(software defaults), which asks the age-old question, Why Are My Excel Bar Chart Categories Backwards? The post was in a new blog by Alex Kerin of Data Driven Consulting.Alex works on projects in analytics and dashboarding. I have been asked this question a number of times, and being a founding member of Chart Busters, of course I know the REMOVING RECENT COLORS FROM MICROSOFT EXCEL Save the xml file, and copy it back into the “xl” folder in the zip file. Delete the “.zip” extension you added to the Office file earlier, so it is left with its original Office extension (“.xlsm” for our workbook), and reopen the file. The screenshot below shows the three colored cells, but only the two non-pink Recent Colors in PELTIER TECH PRODUCTS AND SERVICES Peltier Technical Services, Inc. was founded in 2004 by Microsoft Excel MVP Jon Peltier. Peltier Tech provides consulting services, in the form of custom Excel development, using Excel worksheet, template, charting, and VBA technologies. Peltier Tech has developed Peltier Tech Charts for Excel, an extensive Excel add-in that providesnumerous
THE EXCEL CHART SERIES FORMULA Plot Order. Plot Order is a series number within the chart. This is always a number between 1 and the number of series in the chart. “Plot Order” is a bit of a misnomer, because regardless of this number, some types of series are plotted before others. ADD A HORIZONTAL LINE TO AN EXCEL CHART Below is the data for our horizontal line, which will start at 1 (the first category number of 1) and end at 5 (the last category number of 5), without the half-category cushion at either end. Copy the data, select the chart, and Paste Special to add the data as a new series. Right click on the added series, and change its chart type to XY CHART A WIDE RANGE OF VALUES A common approach to chart a wide range of values is to break the axis, plotting small numbers below the break and large numbers above the break. An advantage here is that it generally uses a linear scale. A disadvantage is that it distorts data, and doesn’t really give a sense for the differences in value on either side of the break. CHECK YOUR DATA: IS IT NUMERIC OR TEXTCHART VBA EXAMPLES
Here is the desired outcome: And here’s the chart VBA example: Sub ColorGreenToRed () Dim iSrs As Long, nSrs As Long If ActiveChart Is Nothing Then MsgBox "Select a chart and try again.", vbExclamation, "No Active Chart" Else With ActiveChart nSrs = .SeriesCollection.Count For iSrs = 1 To nSrs ' only format series whose names are found SelectJON'S EXCEL PAGE
Jon's Excel Page. Microsoft Excel Tips and Tricks. Here are a few sample Excel files with some of the tricks I've learned over the years. This is still only about 10% MULTIPLE SERIES IN ONE EXCEL CHART Select Series Data: Right click the chart and choose Select Data, or click on Select Data in the ribbon, to bring up the Select Data Source dialog. You can’t edit the Chart Data Range to include multiple blocks of data. However, you can add data by clicking the Add button above the list of series (which includes just the first series). PLOT BLANK CELLS AND #N/A IN EXCEL CHARTS Plot Blank Cells in Excel Charts. A common problem around web forums and blogs is how to plot blank cells in Excel charts. There is a lot of confusion about plotting of hidden and empty cells, about what constitutes a blank cell, and about various workarounds that purport to produce blank cells that will or will not be displayed in a chart. WHY ARE MY EXCEL BAR CHART CATEGORIES BACKWARDS? I came across a blog post called Is it just me?(software defaults), which asks the age-old question, Why Are My Excel Bar Chart Categories Backwards? The post was in a new blog by Alex Kerin of Data Driven Consulting.Alex works on projects in analytics and dashboarding. I have been asked this question a number of times, and being a founding member of Chart Busters, of course I know the PELTIER TECH PRODUCTS AND SERVICES Peltier Technical Services, Inc. was founded in 2004 by Microsoft Excel MVP Jon Peltier. Peltier Tech provides consulting services, in the form of custom Excel development, using Excel worksheet, template, charting, and VBA technologies. Peltier Tech has developed Peltier Tech Charts for Excel, an extensive Excel add-in that providesnumerous
THE EXCEL CHART SERIES FORMULA Plot Order. Plot Order is a series number within the chart. This is always a number between 1 and the number of series in the chart. “Plot Order” is a bit of a misnomer, because regardless of this number, some types of series are plotted before others. ADD A HORIZONTAL LINE TO AN EXCEL CHART Below is the data for our horizontal line, which will start at 1 (the first category number of 1) and end at 5 (the last category number of 5), without the half-category cushion at either end. Copy the data, select the chart, and Paste Special to add the data as a new series. Right click on the added series, and change its chart type to XY CHART A WIDE RANGE OF VALUES A common approach to chart a wide range of values is to break the axis, plotting small numbers below the break and large numbers above the break. An advantage here is that it generally uses a linear scale. A disadvantage is that it distorts data, and doesn’t really give a sense for the differences in value on either side of the break. CHECK YOUR DATA: IS IT NUMERIC OR TEXTCHART VBA EXAMPLES
Here is the desired outcome: And here’s the chart VBA example: Sub ColorGreenToRed () Dim iSrs As Long, nSrs As Long If ActiveChart Is Nothing Then MsgBox "Select a chart and try again.", vbExclamation, "No Active Chart" Else With ActiveChart nSrs = .SeriesCollection.Count For iSrs = 1 To nSrs ' only format series whose names are found SelectJON'S EXCEL PAGE
Jon's Excel Page. Microsoft Excel Tips and Tricks. Here are a few sample Excel files with some of the tricks I've learned over the years. This is still only about 10% MULTIPLE SERIES IN ONE EXCEL CHART Select Series Data: Right click the chart and choose Select Data, or click on Select Data in the ribbon, to bring up the Select Data Source dialog. You can’t edit the Chart Data Range to include multiple blocks of data. However, you can add data by clicking the Add button above the list of series (which includes just the first series). PLOT BLANK CELLS AND #N/A IN EXCEL CHARTS Plot Blank Cells in Excel Charts. A common problem around web forums and blogs is how to plot blank cells in Excel charts. There is a lot of confusion about plotting of hidden and empty cells, about what constitutes a blank cell, and about various workarounds that purport to produce blank cells that will or will not be displayed in a chart. WHY ARE MY EXCEL BAR CHART CATEGORIES BACKWARDS? I came across a blog post called Is it just me?(software defaults), which asks the age-old question, Why Are My Excel Bar Chart Categories Backwards? The post was in a new blog by Alex Kerin of Data Driven Consulting.Alex works on projects in analytics and dashboarding. I have been asked this question a number of times, and being a founding member of Chart Busters, of course I know the CHART WITH ACTUAL SOLID LINES AND PROJECTED The better way to make the chart is to start with better data. Put the Actual data in one column and the Projected data into the next. Repeat the last Actual value in the Projected column. Now plot the data. Above I simply jumped ahead to a scatter chart without explanation, but anyone who knows me knows I love to explain stuff. GANTT CHART WITH NICE DATE AXIS Simple Gantt Charts. A year ago I wrote an article about Gantt Charts in Microsoft Excel.It was pretty comprehensive, showing how to make simple Gantt charts using worksheet conditional formatting and using regular Excel stacked bar charts, and it covered some advanced techniques for adding embellishments, such as a reference line for a given date, multiple bars showing percent LINE CHARTS VS. XY CHARTS The line chart places the points uniformly along the X axis, using the numerical values as non-numerical text labels. The XY chart plots the points according to actual X values. Text X Labels. Below are two charts created using text labels as X values, a line chart (left) and an XY chart (right). The line chart places the points uniformly along MULTIPLE SERIES IN ONE EXCEL CHART Select Series Data: Right click the chart and choose Select Data, or click on Select Data in the ribbon, to bring up the Select Data Source dialog. You can’t edit the Chart Data Range to include multiple blocks of data. However, you can add data by clicking the Add button above the list of series (which includes just the first series). PIVOT CHART FORMATTING CHANGES WHEN FILTERED Even though the source range of the PivotChart remains the same, the formatting of the chart changes. This is an odd behavior until you understand that the formatting properties are saved and remembered byExcel.
COPY CHART TO NEW SHEET AND LINK TO DATA ON Now, copy the new data from “New Data”, and paste it on top of the data in “Original Data (2)”. The data arrangements are identical on the two worksheets, so we should be fine. I usually use Paste Special – Values, but it doesn’t seem to matter. Look, the chart now is showing the data we just pasted on top of the copied data: EXCEL PLOTTED MY BAR CHART UPSIDE-DOWN If they sort the data in reverse order, the chart’s categories look right, but now the worksheet is upside-down, and the series are still in the wrong order. The reason for this arrangement is logical, once you figure it out. To illustrate, let’s start with this data. Yes, the data in column A is mixed up. Instead of 1-2-3-4, it goes 1-3-2 HOW TO: ASSIGN A MACRO TO AN ACTIVEX CONTROL In the latest post, I showed how to assign a macro to a Forms menu control or other shape on a sheet. Macros can also be run from the ActiveX controls on the Control Toolbox. We’ll use this simple macro for this example. Sub HelloWorld() MsgBox "Hello, World! ", PLOT BLANK CELLS AND #N/A IN EXCEL CHARTS Plot Blank Cells in Excel Charts. A common problem around web forums and blogs is how to plot blank cells in Excel charts. There is a lot of confusion about plotting of hidden and empty cells, about what constitutes a blank cell, and about various workarounds that purport to produce blank cells that will or will not be displayed in a chart. CUSTOM ERROR BARS IN EXCEL CHARTS Jon. Simple request. I want to put 95% confidence limits around some bar chart means. I cannot believe excel (2007) needs me to calculate the Confidence Limits * Skip to primary navigation* Skip to content
* Skip to primary sidebarPELTIER TECH BLOG
Peltier Tech Excel Charts and Programming Blog Search this website Menu * Peltier Tech Consulting * Excel Chart Add-Ins * Advanced Excel Training* Email Feed
* RSS
ERROR BAR GANTT CHART Thursday, May 30, 2019 by Jon PeltierLeave a Comment
Thursday, May 30, 2019 by Jon Peltier Peltier Technical Services, Inc., Copyright © 2019 , All rights reserved. FLOATING BARS AND GANTT CHARTS Gantt charts are a special kind of floating bar chart used in scheduling and program management. A list of tasks or activities is plotted along the left hand axis, and dates along the bottom axis. Floating horizontal bars indicate when tasks begin and end, and which tasks are in progress at any giventime.
In Floating Bars in Excel ChartsI showed several
ways to produce vertical or horizontal floating bars in Excel charts, including stacked bar and column charts, XY Scatter chart line segments formatted with thick lines, and error bars formatted withthick lines.
My article Custom Error Bars in Exceldescribes
how to customize error bar values, and I’ve used the error bar-floating bar technique in many ways, such as the recent article that showcased Low-Medium-High charts.
In Gantt Charts in Microsoft Excel , I shared a couple ways to make Gantt charts, including the use of Conditional Formatting to shade cells in the worksheet (which doesn’t actually produce a chart), and the use of floating bar charts to produce the timelinebars.
This tutorial will show how to use custom error bars to create a Gantt chart in Microsoft Excel. It’s also a case study of features in Excel charts, and how to go about designing your own non-native chart in Excel. You’ll see me encounter a few false starts, not unusual as I zero in on a working protocol. XY SCATTER ERROR BAR GANTT CHART My first approach with almost any non-native custom chart starts with an XY Scatter chart, and make adjustments from there. My plan is to use XY points as the endpoints of the timeline bars, applying negative horizontal error bars to the points, and adding data labels with task names to the right of the points. Below is the minimal data needed for the chart I envisioned, in A1:D9. I need columns for Task (a label), End Date (the X values of my points), Height (the Y values of my points), and Duration (the lengths of the timeline bars). I have eight tasks, which I want vertically distributed evenly in the chart. This formula in cell C2, copied into C3:C9, does the trick: =1-(ROW()-0.5-ROW(E$1))/COUNTA($A$2:$A$9) I have included a small satellite data region in F1:G4, with two dates in column F (the desired endpoints of my horizontal axis), and the same dates in column G formatted as General. Cell G4 shows the difference between the axis endpoints divided by 3, which I’ll use as the tick spacing of the horizontal axis. You need to know the numeric values of any dates if you want to manually scale an axis, because XY charts don’t accept dates in the Format Axis task pane. I selected the End Date and Height data (B1:C9) and inserted an XY Scatter chart (below left). I have already added the error bars to the XY Chart series. You can see that the dates are a mess, with the labels so close that they overlap. The vertical axis scale was selected to show tick marks and labels between the horizontal errorbars.
The chart below right shows the following improvements (I’ll share the exact steps later, when I’ve developed a protocol I like): * Deleted vertical error bars * Added custom negative values only to horizontal error bars * Formatted the horizontal error bars as thick semitransparent lines * Added Task labels to XY Scatter points * Formatted the minimum, maximum, and major unit of the horizontal date axis, using the satellite data I calculated in G2:G4, above. I haven’t finished cleaning up the chart, but you can easily see the problem. Using an XY chart, I can’t get a nice axis scale with a tick mark and label at the first of each month: my axis has 31-May instead of 1-June. In fact, I recently wrote a tutorial, Gantt Chartwith Nice Date Axis
, which presented
an intricate adjustment to my original Gantt chart that avoids thisissue.
Let’s try using a Line chart directly, with a nice date scale axis which inherently provides a nice axis scale. LINE CHART ERROR BAR GANTT CHART I applied the same data as above to a Line chart. I did not need the extra “satellite” data, because I can enter dates into a Line chart’s Format Axis task pane. I selected the End Date and Height data (B1:C9) and inserted a Line chart (below left). I have already added the error bars to the Line chart series. The dates only include the End Dates from the table, not the starting date of 1-Apr. As above, the vertical axis scale shows tick marks and labels between the horizontal error bars. I’ve applied the desired minimum date to the chart below right. I’ve also highlighted why I can’t use this as my error bar Gantt chart: a Line chart series can only have vertical, not horizontal,error bars.
Let’s use a combination chart, with a Line chart for the date axis scale and an XY Scatter chart for the horizontal error bars. COMBINATION ERROR BAR GANTT CHART Here is the data range for a combination chart. The XY data is in A1:D9, and the Line chart data is in F1:G3. I selected this Line chart data and inserted a Line chart, below left. The date axis spans the two dates in the data range. I copied the End Date and Height data (B1:C9), selected the chart, and used Paste Special to add the data to the chart as a new series by columns, using the first row for series names and the first column for X values (below right). The XY series only shows two points, but that’s okay, because it’s still a Line chart series, and uses the X values for the existing Line series, which only has two points. I converted the added series to an XY Chart type, then added and formatted the horizontal error bars(below left).
Oh yeah, then I moved the added series, now XY Chart type, back to the primary axis (below right). The XY Chart series is plotted at the intended dates, but again, it is reduced to two points. So the problem is that even if the second series doesn’t use the same dates as the first Line chart series, it is limited to having no more points than the first series. So let’s see if we can pad the Line chart series with extra points. SIMPLE COMBINATION ERROR BAR GANTT CHART THAT WORKS You can safely use the following protocol to make a simple Gantt chart. I’ve included all the little steps that you need to follow. Here is the adjusted data range for a combination chart. The XY data (Task Name, End Date, Height, and Duration) is in A1:D9, and the Line chart data is in F1:G11. Notice the line chart data in column F now has ten dates, more than needed to plot the endpoints, and more than the number of XY points we will have. The dates between Start and Endare evenly spaced.
Select the Line chart data in F1:G11 and insert a Line chart, below left. The date axis spans the two dates in the data range, and the points are uniformly spaced. Copy the End Date and Height data (B1:C9), select the chart, and use Paste Special (go to the Home tab of Excel’s ribbon, click the Paste button dropdown, select the last item on the menu) to add the data to the chart as a new series by columns, using the first row for series names and the first column for X values (below right). All of the XY points are there, but spaced according to the Line chart dates from the first series, since the new series is not yet converted to an XY Scatter type. Easy enough, right click on the added series, choose Change Series Chart Type from the dropdown, and select an XY Scatter type (below left). In the same Change Series Chart Type dialog, or in the Format Series task pane, reassign the XY series to the primary axis (below right). Now the points follow the End Date data. Select the added series, click the plus icon next to the chart, and check Error Bars (below left). Select the vertical error bars, and press Delete. Then select the horizontal error bars, press Ctrl+1, and in the Format Error Bars task pane, under Error Amount, select Custom and click the Specify Value button. Enter 0 (zero) for Positive, and select the Duration range (D2:D9) for Negative (below right). Format the error bar lines. Choose No Cap, then under the paint can, choose a line color and thickness (below left). I used the same orange color as the data points use, with 50% transparency, and with a thickness of 9 points. Add data labels to the right of the XY markers. I used the Value from Cells option, selecting the Task names in A2:A9 (below right). Format the Line and XY series to show no markers and no lines, select None for the vertical axis labels, and remove the gridlines, and the error bar Gantt chart is finished. LABEL VARIATION IN ABOVE GANTT CHART You want the Task labels before the bars, not after them? You can use the same protocol, replacing Start dates for End dates, and switching the positions of the error bars and data labels. The adjusted data range for a combination chart is shown below. The XY data (Task Name, Start Date, Height, and Duration) is in A1:D9, and the Line chart data is in F1:G11. Notice the line chart data now has ten dates, more than needed to plot the endpoints, and more than the number of XY points we will have. The dates between Start and End areevenly spaced.
Select the Line chart data in F1:G11 and insert a Line chart, below left. The date axis spans the two dates in the data range, and the points are uniformly spaced. Copy the Start Date and Height data (B1:C9), select the chart, and use Paste Special to add the data to the chart as a new series by columns, using the first row for series names and the first column for X values(below right).
Right click on the added series, choose Change Series Chart Type from the dropdown, and select an XY Scatter type (below left). In the same dialog, or from the Format Series task pane, reassign the XY series to the primary axis (below right). Select the added series, click the plus icon next to the chart, and check Error Bars (below left). Select the vertical error bars, and press Delete. Then select the horizontal error bars, press Ctrl+1, and in the Format Error Bars task pane, under Error Amount, select Custom then click the Specify Value button. Select the Duration range (D2:D9) for Positive, and enter 0 (zero) for Negative. Choose No Cap for the error bars, then under the paint can, choose a line color and thickness (below left). I used the same orange color as the data points use, with 50% transparency, and with a line thicknessof 9 points.
Add data labels to the left of the XY markers. I used the Value from Cells option, selecting the Task names in A2:A9 (below right). A little clean-up, and your simple error bar Gantt chart is done. ADVANCED COMBINATION ERROR BAR GANTT CHART Let’s extend out Gantt Chart methodology to show more of our project data. We’ll indicate percent complete for each task with a data label, we’ll split each horizontal bar into a completed portion in blue and an incomplete portion in orange, and we’ll use distinct milestone markers for completed and incomplete tasks. Our advanced Gantt chart data range is much larger than either simple range above. Task names are in column A. The next three columns are Start Date, Duration, and End Date, related to each other by addition or subtraction. Percent Complete is in column E. The next two columns are the Done and Not Done portions of Duration, defined by theseformulas:
=E2*C2
=(1-E2)*C2
Then come the Complete and Incomplete End Dates, defined by=IF(E2=1,D2,NA())
=IF(E2<1,D2,NA())
Finally we have Height in column J, defined as before: =1-(ROW()-0.5-ROW(J$1))/COUNTA($A$2:$A$9) In a separate range we have data for the Line chart in L1:M11. Select the Date and Axis data in L1:M11, and insert a line chart(below left).
Select the Start Date data (B1:B9), then hold Ctrl while you select the Height data (J1:J9), press Ctrl+C to copy, then select the chart, and use Paste Special to add the data as a new series in columns, series names in first row, categories (X values) in first column (below right). I’ve added a legend temporarily, to help keep track of all the series we’re adding; I’ve also changed the series name from cell J1 to cell B1. Right click on the added series, choose Change Series Chart Type from the dropdown, and select an XY Scatter type (below left). In the same dialog, or from the Format Series task pane, reassign the XY series to the primary axis (below right). Select the Complete dates (H1:H9), then hold Ctrl while you select the Height data (J1:J9), press Ctrl+C to copy, then select the chart, and use Paste Special to add the data as a new series in columns, series names in first row, categories (X values) in first column (below left). I’ve changed the series name from cell J1 to cell H1. Select the Incomplete dates (I1:I9), then hold Ctrl while you select the Height data (J1:J9), press Ctrl+C to copy, then select the chart, and use Paste Special to add the data as a new series in columns, series names in first row, categories (X values) in first column (below right). I’ve changed the series name from cell J1 to cell I1. Eventually the markers for Complete and Incomplete will become our milestone markers. We will label the Complete markers “Complete” (conveniently the series name), and the Incomplete markers with the percent complete. We will extend the blue Done timeline bars from Start Date towards the right (positive error bars), and the orange Not Done timeline bars from Incomplete to the left (negative error bars). Then we’ll label the Tasks in one of two ways. Select the Start Date series, click the plus icon next to the chart, and check Error Bars. Repeat for the Incomplete series. I have already colored the error bar lines blue and orange (below left). Select the vertical error bars for Start Date, and press Delete. Repeat for Incomplete (below right). Select the horizontal error bars for Start Date, press Ctrl+1, and in the Format Error Bars task pane, under Error Amount, select Custom and click the Specify Value button. Select the Done range (F2:F9) for Positive, and enter 0 (zero) for Negative. Repeat with the horizontal error bars for Incomplete, entering 0 for Positive and the Not Done range (G2:G9) for Negative (below left). Format the lines for both sets of horizontal error bars. I used Blue for the Start Date error bars and orange for Incomplete, with 50% transparency for both colors, and a line thickness of 9 points forboth (below right).
Time to perform a little clean-up. Format each series too use no lines. Format Axis and Start Date to use no markers. Format the milestone markers for Complete and Incomplete as desired. Format the vertical axis, and choose None for the labels. Delete the gridlines(below left).
Add data labels to the Complete series, and choose the option Label Contains Series Name. Add data labels to the Incomplete series, and choose the Label Contains Value From Cells, and select the % Complete range (E2:E9) as the source for the labels. I’ve used a font color to match the bar and marker color scheme (below right). Now decide if you want the Task labels at the start of each bar (below left) or along the vertical axis at the left hand edge of the chart(below right)
Task labels at the beginning of the bars require only one additional step. Add data labels to the left of the Start Date points (the markers are no longer visible, but you can point and click at the beginning of the bars to select them). Use Label Contains Value From Cells, selecting the Task names in A2:A9 (below left). Task labels along the left edge of the chart requires several more steps. First, select the Task Names (and header) in A1:A9, hold Ctrl while selecting most of the last Axis column (M1:M9), press Ctrl+C to copy, select the chart and use Paste Special to add the data as a new series in columns, etc. The points don’t appear, but they do exist(below right)
Right click on any series, select Change Series Chart Type, find the last added series, and change it to a Bar chart. The points still don’t appear, but you should see a secondary horizontal axis along the top of the chart (below left). Click the plus icon next to the chart, click the right-pointing triangle next to Axes, and check Secondary Vertical Axis. The new axis appears along the right edge of the chart, with Task 1 through Task 8 going bottom to top instead of top to bottom. The added bars now appear as thinner blue bars across the chart (below right). Format the secondary vertical axis, and choose Categories in ReverseOrder (below left).
Format the secondary horizontal axis (the numeric axis, not the date axis), and select Vertical Axis Crosses Automatic (below right). Select the secondary horizontal axis and click Delete (below left). Perform any additional clean-up, such as using no line for the vertical axis (below right). POSTED: Thursday, May 30th, 2019 under Chart Types.
TAGS: error bars , FloatingBars , Gantt Chart
.
COMMENTS:
none
Filed Under: Chart TypesTagged With: error
bars , Floating Bars, Gantt Chart
LOW-MEDIUM-HIGH CHARTS Tuesday, May 28, 2019 by Jon Peltier2 Comments
Tuesday, May 28, 2019 by Jon Peltier Peltier Technical Services, Inc., Copyright © 2019 , All rights reserved. LOW-MEDIUM-HIGH CHARTS When comparing values, it is often desirable to compare ranges of values. You would use some kind of chart to plot Low-Medium-High values. Low and High typically refer to minimum and maximum values, and Medium might be an average or medium value. You could use a line to denote the range between Low and High… … or you might prefer a thicker bar… This article shows an easy way to set up such a chart, using error bars to construct the lines or bars. This article uses techniques in two of my previous tutorials, Custom Error Bars in Excel Chartsand
Floating Bars in Excel Charts. It also shows
ideas I shared when answering Making horizontal max-min-average chartin Excel
in Stack Overflow.
You can download the companion workbook, Error Bars for HorizontalLines and Bars.xlsx
.
LOW-MEDIUM-HIGH CHART WITH HORIZONTAL LINES The simple Low-Medium-High chart with lines is an XY Scatter chart with horizontal error bars. The data is simple: two columns with X (Medium) and Y values, two columns with Low and High values, and two columns with error bar lengths (from Low to Medium and from Medium to High). The error bar columns contain subtraction formulas to compute the values, but if you know the error bar lengths, you don’t need Low and High in the data range. Selecting the X and Y columns of data, and insert an XY Scatter chart. Click the plus icon next to the chart, and add error bars. Excel adds X and Y error bars with default values. Select the vertical error bars, and click Delete. Select the horizontal error bars, use the Ctrl+1 shortcut to open the Format Error Bars task pane, and under Error Amount, choose Custom, and click Specify Value. The dialog shown below left appears (yes, the range boxes are really that narrow!), with default ={1} for the custom values. Select the ={1} in the first box, delete it, and select the range with the positive error bar values. If you don’t delete the default value before selecting the range, Excel may try to add the range to the {1} already in the box, and give you an error: ={1}+Sheet1!$F$2:$F$5 Repeat for the second box and the range with the negative error bar values (below right). Here is the chart with our custom error bar values. Format the error bars. Here I selected No Cap for the error bar’s end style, gave the line the same color as the XY markers, chose a dash style, and used the diamond-shaped option for begin and end arrowtypes.
Finally, click the plus icon next to the chart, and check the box to add data labels. Format the data labels so they are above the points, and so they show the X values, not the default Y values. LOW-MEDIUM-HIGH CHART WITH ONE-COLORED HORIZONTAL BARS For a simple Low-Medium-High chart with one color bars, follow the same protocol as above, up to where you have applied custom error bar values. Select the markers, press the Ctrl+1 shortcut to open the Format Series task pane. Under the paint can icon, choose Marker > Marker Options > None. Format the error bars. Here I selected No Cap for the error bar’s end style, gave the line the first accent color in Excel’s default color scheme, gave it a transparency of 50% so the gridlines show through, and set the line width to 12 points. Finally, click the plus icon next to the chart, and check the box to add data labels. Format the data labels so they are centered on the points, and so they show the X values instead of the default Y values. Unfortunately, all of the error bars have to be formatted the same, so you can’t do it so simply if you want different color bars. Read on for the procedure to create multi-colored bars. LOW-MEDIUM-HIGH CHART WITH MULTI-COLORED HORIZONTAL BARS In order to get multi-colored bars in the chart, you need multiple sets of error bars, which means multiple series in the chart. The data range is like that used above, but there are separate columns for Y values for each individual color of error bars. The other columns can be shared amonf the different series. Select the columns with X and all Y values and insert an XY Scatter chart. Make sure Excel plots the data in columns. Each point in the sample chart below is a separate series, so each has its own color. Click the plus icon next to the chart, and add error bars. Excel adds X and Y error bars with default values. The default Y error bar values are very small, so they are hidden by the markers; I’ve temporarily assigned a value of 0.5 you you can see them. Select one set of vertical error bars, and click Delete; repeat for all other vertical error bars. Remember, each series has its own set. Select one set of horizontal error bars, use the Ctrl+1 shortcut to open the Format Error Bars task pane, and under Error Amount, choose Custom, and click Specify Value. Select the ={1} in the first box, delete it, and select the range with the positive error bar values (use the whole range, even though the series has fewer points). Repeat for the second box and the range with the negative error bar values. Repeat for each additional set of horizontal error bars, using the same positive and negative range for each. Select one of the markers, press the Ctrl+1 shortcut to open the Format Series task pane. Under the paint can icon, choose Marker > Marker Options > None. Repeat for the other series. Format the error bars. Use No Cap for the error bar’s end style, give the line its own unique color with a transparency of 50% so the gridlines show through, and set the line width to 12 points. Finally, click the plus icon next to the chart, and check the box to add data labels. Format the data labels so they are centered on the points, and so they show the X values instead of the default Y values. POSTED: Tuesday, May 28th, 2019 under Chart Types.
TAGS: error bars , FloatingBars .
COMMENTS: 2
Filed Under: Chart TypesTagged With: error
bars , Floating Bars GANTT CHART WITH NICE DATE AXIS Monday, May 20, 2019 by Jon Peltier2 Comments
Monday, May 20, 2019 by Jon Peltier Peltier Technical Services, Inc., Copyright © 2019 , All rights reserved.Gantt Chart
with Labels and Gridlines on First of MonthSIMPLE GANTT CHARTS
A year ago I wrote an article about Gantt Charts in Microsoft Excel. It was pretty
comprehensive, showing how to make simple Gantt charts using worksheet conditional formatting and using regular Excel stacked bar charts, and it covered some advanced techniques for adding embellishments, such as a reference line for a given date, multiple bars showing percent complete of a task, and milestone markers at the end of the bars. Simple Excel Gantt Chart using Conditional Formatting in a Worksheet Simple Excel Gantt Chart usingStacked Bar Chart
Simple Excel Gantt Chart starting with a Stacked Bar Chart These Gantt charts all looked great, but a clever reader asked about a nicer axis scale across the bottom of the chart. In my example above, I had a tick label and vertical gridline every week, which is great for the time scale of my data. But what if the chart spans a longer time, for example, a year, and I want a label and gridline at the first of every month. GANTT CHART WITH WRONG MONTHLY DATE SCALE Below is a similar data set, but spanning a year rather than a fewmonths.
Following the protocol in the earlier tutorial, we can build a Gantt chart like this one. Never mind the steps, because we’ll have to do it differently. Read on. The chart spans the year nicely, but the axis scale is not what we’d like. When setting the bar chart’s axis scale, I can’t just say, put a label every month on the first. I have to pick a set number of days. Above I tried 31 days, which worked for January and February, but quickly went awry. Below you can see the 31-day axis spacing, and a 30 day spacing, which is also wrong. If we recall that 365 days divided by 12 is 30.41666…, we might try 30.4 days, in the bottom scale, and italmost works.
Excel Line charts can give us a nice axis scale. Let’s have a look. LINE CHART DATE AXES In the first example, we had a bar chart with a horizontal axis date scale that had a label every 7 days. The horizontal axis in a bar chart is a value axis. We can use that same data to create a line chart, with the same 7-day axis spacing. The horizontal axis in a line chart is a category axis, in this case, a date-scale axis. The axis can have the same min and max as the bar chart’s value axis above, but there are moreformatting options.
Below are the Format Axis task panes for the bar chart’s horizontal value axis (left) and the line chart’s horizontal date axis (right). In the bar chart, the minimum and maximum are the numbers 43184 and 43289, which are actually the serial numbers (days since 1 January 1900) of the min and max dates in the line chart, 3/25/2018 and7/8/2018. The
major unit (axis tick spacing) in the bar chart is 7, and in the line chart is 7 days. So these two axes are the same. In the current example, we have a bar chart with a horizontal axis date scale that had a label every 31 days. Not exactly every month. Again, we can use that same data to create a line chart, with monthly axis spacing. But here, the spacing is exactly monthly. Now we can see the real difference in the axis scales. Here are the Format Axis task panes as before. In the bar chart, the minimum and maximum are the numbers 43466 and 43831, which are the serial numbers of the min and max dates in the line chart, 1/1/2019 and 1/1/2020. The major unit (axis tick spacing) in the bar chart is 31, which is only appropriate for some months, while in the line chart it is 1 month. Excel uses this setting and adds the appropriate number of days, 31, 30, 29, or 28, so the tick labels are one month apart. GANTT CHART WITH NICE MONTHLY DATE SCALE Sometimes different chart types can share incompatible axis types, but here we are not so lucky. We need to create a chart with bar chart series to display the Gantt chart bars, and we need to combine this with a line chart to produce the date axis with the desired tick spacing. It’s a lot of steps, but not too complicated, and for the most part, the order you apply them is immaterial.THE DATA
Let’s start with the data, below. On the left is the data needed to create the Gantt chart itself, and on the right is the data for the axis scale. The Gantt data has Task names, Start dates, Duration, and End dates; only the first three columns are needed. The Axis Scale data has the first and last dates in the first column, dummy values of zero in the second column, and serial numbers of the dates in the third column. The formula in the third column links to the first, but the cells are formatted not as a Date but as General. CREATE AND FORMAT THE CHART Select the first three Gantt columns, and insert a bar chart. Let’s format the bars and get it over with. Start has no fill, so it’s hidden, and Duration is given the desired color. I have applied a 50% transparency to Duration, so the gridlines show through thebars.
Format the vertical category axis (showing task names), choosing the Categories in reverse order. In the simple Gantt chart from the earlier tutorial, we also moved the time axis to the bottom of the chart, but we’ll keep them at the top for this chart. Now add the data for the dummy axis. Copy the first two columns of the right hand data block shown above, with dates and dummy zero values. Select the chart, then go to the Home tab of the ribbon, click the Paste dropdown, and choose Paste Special from the bottom of the menu. Make sure these options are selected: Add Cells as New Series, By Column, Series Names in First Row, Categories in First Column. The series doesn’t appear as much: it is a new set of stacked bars, but the values of zero means the bars have zero length and don’t appear. There is a new entry in the legend. Right click on any series in the chart, and choose Change Series Chart Type. All three series are listed as Stacked Bar on the Primary Axis. Click the Chart Type dropdown for the series named Axis, and select a Line type (I used the line with markers subtype so the series shows more clearly in the following steps). Excel automatically changes the Axis series from Primary to SecondaryAxis.
Here’s our combination chart. We don’t need the legend any more, so delete it. FORMAT THE DATE AXES Now add the secondary horizontal axis, the whole reason we added the dummy Axis series. Easiest way: select the chart, click on the “+” icon that appears next to the chart, click the right-pointing triangle next to Axes, and check the Secondary Horizontal box. Now move the new axis to the bottom. Format the secondary vertical axis, on the right edge of the chart. Under Horizontal Axis Crosses,choose Automatic.
Now double-click on the bottom horizontal date axis to format it. Make sure the minimum and maximum are correct, change Base Units to Days, and change Major Units to 1 Month. Under Axis Position, choose On Tick Marks. Also, click under the paint can, and choose No Line for theaxis.
Change the Number Format of the axis labels. I like to use a two-line format, with the day above the month. You can get a line feed in the middle of a number format in the worksheet, by typing Ctrl+J in the Number Format box, and then use this data for the axis and check Linked to Source in the Format Axis task pane. Unfortunately there is no way to get the line feed in the Format Axis task pane, which is a severely annoying shortcoming. I was so annoyed that I wrote a little VBA procedure to do this, and I’ve added it to the Advanced Edition of Peltier Tech Charts for Excel 3.0 , pardon the shameless plug. I can right click on the axis, and select Axis Number Format from the context menu. A little dialog pops up, showing the current number format for theaxis.
Then I enter the number format I want, inserting ‘I want a line feed.
I click OK and I get the desired number format. The axis may have been fine with just the short month name, that is, a number format of “mmm”, which you can apply using the Format Axistask pane.
Now set the axis scale limits on the primary axis, showing dates at the top of the chart. The axis had used 43200 and 43900, the serial numbers of 10-April-2018 and 10-March-2020, which were autoscaled by Excel. We need to use the serial numbers of 1-January-2019 and 1-January-2020, which are 43466 and 43831. If you use a major unit of 365, you will get tick labels at only the min and max of the axis, asshown below.
FINISH WITH A LITTLE CLEAN-UP Turn off the primary major vertical gridlines and turn on the secondary major vertical gridlines. Easy: click the “+” icon next to the chart, click the right-pointing triangle next to Gridlines, anduse the checkboxes.
There are two axes we don’t want to show. Format the top horizontal axis so it uses no line (in this case it already had no line) and no tick labels. You could format the right-hand vertical axis the same way; since we really don’t need it any more you could also delete it, but don’t delete the horizontal axes! Finally, hide the dummy axis series. Double click on the series, and under the paint can, choose No Line and No Markers for its formatting. There’s our Gantt chart with gridlines and axis labels at the first of each month. You don’t need to squint too hard to see that the month between 1 February and 1 March is shorter than the months between 1 January and 1 February and between 1 March and 1 April. POSTED: Monday, May 20th, 2019 under Chart Axes.
TAGS: Combination Charts, date scale
, date-scale axis
, Gantt Chart
.
COMMENTS:
2
Filed Under: Chart Axes Tagged With: Combination Charts, date scale
, date-scale axis
, Gantt Chart
SURPLUS-DEFICIT AREA CHART Monday, March 18, 2019 by Jon Peltier3 Comments
Monday, March 18, 2019 by Jon Peltier Peltier Technical Services, Inc., Copyright © 2019 , All rights reserved. This Surplus-Deficit chart shows the history of surpluses and deficits (positive and negative values) over the past few years. The two colors help show when the deviation was positive and when it was negative. It takes a little bit of Excel magic to get that effect to work properly in an Excel area chart. I showed how to make this kind of chart in an old tutorial, Area Chart – Invert if Negative. That
approach required repeating the data in a new range, inserting a row after every row of data, and doing some interpolation to get the chartdata right.
But I was looking over a “visual vocabulary” by my colleague Jon Schwabish of Policy Viz , a rough draft summary of many chart and visualization types and guidelines for building them in Excel. When it’s ready for public consumption, I’ll be sure topost a link.
Jon showed a surplus-deficit area chart, and on inspection, it wasn’t exactly right. He went to my earlier post and fixed it, but in the meantime, I thought of a way to do the calculations in a more compact way, supplementing rather than supplanting the original data. SURPLUS-DEFICIT COLUMN CHARTS For this tutorial I will use a simple data set, with monthly surplus-deficit data. I’ve shaded this original data blue, so you can follow it in the long protocol. Note that the data is in an Excel Table, which has lots of magic of its own. You can make a surplus-deficit chart with any number of chart types. Below is a simple column chart of this data showing surpluses and deficits in a simple data set. Easy to make, easy to read: surpluses grow upwards from the horizontal axis, deficits downwards. Excel makes it easy to show positive and negative values in different colors in bar and column charts. Format the bars with the main (positive) color, then in the Format Fill part of the Format Series task pane, check the Invert If Negative box, and select the color for the negative values. SURPLUS-DEFICIT AREA CHARTS THE TWO-COLOR AREA CHART In general, I prefer columns for this kind of data rather than areas: it always seems to me that an area chart minimizes the points on either side of an axis crossing, and overemphasizes points in the middle of a positive or negative run. But area charts have their place, so let’s make an area chart of thedata:
Let’s format the colors like we did for the column chart. Main color, blue, that’s fine. But there’s no Invert If Negative setting. Let’s stop and scratch out chins for a while. What if we have separate Pos and Neg series in the chart? We can always format them with the desired different colors. Here’s the data with Pos and Neg data columns added. I’ve used these formulas for Pos and Neg respectively:Pos: =MAX(0,B2)
Neg: =MIN(0,B2)
Here’s the (unstacked) area chart showing Pos and Neg: It’s not exactly right. The sloped edges of the different colored should meet on the horizontal axis, along the lines shown in thefollowing chart:
Our data isn’t complete enough. We only have points at the vertices of the surplus-deficit data, indicated by the circular markers in thisline chart:
That’s fine for a one-series area chart. But in our Pos and Neg data, here are the points we have:ADJUSTING THE DATA
For the areas to line up correctly, we would need data points at the axis crossings between vertices, like this for the one-series linechart:
…or like this for the Pos Neg chart: This is where we need to interpolate our area chart data. In my old tutorial, I inserted rows to make room for interpolation, like this. The blue is our original data, and the gold shaded rows are the interpolated data. The green shaded row at the top is a placeholder to make sure I have as many interpolated rows as original rows, for when I adjust the data in a minute. In the Value column, the formula in the gold cell B4 compares the values in the blue cells above and below it (B3 and B5). If their product (B3*B5) is positive, it means that both have the same sign, so there is no axis crossing, and the cell will show #N/A, to suppress plotting of that point. If their product is negative, it means the blue cells have different signs; the line must cross in between, so the cell contains zero for the Y value at the crossing. Don’t worry about the formulas yet, they need to be redone when the table isadjusted.
In the Date column, if there is no axis crossing (product of blue Value cells B3*B5 is positive or gold Value cell contains #N/A), then the gold Date cell contains #N/A, to suppress plotting. If there is a crossing (product of blue Value cells is negative or gold Value cell contains zero), the gold Date cell A4 contains an interpolated value between the two blue Dates A3 and A5. Anyway, I realized that I can add columns to the data table for these calculations. To illustrate, I’ll adjust my data in two steps. First I’ll move the gold and green cells to the right by four columns: Now I’ll remove the blank cells between rows. And we no longer need the gold and green to distinguish interpolatedrows.
The table contains the original data in the blue columns, and these formulas in the other columns Pos: =MAX(0,B2) Neg: =MIN(0,B2) Date 2: =IF(ROW()=ROW(INDEX(,1)),NA(),IF(B1*B2>0,NA(), B1/(B1-B2)*(A2-A1)+A1)) Value 2: =IF(ROW()=ROW(INDEX(,1)),NA(),IF(B1*B2>0,NA(),0)) Pos 2: =IF(ROW()=ROW(INDEX(,1)),NA(),IF(B1*B2>0,NA(),0)) Neg 2: =IF(ROW()=ROW(INDEX(,1)),NA(),IF(B1*B2>0,NA(),0)) There are a few tricks. The first IF looks at the condition ROW()=ROW(INDEX(,1)) which is true if the row is the first data row of the table. And if it’s true, the formula uses NA() to put #N/A into the cell. The second IF looks at B1*B2. If values in both B1 and B2 are positive or both are negative, the result of multiplication is positive, so the formula returns #N/A. If they are mixed, we get a negative result, so the formula puts zero into the cell. We’re never actually multiplying B1 and B2, which would produce a #VALUE! error, because cell B1 contains a text header label. In that row of the table, the first IF takes precedence, because it already has determined that the cell is in the first data row and the valueshould be #N/A.
ADJUSTING THE CHART
Now we just need to get this additional data into the chart. The original Pos-Neg area chart looks like this. The two series formulas look like this: Pos: =SERIES(Sheet1!$C$1,Sheet1!$A$2:$A$13,Sheet1!$C$2:$C$13,1) Neg: =SERIES(Sheet1!$D$1,Sheet1!$A$2:$A$13,Sheet1!$D$2:$D$13,2) We need to change the X values fromSheet1!$A$2:$A$13
to
(Sheet1!$A$2:$A$13,Sheet1!$E$2:$E$13) And the Y values fromSheet1!$C$2:$C$13
to
(Sheet1!$C$2:$C$13,Sheet1!$G$2:$G$13)and
Sheet1!$D$2:$D$13
to
(Sheet1!$D$2:$D$13,Sheet1!$H$2:$H$13) You can make the changes by typing in the series formula directly, or by using the Select Data dialog. After changing the series formulas, the areas in the resulting chart align across the axis as desired. It’s a nice little trick, and it works for area charts and other charts that have category axes that can display a Date scale. These charts internally sort by date before plotting the data. So we can put the data anywhere in the list, even in a separate column that makes up a different part of the list. POSTED: Monday, March 18th, 2019 under Chart Types.
TAGS: area chart , invert ifnegative .
COMMENTS:
3
Filed Under: Chart TypesTagged With: area
chart , invert if negative REMOTELY TRIGGER AN EXCEL VBA MACRO Monday, March 11, 2019 by Jon Peltier4 Comments
Monday, March 11, 2019 by Jon Peltier Peltier Technical Services, Inc., Copyright © 2019 , All rights reserved.THE PROBLEM
I’ve recently helped out a client with a project that updated some data and exported a related chart. The client asked if I had a way for him to trigger the VBA procedure (macro) on his PC from his Android phone. He’s not using Office 365 or OneDrive, so he needed a low-tech remote VBA solution. I decided to base my solution on a synchronized folder in Google Drive or Dropbox, where the presence of a particular file triggers execution of the VBA procedure. It’s easy enough to create a dummy file in one of these shared folders from an Android phone, and even easier if a dummy file exists and all you need to do is rename it. To run successfully, the remote computer must be running Excel, with the applicable workbook open; Excel’s Application.OnTime function must have started the monitoring process; and a reliable file-sharing service must be synchronizing the target folder automatically. THE UNTRIGGERED PROJECT An oversimplification of the client’s project is shown below. There is some data and a chart that displays the data. The data in C3:C8 is dynamic, in this example because the cells contain the formula =RANDBETWEEN(1,5). The “Activate Monitoring” button is used to start the monitoring process; I’ll talk about it in a bit. Why a pie chart? It’s simple enough for a demo; pie charts aren’t quite as evil as we’ve asserted for all these years; and they make easily-distinguishable file icons when viewing a Windows directory, as shown in a screenshot at the end of this article. Here is the simple VBA routine that updates the data and exports a time-stamped image of the chart: Sub RecalcAndExportChart() Dim ws As Worksheet Set ws = ThisWorkbook.Worksheets("Sheet1")Dim cht As Chart
Set cht = ws.ChartObjects(1).Chartws.Calculate
Dim FileName As String FileName = FullPath & "Chart_" & Format(Now, "yyyy-mm-dd_hhmmss") & ".png" cht.Export FileName Debug.Print FileNameEnd Sub
FullPath calls a function to return the target directory for the exported image of the chart. I use the Debug.Print to tell me if achart was exported.
THE TRIGGER MECHANISMTHE SHARED FOLDER
I designed the first version of this remote VBA trigger mechanism to run using Google Drive, and then modified it to run in DropBox. To operate in Google Drive, I first created a top-level folder in my Drive account named _ Remote. I put this folder name into a VBA constant at the top of my code module: Const REMOTE_DIRECTORY As String = "\Google Drive\_ Remote" and I referenced this constant in the FullPath function: Public Function FullPath() As String FullPath = Environ("UserProfile") & REMOTE_DIRECTORY & Application.PathSeparatorEnd Function
Environ is a handy function that returns useful information about the user’s environment. On my computer, Environ("UserProfile") returns the directory of my Windows account: C:\Users\Jon Peltier so FullPath returns: C:\Users\Jon Peltier\Google Drive\_ Remote\ This is where the newly created Google Drive folder is located on my computer. It is set to synchronize automatically, so any changes made on my laptop are reflected (nearly) instantly on my phone, and viceversa.
TRIGGER FILES
I defined three file names (as VBA constants) for various triggers: Const REMOTE_TRIGGER_FILE_TRUE As String = "true.trigger" Const REMOTE_TRIGGER_FILE_FALSE As String = "false.trigger" Const REMOTE_TRIGGER_FILE_STOP As String = "stop.trigger" Under normal operation, when the PC is waiting to be triggered but I don’t want to trigger it yet, a file named false.trigger will be present in the _ Remote folder. When I want the VBA update and export procedure to run, I change the name of the file to true.trigger. And if I want the PC to stop monitoring for a trigger, I will rename thefile stop.trigger.
There is nothing special about these trigger files except their file names. I created false.trigger as a simple text file. More sophisticated versions of this mechanism may use parameters encoded in the contents of the file. To determine whether a file exists, I use a very old Visual Basic routine I long ago cribbed from a former Visual Basic MVP named Karl Peterson. Pass in the full path and file name, and it returns True orFalse:
Public Function FileExists(ByVal FileSpec As String) As Boolean ' thanks Karl Peterson MS VB MVP ' Guard against bad FileSpec by ignoring errors retrieving its attributes On Error Resume NextDim Attr As Long
Attr = GetAttr(FileSpec) If Err.Number = 0 Then ' No error, so something was found. ' If Directory attribute set, then not a file. FileExists = Not ((Attr And vbDirectory) = vbDirectory)End If
End Function
I have three functions that tell me whether a trigger file is present: Public Function IsTriggerSet() As Boolean IsTriggerSet = FileExists(FullPath & REMOTE_TRIGGER_FILE_TRUE)End Function
Public Function IsTriggerNotSet() As Boolean IsTriggerNotSet = FileExists(FullPath & REMOTE_TRIGGER_FILE_FALSE)End Function
Public Function StopTrigger() As Boolean StopTrigger = FileExists(FullPath & REMOTE_TRIGGER_FILE_STOP)End Function
MONITORING AND UPDATING Finally, I need a VBA procedure to be running on my PC which will detect one of the trigger files and act accordingly: Sub MonitorAndUpdate() If StopTrigger Then Debug.Print Now, "Stop" If Not IsTriggerNotSet Then FileCopy FullPath & REMOTE_TRIGGER_FILE_STOP, FullPath & REMOTE_TRIGGER_FILE_FALSEEnd If
Kill FullPath & REMOTE_TRIGGER_FILE_STOP DeactivateMonitoringElse
If IsTriggerSet Then If Not IsTriggerNotSet Then FileCopy FullPath & REMOTE_TRIGGER_FILE_TRUE, FullPath & REMOTE_TRIGGER_FILE_FALSEEnd If
Kill FullPath & REMOTE_TRIGGER_FILE_TRUE Debug.Print Now, True RecalcAndExportChartElse
Debug.Print Now, False If Not IsTriggerNotSet Then Dim sFullName As String sFullName = FullPath & REMOTE_TRIGGER_FILE_FALSE Debug.Print "Creating " & sFullName Dim iFile As LongiFile = FreeFile
Open sFullName For Output As iFileClose #iFile
End If
End If
ActivateMonitoring
End If
End Sub
How it works in English is: * If a file named stop.trigger is present* Monitoring stops
* stop.trigger is renamed to false.trigger * If a file named true.trigger is present * The file is recalculated and the chart is exported * true.trigger is renamed to false.trigger * Monitoring continues * If none of the trigger files are present * A new false.trigger file is created * Monitoring continues * If a file named false.trigger is present * Nothing happens, the program doesn’t even check ACTIVATION AND DEACTIVATION OF MONITORING To activate or continue monitoring we need one more VBA constant for MONITOR_DURATION the time between checking for a trigger (here it is set for five seconds): Const MONITOR_DURATION As String = "0:00:05" and we need to use Application.OnTime to tell VBA when to run our monitoring procedure MonitorAndUpdate: Sub ActivateMonitoring() If Not IsActive Then Debug.Print "Activating"End If
IsActive = True
NextTime = Now + TimeValue(MONITOR_DURATION) Application.OnTime NextTime, "MonitorAndUpdate"End Sub
IsActive is a variable that keeps track of whether we are just starting our monitoring or whether we have been monitoring all along, and NextTime is another variable that keeps track of the next scheduled monitoring time: Dim IsActive As Boolean Dim NextTime As Date Finally, to abort the monitoring, we need to tell Application.OnTime to cancel the last event we’ve scheduled. If we don’t keep track of NextTime, you can’t cancel the scheduled event. Sub DeactivateMonitoring() On Error Resume Next Application.OnTime NextTime, "MonitorAndUpdate", , FalseOn Error GoTo 0
NextTime = 0
IsActive = False
Debug.Print "Deactivating"End Sub
The “Activate Monitoring” button next to the chart in the very first screenshot runs ActivateMonitoring the very first time. RUNNING THE MONITORING ROUTINE When I started, the _ Remote directory contained only the “Remote.xlsm” workbook that contains the data and chart and the code. I didn’t need to keep the workbook in the _ Remote directory, but it was convenient for this exercise. I hadn’t even bothered to create the empty false.trigger text file. I started the routine by clicking the “Activate Monitoring” button. This is indicated by “Activating” in the Immediate Windowlog below.
The first time the program checked for a trigger file was at 10:22:08 AM; “False” indicates that it should not update and export. Since it found no false.trigger file, it created one. The program checked again five seconds later, at 10:22:13, and took no action, but by 10:22:18, I had renamed the false.trigger file to true.trigger, and we see that a file named Chart_2019-03-07_102218.pngwas exported.
The monitoring proceeded for several more cycles, exporting three more charts while I tested renaming the trigger file on my phone, in Windows Explorer, and in the web interface for Google Drive. Finally I renamed the false.trigger file to stop.trigger, andmonitoring ceased.
Debug.Print log from Immediate Window -------------------------------------Activating
3/7/2019 10:22:08 AM False Creating C:\Users\Jon Peltier\Google Drive\_ Remote\false.trigger 3/7/2019 10:22:13 AM False 3/7/2019 10:22:18 AM True C:\Users\Jon Peltier\Google Drive\_ Remote\Chart_2019-03-07_102218.png 3/7/2019 10:22:23 AM False 3/7/2019 10:22:28 AM False 3/7/2019 10:22:33 AM False 3/7/2019 10:22:38 AM True C:\Users\Jon Peltier\Google Drive\_ Remote\Chart_2019-03-07_102238.png 3/7/2019 10:22:43 AM False 3/7/2019 10:22:48 AM False 3/7/2019 10:22:53 AM False 3/7/2019 10:22:58 AM False 3/7/2019 10:23:03 AM True C:\Users\Jon Peltier\Google Drive\_ Remote\Chart_2019-03-07_102303.png 3/7/2019 10:23:08 AM False 3/7/2019 10:23:13 AM False 3/7/2019 10:23:18 AM True C:\Users\Jon Peltier\Google Drive\_ Remote\Chart_2019-03-07_102318.png 3/7/2019 10:23:23 AM False 3/7/2019 10:23:28 AM False 3/7/2019 10:23:33 AM False 3/7/2019 10:23:38 AM StopDeactivating
When this all finished, I took a screenshot of the Google Drive\_ Remote folder. You can see the four exported chart images, the false.trigger file (which stop.trigger was renamed), and the workbookthat runs it all.
MODIFYING TO WORK IN DROPBOX I had to make only one simple change in the whole routine, to the REMOTE_DIRECTORY constant, for it to work just as well in Dropbox: Const REMOTE_DIRECTORY As String = "\Dropbox\_ Remote" Here’s a screenshot of the _ Remote folder on dropbox, taken frommy phone.
The synchronization is handled by third-party file sharing applications, and all Excel has to do is wake up periodically to check for a filename, so this routine ought to work in other services besides Google Drive and Dropbox. THE COMPLETE VBA CODE Here is the complete code for the remote VBA triggering project. You can also download my macro-containing workbook in this zip file:Remote.zip
.
Option Explicit
Const REMOTE_DIRECTORY As String = "\Google Drive\_ Remote" 'Const REMOTE_DIRECTORY As String = "\Dropbox\_ Remote" Const REMOTE_TRIGGER_FILE_TRUE As String = "true.trigger" Const REMOTE_TRIGGER_FILE_FALSE As String = "false.trigger" Const REMOTE_TRIGGER_FILE_STOP As String = "stop.trigger" Const MONITOR_DURATION As String = "0:00:10" Dim IsActive As Boolean Dim NextTime As Date Public Function FullPath() As String FullPath = Environ("UserProfile") & REMOTE_DIRECTORY & Application.PathSeparatorEnd Function
Public Function IsTriggerSet() As Boolean IsTriggerSet = FileExists(FullPath & REMOTE_TRIGGER_FILE_TRUE)End Function
Public Function IsTriggerNotSet() As Boolean IsTriggerNotSet = FileExists(FullPath & REMOTE_TRIGGER_FILE_FALSE)End Function
Public Function StopTrigger() As Boolean StopTrigger = FileExists(FullPath & REMOTE_TRIGGER_FILE_STOP)End Function
Public Function FileExists(ByVal FileSpec As String) As Boolean ' thanks Karl Peterson MS VB MVP ' Guard against bad FileSpec by ignoring errors retrieving its attributes On Error Resume NextDim Attr As Long
Attr = GetAttr(FileSpec) If Err.Number = 0 Then ' No error, so something was found. ' If Directory attribute set, then not a file. FileExists = Not ((Attr And vbDirectory) = vbDirectory)End If
End Function
Sub ActivateMonitoring() If Not IsActive Then Debug.Print "Activating"End If
IsActive = True
NextTime = Now + TimeValue(MONITOR_DURATION) Application.OnTime NextTime, "MonitorAndUpdate"End Sub
Sub DeactivateMonitoring() On Error Resume Next Application.OnTime NextTime, "MonitorAndUpdate", , FalseOn Error GoTo 0
NextTime = 0
IsActive = False
Debug.Print "Deactivating"End Sub
Sub MonitorAndUpdate() If StopTrigger Then Debug.Print Now, "Stop" If Not IsTriggerNotSet Then FileCopy FullPath & REMOTE_TRIGGER_FILE_STOP, FullPath & REMOTE_TRIGGER_FILE_FALSEEnd If
Kill FullPath & REMOTE_TRIGGER_FILE_STOP DeactivateMonitoringElse
If IsTriggerSet Then If Not IsTriggerNotSet Then FileCopy FullPath & REMOTE_TRIGGER_FILE_TRUE, FullPath & REMOTE_TRIGGER_FILE_FALSEEnd If
Kill FullPath & REMOTE_TRIGGER_FILE_TRUE Debug.Print Now, True RecalcAndExportChartElse
Debug.Print Now, False If Not IsTriggerNotSet Then Dim iFile As LongiFile = FreeFile
Dim sFullName As String sFullName = FullPath & REMOTE_TRIGGER_FILE_FALSE Debug.Print "Creating " & sFullName Open sFullName For Output As iFileClose #iFile
End If
End If
ActivateMonitoring
End If
End Sub
Sub RecalcAndExportChart() Dim ws As Worksheet Set ws = ThisWorkbook.Worksheets("Sheet1")Dim cht As Chart
Set cht = ws.ChartObjects(1).Chartws.Calculate
Dim FileName As String FileName = FullPath & "Chart_" & Format(Now, "yyyy-mm-dd_hhmmss") & ".png" cht.Export FileName Debug.Print FileNameEnd Sub
This code can be pasted into a regular code module, and modified to suit your file sharing situation and the updating and exporting requirements of your project. POSTED: Monday, March 11th, 2019 under VBA.
TAGS: VBA .
COMMENTS:
4
Filed Under: VBA Tagged With:VBA
* Page 1
* Page 2
* Page 3
* …
* Page 119
* Next Page »
PRIMARY SIDEBAR
* Peltier Tech Blog
* About Peltier Tech* About Jon Peltier
* Peltier Tech Consulting * Peltier Tech Advanced Excel Training * Copyright and Licensing * Blog Privacy Policy * Blog Comment Policy* Guest Post Policy
EXCEL CHARTING ADD-INS PELTIER TECH NEWSLETTER Sign up for the Peltier Tech Newsletter: weekly tips and articles, monthly or more frequent blog posts, plus information about training and products by Peltier Tech and others.First name:
Details
Copyright © 2024 ArchiveBay.com. All rights reserved. Terms of Use | Privacy Policy | DMCA | 2021 | Feedback | Advertising | RSS 2.0