Are you over 18 and want to see adult content?
More Annotations
![A complete backup of cosmedecorte.com](https://www.archivebay.com/archive2/436d9f61-ced0-43ea-b9d4-4f3c9c11e204.png)
A complete backup of cosmedecorte.com
Are you over 18 and want to see adult content?
![A complete backup of cultivainternational.org](https://www.archivebay.com/archive2/8199b5f7-ec2b-408a-b980-7dec90df4dcd.png)
A complete backup of cultivainternational.org
Are you over 18 and want to see adult content?
![A complete backup of bangkokinsurance.com](https://www.archivebay.com/archive2/39b38149-3316-423a-a500-e080eb11e09a.png)
A complete backup of bangkokinsurance.com
Are you over 18 and want to see adult content?
![A complete backup of agungadhyaksa.blogspot.com](https://www.archivebay.com/archive2/793a9a66-f2da-4bba-94ce-832818c14207.png)
A complete backup of agungadhyaksa.blogspot.com
Are you over 18 and want to see adult content?
![A complete backup of careerbuilder.ca](https://www.archivebay.com/archive2/8d75ffae-56e0-4dbf-9e34-c71144896e96.png)
A complete backup of careerbuilder.ca
Are you over 18 and want to see adult content?
![A complete backup of inyourtummy.tumblr.com](https://www.archivebay.com/archive2/a5fe81f0-e837-4f2e-bfd4-d004bbb3507f.png)
A complete backup of inyourtummy.tumblr.com
Are you over 18 and want to see adult content?
Favourite Annotations
![A complete backup of xdlatino.blogspot.com](https://www.archivebay.com/archive2/94aa9895-9964-4a33-af80-50ac36568ae9.png)
A complete backup of xdlatino.blogspot.com
Are you over 18 and want to see adult content?
Text
numerous
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 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
JON'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% 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. 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.
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
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 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
JON'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% 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. 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.
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
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 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.
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-2JON'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% ADD ONE TRENDLINE FOR MULTIPLE SERIES Select Data. Right click on the chart and click on Select Data from the pop up menu. The Select Data Source dialog appears. Click the Add button, and the Edit Series dialog appears. Click in the Series Name box, and add a descriptive label. “Combined” works. Click in the Series X Values box, then with the mouse select the first range of X 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 VBA: ASSIGN NAMES TO CHART SERIES Here is a routine that assigns names from a given range to series in a given chart: Sub Chart_AssignNamesFromRange (cht As Chart, rng As Range) Dim iSrs As Long For iSrs = 1 To cht.SeriesCollection.Count If iSrs > rng.Cells.Count Then Exit For cht.SeriesCollection (iSrs).Name = _ "=" & rng.Cells (iSrs).Address (, , , True) Next End Sub. 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 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 DYNAMIC CHARTS USING DYNAMIC ARRAYS 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 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 DYNAMIC CHARTS USING DYNAMIC ARRAYS 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 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. 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
POLAR PLOT IN EXCEL
Polar Plots. Microsoft Excel offers a number of circular charts, but none of them is usually a particularly good choice for displaying data. You can search this blog for “pie chart” and see numerous examples of badly applied pie charts. 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.
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 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 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 CHECK YOUR DATA: IS IT NUMERIC OR TEXT 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.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 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). 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 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 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 CHECK YOUR DATA: IS IT NUMERIC OR TEXT 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.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 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). 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 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. 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 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. 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! ", 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 main 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* Excel Training
* Email Feed
* RSS
ASSIGN CHART SERIES NAMES OR CATEGORIES WITH VBA Wednesday, February 12, 2020 by Jon Peltier6 Comments
Wednesday, February 12, 2020 by Jon Peltier Peltier Technical Services, Inc., Copyright © 2019, All rights
reserved.
ASK USER FOR A RANGE VBA provides a number of ways to interact with users. MsgBox lets you send a message to a user, and get a simple response (yes/no or okay/cancel) in return. InputBox lets you ask a user to input some information. And you can design a whole UserForm as a custom dialog. We will use an InputBox to get a range from our user. There are two kinds of InputBox: regular InputBox that accepts text input from the user, and Application.InputBox, which lets you require a certain type of information from the user. Since we need a range, we’ll use Application.InputBox. The required syntax is: Function InputBox(Prompt As String, , , _, , , , )
You supply a Prompt to tell the user what you need, and optionally a Title for the InputBox and a Default value. Left and Top position the InputBox but haven’t worked in recent memory. HelpFile and HelpContextID are used if you have help content for the InputBox. Finally, Type describes the type of data you want; for our purposes, type 8 is used for a range. Here is a simple VBA procedure that uses Application.InputBox. It uses simple prompt, title, and default arguments, plus the 8 for range, and the in between arguments are left blank. When a range is returned, those cells are filled red. Sub GetUserRange1() Dim UserRange As Range Set UserRange = Application.InputBox("Prompt", "Title", "$A$1", , , , , 8) UserRange.Interior.Color = vbRedEnd Sub
When the code runs, here is how it looks. The default range is highlighted with a dark dashed outline. When another range is selected, its address appears in the InputBox, and the new range is highlighted a dark dashed outline. When we then click OK, the selected cell is filled red. If we try to enter anything that isn’t a range, we get a warning. If we give up and hit cancel, we get a run time error. The statement calling InputBox is highlighted. The warning is fine, but we don’t want to saddle our poor user with the run time error, so we will make a minor modification to the code. Actually, two modifications. First, we wrap the call to InputBox in On Error Resume Next and On Error GoTo 0. Then, if the user has canceled, UserRange will not have been assigned, so it is Nothing, and if this is the case, we’ll bail out before trying to format a nonexistent range, which would lead to another error. Sub GetUserRange2() Dim UserRange As Range _On Error Resume Next_ Set UserRange = Application.InputBox("Prompt", "Title", "$A$1", , , , , 8) _On Error GoTo 0_ _If UserRange Is Nothing Then_ _Exit Sub_ UserRange.Interior.Color = vbRedEnd Sub
Execution exits the sub above in two places, either Exit Sub if UserRange Is Nothing or End Sub at the end. If you have a bit of OCD like I do, you’d prefer the code to always exit at one place, so I usually use this structure instead: Sub GetUserRange2A() Dim UserRange As Range On Error Resume Next Set UserRange = Application.InputBox("Prompt", "Title", "$A$1", , , , , 8)On Error GoTo 0
_If Not UserRange Is Nothing Then_ UserRange.Interior.Color = vbRedEnd If
End Sub
Let’s punch up the code with a few text variables, and allow for a smart default range (or you can omit the default to leave out blank if that makes more sense). Sub GetUserRange3() Dim _Prompt_ As String _Prompt_ = "Select a Range" Dim _Title_ As String _Title_ = "Select Range" Dim _Default_ As String _Default_ = "$A$1" If TypeName(Selection) = "Range" Then _Default_ = Selection.Address(True, True)End If
Dim UserRange As Range On Error Resume Next Set UserRange = Application.InputBox(_Prompt_, _Title_, _Default_, , , , , 8)On Error GoTo 0
If Not UserRange Is Nothing Then UserRange.Interior.Color = vbRedEnd If
End Sub
So now that we know how to get a range from the user, let’s put it to better use than just formatting a range to have a red background. SELECT RANGE AND ASSIGN SERIES NAMES In this example, we’ll ask the user for a range containing series names, and we’ll apply the range to the active chart’s series one by one, until we run out of series in the chart or cells in theselected range.
Since we’re working on the active chart, the user must select a chart before running the code. We’ll use this structure to make sure there is an active chart: Sub DoSomethingToActiveChart() _If ActiveChart Is Nothing Then_ MsgBox "Select a chart and try again.", vbExclamation, "No Chart Selected"Else
' do our thing hereEnd If
End Sub
Often I just bail out if there’s no active chart, but it’s nice to let the user know why nothing happened when they clicked a button. In the procedure below, we check first for an active chart, then we ask for a range containing series names. Then we make sure the range is a single row or column (not strictly necessary, but otherwise it gets _complicated_), and step through the series of the chart, using each cell in the range as the title of the series. Sub SelectRangeWithSeriesNames() If ActiveChart Is Nothing Then MsgBox "Select a chart and try again.", vbExclamation, "No Chart Selected"Else
Dim Prompt As String Prompt = "Select a range that contains series names for the active chart." Dim Title As String Title = "Select Series Names" Dim SeriesNameRange As Range On Error Resume Next Set SeriesNameRange = Application.InputBox(Prompt, Title, , , , , , 8)On Error GoTo 0
If Not SeriesNameRange Is Nothing Then If SeriesNameRange.Rows.Count = 1 Or SeriesNameRange.Columns.Count = 1 ThenWith ActiveChart
Dim iSrs As Long
For iSrs = 1 To .SeriesCollection.Count If iSrs <= SeriesNameRange.Cells.Count Then .SeriesCollection(iSrs).Name = _ "=" & SeriesNameRange.Cells(iSrs).Address(, , , True)End If
Next
End With
Else
MsgBox "Select a range with one row or one column", vbExclamation, _ "Must be One Row or Column"End If
End If
End If
End Sub
Here is a chart. The highlighted data range shows that the Y values are in C5:E10, and the category labels (X values) in B5:B10, but no series names are highlighted. The series names Series1 etc. in the legend also indicate that no series names have been specified. Let’s use the labels in C2:E2 for series names. Run the code: the InputBox is waiting for a range to be selected. Select a range: it is highlighted with a dark dashed outline. Click OK and the selected range is now highlighted as the series names, and the chart legend shows these labels as series names. The above procedure can be used to assign series names (legend entries) for a chart that has none, or to replace the existing names (entries) in a chart that already has them. If there are not enough cells in the selected range, some series do not get new names assigned. If there are more cells than series, the code ignores the excess cells. SELECT RANGE AND ASSIGN CATEGORIES In much the same way, we can ask a user for a range containing category labels (X axis values) to assign to a chart. Sub SelectRangeWithCategoryLabels() If ActiveChart Is Nothing Then MsgBox "Select a chart and try again.", vbExclamation, "No Chart Selected"Else
Dim Prompt As String Prompt = "Select a range that contains category labels for the active chart." Dim Title As String Title = "Select Category Labels" Dim CategoryLabelRange As Range On Error Resume Next Set CategoryLabelRange = Application.InputBox(Prompt, Title, , , , , , 8)On Error GoTo 0
If Not CategoryLabelRange Is Nothing Then If CategoryLabelRange.Rows.Count = 1 Or CategoryLabelRange.Columns.Count = 1 ThenWith ActiveChart
If CategoryLabelRange.Cells.Count = .SeriesCollection(1).Points.Count Then .SeriesCollection(1).XValues = CategoryLabelRangeElse
MsgBox "Select a range with the correct number of points.", vbExclamation, _ "Wrong Number of Points"End If
End With
Else
MsgBox "Select a range with one row or one column", vbExclamation, _ "Must be One Row or Column"End If
End If
End If
End Sub
The above procedure can be used to assign category labels (X axis values) for a chart that has none, or to replace the existing labels in a chart that already has them. The selected range must have one row and multiple columns or one column and multiple rows. (In some cases, a chart can have category labels that use multiple rows/columns, but that is a lot more complicated than needed for this example.) If this condition is not met, the code tells the user to try again. Here is a chart. The highlighted data range shows that the Y values are in D3:F8, and the series names in D2:F2, but no categories are highlighted. The category labels 1, 2, 3, etc. along the X axis also indicate that no categories have been specified. Let’s use the labels in B3:B8 for categories. Run the code: the InputBox is waiting for a range to be selected. Select a range: it is highlighted with a dark dashed outline. Click OK and the selected range is now highlighted as the category range, and the horizontal axis displays these labels as categories. The above procedure can be used to assign categories (X values) for a chart that has none, or to replace the existing categories in a chart that already has them. If the selected range has the wrong number of cells for the points in a series, the procedure tells the user to select a range with the right number of labels. FIND RANGE AND ASSIGN SERIES NAMES If we construct a chart from an ideally-shaped range, there is a predictable alignment of X values, Y values, and series names in the worksheet. In this chart, the series data is in columns. The Y values for a series are in a vertical range, and the series name is in the cell directly above these Y values. The category labels (X values) are in the column to the left of the Y values of the first series. In the next chart, the series data is in rows. The Y values for a series are in a horizontal range, and the series name is in the cell directly to the left these Y values. The X values are in the row above the Y values of the first series. If for some reason the chart was constructed without series names or without category labels, but the missing elements are in the right alignment with respect to the Y values, we can find the cells with the series names or category labels and assign them to the chart. FIND RANGE AND ASSIGN SERIES NAMES The procedure below processes each series in the active chart. First it looks up the SERIES formula, and extracts its arguments into an array. The code finds the address of the Y values, which is in the third argument, and locates the associated range. Then it determines whether the data is by column or by row, identifies the cell with the series name, and uses this cell’s address as the name of the series. Sub FindAndApplyNamesToSeries() If Not ActiveChart Is Nothing ThenWith ActiveChart
Dim srs As Series For Each srs In .SeriesCollection _' series formula_ Dim sFmla As String sFmla = srs.Formula _' just the arguments_ sFmla = Mid$(Left$(sFmla, Len(sFmla) - 1), InStr(sFmla, "(") + 1) _' split into an array_ Dim vFmla As Variant vFmla = Split(sFmla, ",") _' Y values are the 3rd argument_ Dim sYVals As String sYVals = vFmla(LBound(vFmla) + 2) _' the Y value range_ Dim rYVals As Range Set rYVals = Range(sYVals) _' by row or column?_ If rYVals.Rows.Count > 1 Then _' by column, so use cell above column of Y values_ Dim rName As Range Set rName = rYVals.Offset(-1).Resize(1) ElseIf rYVals.Columns.Count > 1 Then _' by row, so use cell to left of Y values_ Set rName = rYVals.Offset(, -1).Resize(, 1)Else
_' one cell only: who knows?_ Set rName = NothingEnd If
If Not rName Is Nothing Then srs.Name = "=" & rName.Address(, , , True)End If
Next
End With
End If
End Sub
The data range for the chart below is intact, but somehow, the series names were not associated with it. Select the chart and run the procedure, and it applies the seriesnames.
If the series in the chart already had names, this procedure overwrites those names with the names it finds in the worksheet. FIND RANGE AND ASSIGN CATEGORIES The procedure below processes the first series in the active chart. First it looks up the SERIES formula, and extracts its arguments into an array. The code finds the address of the Y values, which is in the third argument, and locates the associated range. Then it determines whether the data is by column or by row, identifies the parallel range with the categories (X values), and uses this range as the categories for the first series, and therefore, for the chart. Sub FindAndApplyCategoriesToChart() If Not ActiveChart Is Nothing ThenWith ActiveChart
Dim srs As Series Set srs = .SeriesCollection(1) _' series formula_ Dim sFmla As String sFmla = srs.Formula _' just the arguments_ sFmla = Mid$(Left$(sFmla, Len(sFmla) - 1), InStr(sFmla, "(") + 1) _' split into an array_ Dim vFmla As Variant vFmla = Split(sFmla, ",") _' Y values are the 3rd argument_ Dim sYVals As String sYVals = vFmla(LBound(vFmla) + 2) _' the Y value range_ Dim rYVals As Range Set rYVals = Range(sYVals) _' by row or column?_ If rYVals.Rows.Count > 1 Then _' by column, so use column to left of Y values_ Dim rXVals As Range Set rXVals = rYVals.Offset(, -1) ElseIf rYVals.Columns.Count > 1 Then _' by row, so use row above Y values_ Set rXVals = rYVals.Offset(-1)Else
_' one cell only: who knows?_ Set rXVals = NothingEnd If
If Not rXVals Is Nothing Then srs.XValues = rXValsEnd If
End With
End If
End Sub
The data range for the chart below is intact, but somehow, the categories were not attached to it. Select the chart and run the procedure, and it applies the categoriesto the chart.
If the series in the chart already had categories, this procedure overwrites those categories with the new labels it finds in theworksheet.
SELECT CHART SOURCE DATA RANGE For completeness, here is the routine that lets the user select the chart source data for the active chart. Sub SelectChartSourceDataRange() If ActiveChart Is Nothing Then MsgBox "Select a chart and try again.", vbExclamation, "No Chart Selected"Else
Dim Prompt As String Prompt = "Select a range that contains source data for the active chart." Dim Title As String Title = "Select Chart Source Data Range" Dim ChartSourceData As Range On Error Resume Next Set ChartSourceData = Application.InputBox(Prompt, Title, , , , , , 8)On Error GoTo 0
If Not ChartSourceData Is Nothing Then ' if a range was selected If ChartSourceData.Rows.Count >= ChartSourceData.Columns.Count Then Dim DataOrientation As XlRowCol DataOrientation = xlColumnsElse
DataOrientation = xlRowsEnd If
ActiveChart.SetSourceData ChartSourceData, DataOrientationEnd If
End If
End Sub
JON’S TOOLBOX
If you follow this blog closely, you may have heard of Jon’s Toolbox, a new Excel
add-in that I released recently. I’ve already made a few changes to this utility, thanks to suggestions from readers like you. Jon’s Toolbox already had a feature that made it easy to select a new source data range for a chart. While finishing up this article, I realized that these features should be included in the software. The features have only been added to the DEV version of the software on my computer, but in a week or so I will release a new build that includes these new functions. If you already have Jon’s Toolbox, subscribe to my newsletter to hear when the new build is live. If you don’t have Jon’s Toolbox, you can get it at the bottom of my article Jon’s Toolbox – A New Utility from Peltier Tech.
POSTED: Wednesday, February 12th, 2020 under VBA.
TAGS: Category Labels , InputBox , Series Names.
COMMENTS:
6
Filed Under: VBA Tagged With:Category Labels ,
InputBox , Series Names HOUR BY HOUR FLOW CHART Monday, February 3, 2020 by Jon Peltier5 Comments
Monday, February 3, 2020 by Jon Peltier Peltier Technical Services, Inc., Copyright © 2019, All rights
reserved.
My colleague Frédéric LeGuen, an Excel MVP from France who runs the ExcelExercise web site and YouTube channel , asked me a tricky question last week. He wanted a way to visually track visitors to a museum, knowing how many arrived and left during a given hour. He tried a candlestick chart first, which is great for monitoring stock data, and a waterfall chart, which
is great for tracking a quantity subject to up and down changes. The problem is that both of these charts show only up _or_ down values in a given time period, while we want to show up _and_ down for each timeperiod.
MUSEUM VISITOR DATA
We have the following data, which lists the number of visitors entering (Entrée) and leaving (Sortie) during the hour, and the net visitors . The formula in cell D2, filled down the list, is =SUM(B$2:B2)-SUM(C$2:C2) So let’s plot the data in a clustered column chart to see what wehave.
No, that doesn’t show us anything. How about a stacked column chart? What we want will look like this, but have blue arrows before each column showing visitors who have arrived and orange arrows after each column showing visitors who have left. There is an easy way to do this, using all columns and the sometimes tricky custom error bars. And
there is a hard way, using XY series and tricky custom error bars. There’s another hard way, which uses easier error bars but more complicated stacked columns that require a complicated data layout, so I think I’ll avoid that one.THE EASY WAY
Starting with the same initial data as above, I made a clustered column chart using time (Heure) as X values and Visitors as Y values. I’ve changed the series overlap from the default of -27% to 0% and the gap width from the default of 219% to 100%. I don’t know who thought up those defaults. I copied the Visitors data, selected the chart, and pasted. Two series with the same values and name, that’s what I wanted. Really. Then I repeated the copy and paste, so I have the same data in the chart three times. This will give me the total visitors as the middle column, with a column before and after this where I can draw myarrows.
I changed the colors of the bars, because I want the totals to be gray, and I want blue and orange to represent increases and decreases. And I’ve renamed the outer bars Before and After. I added error bars to the Before and After series, using the little plus-sign icon floating beside the chart. I don’t need to display the blue and orange columns anymore, so I’ve formatted them with no fill, to be transparent. Time to format the error bars. Be sure to choose the _No End Caps_ option, and then under _Error Amount_, select _Custom_, and then click the _Specify Value_ button. I used the data in the Entrée and Sortie columns as custom values. When you click the _Specify Value_ button, you get this tiny little dialog, with even tinier little edit boxes for selecting the range. The values are initially ={1} for both: change the positive value to ={0} and select the Entrée values for the negative. I’ve been complaining about the tiny Custom Error Bars dialog for a long time. Here’s what a more reasonably sized dialog would look like. See that, Microsoft? I did it in 5 minutes in MS Paint. Note, for the Sortie error bar values, they are off by one row, so you need to select the range starting in the second cell, so use $C$3:$C$11. Here’s how the custom error bars look. Format the error bars with the blue and orange colors. Make the lines a bit thicker; I’ve used 1.5 pt which is 2 pixels. Give the blue error bars a starting arrowhead, and the orange error bars an endingarrowhead.
A little clean up. I deleted the legend, and adjusted the chart data range to leave out the meaningless blank at 18:00. You have some leeway in the spacing of the columns, by adjusting the gap width. In the chart above, the gap width is 100, meaning the space between the clusters is 100% as wide as a single column. The hidden columns that the arrows occupy are 100% of this width as well, so there is a reasonably wide space between arrows. If you want to decrease the spacing, you can decrease the gap. The chart below has the minimum gap width of 0%. If you had kept the default gap width of 219%, the columns and arrows would be rather far apart. You can set the gap width as high as 500%, which looks so ridiculous that I’m not showing it..THE HARD WAY
The easy approach used two column chart series to hang the error bars on. The hard approach uses two XY scatter chart series. I’ve inserted a column of X values before the Y values (Visitors) in mydata.
I selected the time (Heure) and Visitors, and created a column chart. I’ve recolored it gray, and applied overlap of 0% and gap width of150%.
I copied the X and Visitors data in columns D and E, selected the chart, and used Paste Special (Ribbon > Home tab > Paste dropdown > Paste Special) to add the data to the chart as a new series in columns, series name in first row, categories in first column. The result is a second set of columns with the same values as the first. I’ve recolored the columns blue and renamed them Entrée. I right-clicked on the new series and chose Change Series Chart Type from the pop-up menu. I changed the chart type for Entrée to Scatter (arrow 1), and I unchecked the Secondary Axis box beside it (arrow 2). The result is a combination chart with a set of gray columns and a set of blue markers, which I have reformatted into a large blue circlewith no fill color.
The X values I’ve used, 1, 2, 3, etc., align with the bars at the first, second, third, etc. categories. What I need is to subtract a little bit from these X values to move the blue circles to the left of the columns, and add the same amount to these X values so another set of circles will sit to the right of the columns. So I’ve added two more columns to my data, X-Before and X-After. I put a small value into cell D13. The formulas in the added columnsare:
Cell E2: =D2+$D$13 Cell F2: =D2-$D$13 Keeping my original X values in their own column and using a cell to hold my small amount to add and subtract make it easier to make adjustments later, to cells I can see, rather than to formulas where the amounts are hidden. I changed the X values of my Entrée series to X-Before. Looking good. So I copied my X-After and Visitors data, and used Paste Special as before to add a third series to the chart. Since I’ve already changed the previous series to a scatter type on the primary axis, Excel made my new series also a scatter type on the primary axis. I’ve renamed the new series Sortie, and made it a large orange circle with no fill. So far so good, as long as Disney doesn’t come after my for trademark infringement. So let’s bring this puppy home. Add error bars to the Entrée and Sortie series, using the plus icon floating beside the chart. Select and delete the horizontal error bars. Let’s format the error bars. Choose the _No End Caps_ option, and under _Error Amount_, select _Custom_, and then click the _SpecifyValue_ button.
Use the data in the Entrée and Sortie columns as custom values for the error bars. You get this tiny little dialog I showed before, with microscopic edit boxes for selecting the range. The error bar values are initially ={1} for both: change the positive value to ={0} and for negative select the Entrée values. Isn’t this Custom Error Bars dialog difficult to use? Here’s a better sized dialog. See that, Microsoft? I fixed it in 5 minutesusing MS Paint.
Note that the Sortie error bar values are off by one row, so you need to select the range starting in the second cell, so use $C$3:$C$11. Here’s how the custom error bars look. Format the error bars with the blue and orange colors and the lines thicker; I’ve used 1.5 pt (2 pixels). Give the blue error bars a starting arrowhead, and the orange error bars an ending arrowhead. Format the Entrée and Sortie series to use no markers. Finally a little clean-up. Delete the legend. Make all of the chart series one point shorter to remove the meaningless 18:00 category (stop at row 10 instead of row 11). Set the Y axis minimum to zero (the arrows between 12:00 and 13:00 actually drop slightly into negative territory, but that’s not crucial to the story). The chart above used a gap width of 125% and a value of 0.333 to add or subtract from X to position the Entrée and Sortie arrows. You can adjust these together to change the chart’s appearance. Below, for example, I’ve applied a gap width of 75% and used an X increment of0.41.
RELATED POSTS
The hourly flow chart in this tutorial uses a similar approach to that in Revenue Chart Showing Year-Over-Year Variances.
There is a somewhat dated but still relevant tutorial on this blog that tells you all about Custom Error Bars in Excel Charts. Here is
the cramped Custom Error Bars dialog in Excel 2007 and Windows 7, even narrower than Excel 365’s. Here is a complete list of error bar related articles on the PeltierTech blog.
* Error Bars in Excel 2007 Charts * Custom Error Bars in Excel Charts * Floating Bars in Excel Charts * Error Bar Gantt Chart * Callout Labels with Error Bars * Step Charts in Excel * Swimmer Plots in Excel * High-Low Line Alternatives in Excel Charts * Custom Axis Labels and Gridlines in an Excel Chart * Add a Horizontal Line to a Column or Line Chart: Error Bar Method * Add a Vertical Line to a Column or Line Chart: Error Bar Method POSTED: Monday, February 3rd, 2020 under Combination Charts.
TAGS: error bars .
COMMENTS:
5
Filed Under: Combination ChartsTagged With:
error bars
JON’S TOOLBOX – A NEW UTILITY FROM PELTIER TECH Thursday, January 23, 2020 by Jon Peltier17 Comments
Thursday, January 23, 2020 by Jon Peltier Peltier Technical Services, Inc., Copyright © 2019, All rights
reserved.
JON’S TOOLBOX is a simple Excel VBA add-in that provides a handful of useful functions. I use it when I am preparing or teaching classes, writing blog posts, and working on projects. Jon’s Toolbox is not a replacement for Peltier Tech Charts for Excel , nor does it require Peltier Tech Charts for Excel in order to operate properly. The two utilities run independently, and I have both installed on each of my computers. WHAT JON’S TOOLBOX DOES I’ll do a quick run-through of the menu controls to give you a sense of what Jon’s Toolbox is all about. Peltier Tech Charts for Excel is a large collection of advanced features, for creating custom chart types which are not native to Excel, for manipulating charts, and for controlling data that charts are linked to. Jon’s Toolbox is a self-contained set of functions that help with quickly inserting data and charts, and with quickly formatting charts and worksheets. JON’S TOOLBOX RIBBON COMMANDS This is a quick look at the custom ribbon tab of Jon’s Toolbox. Click on the image to see it full size in a new browser tab. The ribbon includes groups of controls, such as _Insert Data – Chart – Pivot_, _Chart and Font Size_, _Chart Tools_, _Range Tools_, _Range and Chart_, plus a teaser at the end, _From the Blog_. INSERT DATA – CHART – PIVOT _Insert Data – Chart – Pivot_ has controls to quickly insert data, charts, or a whole data-pivot table-pivot chart ensemble. This is nice when I’m running a demo of my other software, preparing for a class, writing documentation or a blog post. It’s especially nice if I’m running a class and I want to demo a feature of Excel and I want some data for a chart quickly, and I don’t want to make everyone watch me mistype a bunch of numbers.DATA RANGE
_Insert Data Range_ inserts a handy dummy data set, ready for me to demonstrate Excel’s features, or the features of some software I’ve written. If the active sheet is blank, it puts the data there, otherwise it inserts the data onto a clean worksheet. Nothing fancy, but it’s a good simple data range for demonstrationpurposes.
COLUMN CHART, BAR CHART, ETC. There are chart buttons for all of the chart types I routinely use. Yes, it includes pie charts, get over it. Each of the buttons is a split button. I’ll illustrate the functionality with the _Column Chart_ button. If you click on the icon (left), the program creates the chart type shown. If you click on the dropdown (middle), you get a dropdown list with the options for that chart type (right). When you use one of the options from the dropdown, the split button icon changes to that chart type, which is created next time you clickon the icon.
The way a chart is inserted varies, depending on what is selected. * If no data is selected, the program inserts a new worksheet, it inserts the dummy data range (see _Data Range_ above), and it inserts a chart one row below the data range using this data. * If a data range is selected, or if a cell in a block of data is selected, a new chart of the selected type is inserted, one row belowthe data range.
* If a chart is selected, the program inserts a new chart of the selected type, offset slightly from the original chart, using the same data as the previously active chart. The charts created here are essentially the same as those from Excel’s own Insert tab, the same size and same series formatting, but they follow my own preferences for that chart type. For example. Excel’s column charts have too much space between clusters, and unneeded space between columns in a cluster. Jon’sToolbox fixes this.
Excel’s bar charts draw the axis in the opposite order of how the data appears in the worksheet. Jon’s Toolbox fixes this, while also changing to an aspect ratio that better accommodates the chart. In addition to being spelled with too many silent letters, Excel’s doughnut charts have too fat a white line surrounding the individual points, the default hole size of 75% is ridiculously large, and the legend is placed below the chart, shrinking the doughnut’s height and leaving wide swaths of white space on either side of the doughnut. Jon’s Toolbox creates a donut chart spelled with no excessive letters; with a thin white outline around the points; with a default hole size of 66% for one ring (one series), 50% for two rings, and 33% for three or more rings; and with its legend on the right side of the chart, allowing the donut to fill more of the space occupied by the chart. In addition, Jon’s chart is closer to square, which means the chart takes up less space. DATA, TABLE, AND CHART _Data, Table, and Chart_ inserts a new worksheet. The worksheet contains a Table of data, a Pivot Table constructed using the data in this Table, and a Pivot Chart based on this Pivot Table. Click on the screenshot to view in a new window. This is a great way to quickly show someone about Tables and PivotTables and Charts.
CHART AND FONT SIZE
Are the default charts in Excel the right size for your purposes? In the US, the defaults are 5 inches wide and 3 inches tall, and frankly, that’s way larger than most of my charts need to be. Also, that 14-point chart title is much too large. The _Chart and Font Size_ group of controls provides the following predefined set of alternative sizes for charts and their fonts. The different chart and font sizes look something like this. Note that they are independently applied to each chart. For my blog posts, I can easily fit two of the small charts, usually with medium font, side by side within a column of text. Like many of the features of Jon’s Toolbox, if you select multiple charts before clicking the button, the formatting is applied to allselected charts.
CHART TOOLS
Jon’s Toolbox has a handful of useful chart tools. The default legend in an Excel chart is pretty widely spaced out (below left). _Condense Legend_ closes it up (below right). You can easily _Reverse Series_ (and then unreverse them) in a chart, for example in a clustered column chart… …or in a stacked column chart. By default, Excel plots a bar chart with the vertical axis in exactly the opposite order as the data appears in the worksheet. This makes sense on some level, but not from the standpoint of usability. Click _Flip Bar Chart_ and fix your bar charts instantly. (Click it again to get the upside-down version back.) Note that if you use Jon’s Toolbox to insert a bar chart, it is automatically plotted the right way. The default Excel color palette is not too bad, but if large areas of your chart are filled with the colors, the chart may seem oversaturated (below left). In Excel’s color pickers, the next lighter shade is 40% brighter, which may make the charts washed out. _Apply 20% Brightness_ applies an intermediate shade to the selected series (below right). Note that you have to select and format eachseries separately.
Finally, the _Copy Bitmap_ button copies the active chart to the clipboard in bitmap format, saving you several clicks (Home tab > Copy dropdown > Copy as Picture > select Appearance and Format). Simply switch to PowerPoint or your image editing software and Paste.RANGE TOOLS
Jon’s Toolbox contains a set of range formatting tools. (FORMAT AS) CHART DATA When you select a chart or a series in a chart, Excel highlights the associated data in the worksheet. _Chart Data_ applies this formatting more permanently to the worksheet. The colors are the same, it only lacks the small square handles on the corners of the shaded regions. Great for tutorials and illustrations, though. Click the _Chart Data_ icon, and different things happen depending onwhat is selected.
* If a chart is selected, the data range for the whole chart isformatted.
* If a series in a chart is selected, the data range for that seriesis formatted.
* If a range is selected, the range is formatted as if it were used in a chart. If more columns that rows are selected, the range is formatted as if series are plotted by row, otherwise it is formatted as if series are plotted by column. If you need more control over the formatting, click the _Chart Data_dropdown.
You can format a range including X and Y values and series names as if plotted by row or by column. You can format a range as if it contains only series names, X values, or Y values. In addition, you can format a range so it looks like a selected range (below left), as a copied range (below center), or by clicking the two buttons in succession, as a selected range that’s been copied (belowright).
I have found these very useful when writing blog posts, class notes, or documentation for my software.BORDERS
Excel has an extensive built-in Borders menu. And the borders it creates are stark, in-your-face dark black. Sometimes I even want to pluck out my own eyeballs. (My daughter once determined that any statement is funnier if you say “eyeballs” than if you just say “eyes”, and I agree. She’s a scientist working on visual perception using primates.) Below are Outside Borders, Thick Outside Borders, All Borders, and a combination of All Borders and Thick Outside Borders. Pretty harsh. _Nice Borders_ applies more visually appealing borders to a selected range. Medium is a medium gray, darker than the default cell boundaries, but not as irritating as black, while light is a light gray similar to the default cell boundary color. “Nice” borders means a medium gray outline with light gray internal boundaries, and the other options format internal borders and outlines, or just outlines, with medium or light gray borders. These are illustrated below with cell gridlines (Gridlines on the View tab) displayed on the left and turned off on the right. _Medium Borders_ applies medium gray borders to each cell in theselection.
_Medium Outline_ applies a medium gray border around the outer cell boundaries of the selection. _Light Borders_ applies light gray borders to each cell in the selection. The light gray is indistinguishable from the default cell gridlines. I use this to put gridlines only on a certain range, while turning off gridlines elsewhere. _Light Outline_ applies the light gray border around the outer cell boundaries of the selection. _Nice Borders_ applies a medium gray outline around the selection and light gray borders to cell boundaries within the selection. If I am making screenshots for documentation, I will often either hide the default gridlines and use _Light Borders_ (first screenshot below), or I will show the gridlines and use _Nice Borders_ (secondscreenshot below).
When you select one of these Border options, it becomes the new icon and default action for the command.CLEAR FORMATS
_Clear All Formats_ will clear all formats, except for number formatting, from a selected range. This is nice, because sometimes you need to get rid of some ugly border and fill colors, but recreating number formats is a chore. In the Clear All Formats dropdown, the options are to * Clear border formatting, * Clear border and fill formatting, * Clear all formatting _except for number formatting_.PAGE BREAKS
_Hide Page Breaks_ simply removes those unsightly page break dashed lines that make me crazy.RANGE AND CHART
The _Range and Chart_ group has some controls that help you control how the chart interacts with the worksheet. _Select Data_ allows you to quickly select a new source data range forthe active chart.
_Cover with Chart_ lets you select a range for the active chart to cover. The chart is repositioned and resized to cover the selectedrange.
_Stretch Chart_ resizes the active chart (or all selected charts) to fill the cells it partially covers. _Center Chart_ moves the active chart (or all selected charts) so it is centered within the range of cells it covers.FROM THE BLOG
Finally, Jon’s Toolbox includes some nice little programs that were previously presented in the Peltier Tech Blog. These programs were featured in the following blog posts: * Add Totals to Stacked Column Chart(but it
works on stacked bar, stacked area, and stacked line charts, too) * Trendline Calculator for Multiple Series * Built In and Custom Lists in ExcelHOW DO YOU GET IT?
For a limited time only, Jon’s Toolbox is available for $29. Click this button to make your purchase. Check your shopping cart with this button.BULK DISCOUNT
If you purchase multiple licenses, you qualify for a volume discount, shown in the table below. The discount is automatically applied in the shopping cart when you enter your quantity.GUARANTEE
Like all of Peltier Tech’s software products, Jon’s Toolbox is guaranteed for 90 days from the date of purchase. If you are unsatisfied for any reason, contact Peltier Tech directly to request a refund. Any feedback is appreciated, but none is necessary.LICENSE
Each user of Jon’s Toolbox requires a license. Each license allows the user to install Jon’s Toolbox on up to two computers of which they are the primary user. If a user gets a new computer, the software can be uninstalled from the old machine and reinstalled onto the newone.
INSTALLATION
Jon’s Toolbox is a simple Excel VBA add-in, which can be installed in Excel for Windows or Excel for Mac. Follow instructions at Install an Excel Add-In , and you’ll be working in no time.QUESTIONS?
Contact Peltier Tech with any questions about Jon’s Toolbox. POSTED: Thursday, January 23rd, 2020 under Utilities.
TAGS: .
COMMENTS:
17
Filed Under: Utilities BAR LENGTHS ON A CHART, WHAT DO THEY EVEN MEAN? Thursday, January 9, 2020 by Jon Peltier12 Comments
Thursday, January 9, 2020 by Jon Peltier Peltier Technical Services, Inc., Copyright © 2019, All rights
reserved.
THE MISLEADING CHART My friend and colleague Patrick Matthews, a former Excel MVP, posted a screenshot of an unusual bar chart on his Facebook page. The chart was taken from _What does the public say about impeaching Trump?_, the last section of a Washington Post article titled What happens next in the impeachment of President Trump? Patrick’s comment says it all: “Bar lengths on a chart, what dothey even mean?”
At the risk of opening a torrent of political comments, I’ve reproduced the chart here. Take a close look at the bar lengths in the first chart. The 12% bar is over half as long as the 85% bar, where in a bar chart with proportional bars, the 12% bar should be about 1/7 as long. But at least the 49% bar is slightly longer than the 47% bar, and they are in between the 12% and 85% bars. The same holds true of the bar lengths in the second chart. Someone responded to Patrick’s post, wondering how they came up with those bar lengths. After the analysis in the previous paragraph, I replotted the data, set the axis scales to -100% to +100%, and set the vertical axis to cross at -100% on the horizontal axis. Nailed it! Well, not exactly. As I sometimes do, I overanalyzed the charts. I’ve stripped most of the text from the WaPo graphic, replaced the outlines of my charts with red lines, and stretched my charts so they overlaid the WaPo plot. It turns out that the axis minimum was really -92%, so my wild guess of -100% was pretty good. I’ve set the gridline spacing so that 0% and +92% are shown on the chart, and the far right edge of the plotarea is at +100%.
I don’t think the graphic artist really used an axis minimum of -92%. I’m sure they started with 0%, then decided to fill in some white space by dragging the left edges of each bar while keeping the right edge in place. They filled in the space, all right. But by doing so, they obscured the differences between the values. It’s the same issue that occurs when people start their axis at a value greater than zero, so the differences between values are accentuated. But now the axis and the bars start well below zero, and the differences are minimized.FIXING THE CHART
My next step was to take my two charts, and set their axis minimum to 0%. These two charts now accurately show the relative percentages.IMPROVING THE CHART
Those last two charts were a big improvement. But if we’re expected to compare the values, shouldn’t the bars all be in a single chart? Below I plotted the negative of one set of data, so the bars stretch in opposite directions, the way they do in population pyramids. Let’s call this a diverging bar chart. Then I remembered why I dislike population pyramids, as I discussed ages ago in Tornado Charts and Dot Plots. It’s hard
to compare bars that reach away from each other. It would be easier to compare the values of any two bars if they start at one horizontal position (the vertical axis) and stretch in the same direction (to the right). So I created this clustered bar chart: An alternative is to plot one set of bars from left to right, and the other from right to left. It’s a converging rather than a diverging bar chart. This makes individual bars more difficult to compare, as in the population pyramid lookalike above. But the white spaces clustered between the colored bars represent the percentages of each category who have no opinion. What do you think? Not about the topic of the chart, but about the construction of the chart. Do you prefer the diverging bar chart, the clustered bar chart, the converging (stacked) bar chart, or somethingelse entirely?
POSTED: Thursday, January 9th, 2020 under Charting Principles.
TAGS: bar chart .
COMMENTS:
12
Filed Under: Charting PrinciplesTagged With:
bar chart
ORDER OF SERIES AND LEGEND ENTRIES IN EXCEL CHARTS Tuesday, December 31, 2019 by Jon Peltier1 Comment
Tuesday, December 31, 2019 by Jon Peltier Peltier Technical Services, Inc., Copyright © 2019, All rights
reserved.
CHART SERIES AND LEGENDS In Excel charts, series are drawn in a particular order and legend entries are listed in their own particular order, based on series number, series chart type, the axis a series is plotted on, and other features, like axis category order and whether series are stacked. People often ask how to move series within a chart or within a legend. This article should help explain what is even possible.SINGLE CHART TYPE
If a chart has one type of series, all on the primary axis, the series are plotted in the order that they are added to the chart. The legend usually lists them in this order as well.LINE CHARTS
The line charts below illustrate the layering of series. Whether lines with markers or just lines, the second series in red is always plotted in front of the first series in blue. Even to the extent that the red line in the bottom right chart covers the blue markers. The legends all list the blue series first, then the red series. An interesting inversion occurs if one series has markers and no lines. The markers only series is plotted in front of the line series (top row of charts below) or in front of the lines with markers series (bottom row). In both right-hang charts, the blue markers of the first series or drawn in front of the red series. The legends still list blue first, then red. XY scatter charts show the same behavior as line charts, with series plotted in order except in the case where one series has markers andno lines.
COLUMN AND BAR CHARTS In unstacked horizontal bar charts, legend entries appear in the same order that the series appear in the chart. Below left, red bars and legend entries appear before blue. Below right, the categories have been reversed in the vertical axis. Now blue bars and legend entries appear before red. This swapping of the legend to match the arrangement of the bars helps with interpretation of the chart. You might expect column charts to show the same alignment of legend entries with plotted points. In the original chart, below left, the blue series 1 columns and legend entry appear before the red series 2 columns and legend entry. Reversing the categories in the horizontal axis does reverse the positions of the bars, but the legend keeps its original order, so the legend entries are now out of sync with the columns.EFFECT OF STACKING
In stacked charts (area, bar, and column charts), each series is stacked on the previous series, so the chart shows totals of all series at each category. The legends are rearranged to list series in the order they appear in the stack. This helps the viewer interpretthe plotted data.
_Note: You can also stack line chart series, but it’s not a good idea to do so; stacked line charts can be very confusing._ In the unstacked area chart below left, series 1 in blue is plotted first, and series 2 in red is plotted in front, partially obscuring series 1. The legend lists the series in this order. In the stacked area chart below right, the blue area is plotted first and the red area is stacked on top. The legend is reversed, so that series 2, plotted at higher Y values than series 1, is also listed higher in the legend. In an unstacked bar chart (below left), the legend lists series in the order they appear: red above blue. In a stacked bar chart (below right), the legend also lists series in the order that they are stacked, red to the right of blue. In an unstacked column chart (below left), the legend lists series in the order they are plotted: blue before red. In a stacked column chart (below right), the legend lists series in the order that they appear,red above blue.
MIXED CHART TYPES (COMBINATION CHARTS) The order in which data is plotted in the chart and listed in the legend becomes more complicated when multiple chart types are used in the same chart. The rules are straightforward, but they aren’t documented anywhere, so people get confused. SERIES ORDER BY CHART TYPE The order that series are plotted in the chart and listed in the legend follows this order of chart types: Area, Column and/or Bar, Line, and XY Scatter. Changing the plot order (by rearranging series in the _Select Data Source_ dialog or by changing the last argument in the Series Formula ) will rearrange series within a type, but will not move series out of their plot type order. Start with this simple data and insert a clustered column chart (usually the default type). Then right click on any series and select _Change Series Chart Type_ from the pop up menu. Change series “Area 1” to an area, keep “Column 2” as a column, and change “Line 3” to a line. The blue area series is drawn behind the other series and listed first in the legend, the red columns are drawn in front of the blue area and listed in the middle, and the gold line is plotted in front of the others and listed last. Big deal, you may think, that’s the order that the data was arranged in the worksheet. Reverse all that, and the line will be drawn first, behind the others, while the area will be drawn last, obscuring therest.
Below is the data in reverse order and the resulting column chart. Again, right click on any series and select _Change Series Chart Type_. Change “Line1” to a line, keep “Column 2” as a column, and change “Area 3” to an area. The gold area series is drawn behind the other series and listed first, the red columns are drawn in front of the gold area and listed next, and the blue line is plotted in front of the others and listed last. So we see that the chart type dictates the order in which series are drawn and listed, regardless of the order of series data within all of the data in the chart. We can extend this further to show that an XY Scatter series will be plotted in front of all the other series, regardless of where it falls in the chart source data. PRIMARY AND SECONDARY AXES Earlier I wrote that the order of series by chart type was Area, Column and/or Bar, Line, and XY Scatter. I placed Column and Bar together because if your chart contains both column and bar series, they are plotted in the same layer between areas and lines, and they are listed together in the legend. Their precise order depends on which axis each is assigned to. Below I’ve added a bar chart series to the first combination chart above. A bar chart series cannot be plotted on the same axis group as another chart type, so in the chart below, the area, column, and line series are plotted on the primary axis, and the bar is plotted on the secondary axis, so that the gray bars are in front of the red columns. Note that the legend order is area first, line last, and column and bar in the middle. Because the column series is on the primary axis, it is listed before the bar series. Next I’ve added a bar chart series to the second combination chart above. I’ve plotted the bar on the primary axis, while the area, column, and line series are plotted on the secondary axis. Note that the gray bars are behind the red columns. The legend order is still area first, line last, and column and bar in the middle. Because the bar series is on the primary axis, it is listed before the columnseries.
The following chart has no bar series, but area, column, and line series on each of the primary and secondary axes. All areas are listed first, then all columns, then all lines; within each chart type, the primary series are listed before the secondary series. Legend ordering can be even more intricate. Below is four series of data and a column chart. In the chart below left, series 2 and 3 have been moved to the secondary axis. Note that the legend lists series 1 and 4 first for the primary axis, then series 2 and 3. In the chart below right, series 2 and 3 on the secondary axis have been changed to stacked columns. The legend still lists primary axis series 1 and 4 in that order, but lists secondary series 3 and 2 in the order they re stacked. Earlier I showed how a line chart series with markers and no line will be plotted in front of a later line chart series with a line (with or without markers). This happens if both series are on the same (e.g.,primary) axis.
Below left: red series 2 markers plotted in front of blue series 1 line and markers. Below right: blue series 1 markers plotted in front of red series 2 line and markers. If the series with markers only is on the primary axis and the series with the line (with or without markers) is on the secondary axis, the primary markers will not be plotted in front of the secondary series. Below left: blue secondary series 1 plotted in front of red primary axis series 2. Below right: red secondary series 2 plotted in front of blue primary series 1. LEGENDS WITH MANY ENTRIES Legends can get complicated when there are many charted series and many entries in the legend. LEGENDS WITH MULTIPLE ROWS AND COLUMNS The following chart has a legend across the bottom, listing all 8 series in a horizontal row. If you shrink the width of the chart, eventually the legend will no longer fit. Excel converts the legend to two rows. Shrink the chart further, and the legend will change to three rows, then four. Excel tries to place the same number of entries into each row: 8 entries in the original legend, then 4+4 entries, then 3+3+2, andfinally 2+2+2+2.
It’s a little different with a vertically aligned legend. Below left is the same chart as above, with the legend listing the series along the right edge of the chart. Shrink the chart so that the legend no longer fits, and Excel does not convert it to two columns, instead it simply drops items off the list. In its automatic legends, Excel doesn’t like the entries to get too close together, but you can manually change the height of the legend, and more entries will fit. In the chart below left, the chart is the same size, and so is the font, but I’ve slightly stretched the legend and all eight entries appear. You can even shrink the legend, and push the legend entries together. (Shrink it too much, and again, you will lose items off the bottom of the list.) Alternatively, you can widen the legend, and Excel will add a column of legend entries (below right). If your series names have different lengths but they fit in one row, Excel will position legend entries so the spaces between them areequal.
When you shrink the chart so that the legend reverts to multiple rows, Excel gives all legend entries the same amount of room. Note the wide spaces between the short legend entries below. I’ve used a light gray border on the legend to help illustrate this behavior. This effect continues with more rows of legend entries. PARTIAL LIST OF SERIES I was working on a research project management dashboard with a client, and he was showing a stacked area chart that showed data about his various projects over time. Like many analyses of this sort, most of the total was due to a small number of items. He wanted to show a list of the top N projects in the legend. He was able to do it once, but then couldn’t remember how he did it. In my mock-up below, I have eight “projects” stacked up, and I want to show the largest four contributors to the total. These two charts have all eight series listed in the legend. In the next two charts, I have reduced the sizes of the legends so that half of the legend entries have disappeared. The left chart has the vertical list I want, except it shows the four smallest series. The right chart shows the four largest series, but they are in a horizontal list. But I can start with the horizontal legend to produce the vertical list I want. Below left, I have moved the legend to the top right corner of the plot area. Below right I have made the legend taller and narrower to force Excel to list the entries in one column. Finally a little cleanup and I have the desired list of the fourlargest series.
The client was thrilled when I showed how he could reproduce his top ten list of projects by starting with his legend at the bottom of the chart instead of the right side. OTHER POSTS ABOUT LEGENDS I’ve written several other articles about legends in this blog: * Legend Entry Tricks in Excel Charts * Legends in Excel Charts – Formats, Size, Shape, and Position * Double Legend in a Single Chart * Slim Down Those Fat Legend Keys POSTED: Tuesday, December 31st, 2019 under Charting Principles.
TAGS: Legend , Legend Entry, series order
.
COMMENTS:
1
Filed Under: Charting PrinciplesTagged With:
Legend , Legend Entry, series order
* Go to page 1
* Go to page 2
* Go to page 3
* Interim pages omitted …* Go to page 122
* Go to Next Page »PRIMARY SIDEBAR
* Peltier Tech Blog
* About Peltier Tech* About Jon Peltier
* Peltier Tech Consulting * Peltier Tech 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