Menu
The other day I got a question from Todd, an EngineerExcel.com subscriber. He uses Excel to create charts of cam position, velocity, and acceleration. The industry-standard way of graphing this data is to include all three curves on the same chart, like in the image below. The challenge is that all three curves have very different scales, with acceleration being the smallest.
This makes it hard to view the acceleration curve on the chart without a unique axis. So he wanted to know if there was a way to add a third axis to a scatter chart in Excel. Unfortunately, there isn’t, but we can fake one by creating another data series with a constant x-value, like I’ve done in the image below. Note: Want to learn even more about advanced Excel techniques? In the three-part video series I'll show you how to easily solve engineering challenges in Excel.聽 It’s not a perfect solution, but to my knowledge, it’s the best we can do in Excel with the currently available toolset. Select a Scaling Factor and Scale the Data Excel allows us to add a second axis to a scatter chart, we’ll use this for velocity and acceleration.
However, we’ll want to scale the acceleration data so that it fills the chart area. To do this, I entered an appropriate scaling factor in the spreadsheet and created a new column of scaled acceleration data by multiplying the original acceleration data by the scaling factor. Decide on a Position for the Third Y-axis The third y-axis (which will really be a data series) will be on the chart area, so it has to cross the x-axis at some point.
Creating a Two Axis Chart in Excel 2007 / Excel 2010 / Excel 2013 July 23, 2012 January 18, 2018 by Vinai Prakash Up to Microsoft Excel 2003, there was a in-built Custom Chart Type called the 2-Axis Chart. Instead, the graph x-axis has numbers instead of the text I want. This question is similar to Excel scatter chart, with grouped text values on the X axis, but the answer that is given would not make sense given the number of data points I have. To be clear I want to label the axis, not the individual data points.
I picked a value of 285 degrees, since the position, and therefore the velocity and acceleration, are zero beyond this point. Of course, we can always change this later.
Select the Data for the Chart Next, the angle, position, velocity, and scaled acceleration data. I put the velocity and scaled acceleration data on the secondary axis of the chart. The scaled acceleration data could have been on the primary axis. In that case, I would have had to use a different scaling factor. I also added some color to the axes and axis labels for clarity. Create Three Arrays After inserting the chart, I created three arrays:.
An array of the x-axis values for the third “y-axis”. An array of unscaled values that are roughly on the same order of magnitude but also fully encompass the original acceleration data. For example, the range of acceleration data was from -0.0005 to 0.0005. So I chose -0.001 and 0.001 as limits. An array of scaled (calculated) values, using the scaling factor from above.
These arrays were used to create the third y-axis in the next step. Note: Want to learn even more about advanced Excel techniques?
In the three-part video series I'll show you how to easily solve engineering challenges in Excel.聽 Create an “axis” with a fourth data series Next, I added a fourth data series to create the third “axis”. The x-values for the series were the array of constants and the y-values were the unscaled values. I also modified the line style to match the weight of the other gridlines, added markers (the kind that look like plus signs), and changed the color of the line and marker to match the data series (green). Add data labels – select data label range Axis labels were created by right-clicking on the series and selecting “Add Data Labels”. By default, Excel adds the y-values of the data series.
In this case, these were the scaled values, which wouldn’t have been accurate labels for the axis (they would have corresponded directly to the secondary axis). However, in Excel 2013 and later, you can choose a range for the data labels. For this chart, that is the array of unscaled values that was created previously.
So I right-clicked on the data labels, then chose “Format Data Labels”. Then, in the Format Data Labels Task Pane, I selected the box next to “Values from Cells”.
This opens a small dialog box that allowed me to select a range. I chose the array of unscaled values and clicked OK.
Add a Text Box for the Axis Title Finally, I added a text box next to the axis and typed in the title. Updating the Chart If the data is ever updated, it’s simple to change the scaling factor and/or x-axis position of the third y-axis. Just modify the values in the worksheet, and the third y-axis will update automatically.
Excel for Office 365 Word for Office 365 Outlook for Office 365 PowerPoint for Office 365 Excel for Office 365 for Mac Word for Office 365 for Mac PowerPoint for Office 365 for Mac Excel 2019 Word 2019 Outlook 2019 PowerPoint 2019 Excel 2016 Excel 2019 for Mac PowerPoint 2019 for Mac Word 2019 for Mac Word 2016 Outlook 2016 PowerPoint 2016 Excel 2013 Word 2013 Outlook 2013 PowerPoint 2013 Excel 2010 Word 2010 Outlook 2010 PowerPoint 2010 Excel 2016 for Mac PowerPoint 2016 for Mac Word 2016 for Mac Excel Starter 2010 For most chart types, you can display or hide chart axes. To make chart data easier to understand, you can also change the way they look. Important: This article does NOT cover changing the scale of chart axes. For information about how to change to the scale, see:.
Learn more about axes Charts typically have two axes that are used to measure and categorize data: a vertical axis (also known as value axis or y axis), and a horizontal axis (also known as category axis or x axis). 3-D column, 3-D cone, or 3-D pyramid charts have a third axis, the depth axis (also known as series axis or z axis), so that data can be plotted along the depth of a chart.
Radar charts do not have horizontal (category) axes, and pie and doughnut charts do not have any axes. Vertical (value) axis Horizontal (category) axis Depth (series) axis The following describe how you can modify your charts to add impact and better convey information. For more info on what axes are and what you can do with them, see. Note: The following procedure applies to Office 2013 and newer versions. Display or hide axes. Click anywhere in the chart for which you want to display or hide axes.
This displays the Chart Tools, adding the Design, and Format tabs. On the Design tab, click the down arrow next to Add chart elements, and then hover over Axes in the fly-out menu. Click the type of axis that you want to display or hide.
Adjust axis tick marks and labels. On a chart, click the axis that has the tick marks and labels that you want to adjust, or do the following to select the axis from a list of chart elements:. Click anywhere in the chart. This displays the Chart Tools, adding the Design and Format tabs. On the Format tab, in the Current Selection group, click the arrow in the Chart Elements box, and then click the axis that you want to select. On the Format tab, in the Current Selection group, click Format Selection.
In the Axis Options panel, under Tick Marks, do one or more of the following:. To change the display of major tick marks, in the Major tick mark type box, click the tick mark position that you want. To change the display of minor tick marks, in the Minor tick mark type drop-down list box, click the tick mark position that you want. To change the position of the labels, under Labels, click the option that you want. Tip To hide tick marks or tick-mark labels, in the Axis labels box, click None.
Change the number of categories between labels or tick marks. On a chart, click the horizontal (category) axis that you want to change, or do the following to select the axis from a list of chart elements:. Click anywhere in the chart. This displays the Chart Tools, adding the Design, Layout, and Format tabs. On the Format tab, in the Current Selection group, click the arrow in the Chart Elements box, and then click the axis that you want to select.
On the Format tab, in the Current Selection group, click Format Selection. Under Axis Options, do one or both of the following:. To change the interval between axis labels, under Interval between labels, click Specify interval unit, and then in the text box, type the number that you want. Tip Type 1 to display a label for every category, 2 to display a label for every other category, 3 to display a label for every third category, and so on. To change the placement of axis labels, in the Label distance from axis box, type the number that you want.
Tip Type a smaller number to place the labels closer to the axis. Type a larger number if you want more distance between the label and the axis. Change the alignment and orientation of labels You can change the alignment of axis labels on both horizontal (category) and vertical (value) axes. When you have multiple-level category labels in your chart, you can change the alignment of all levels of labels.
You can also change the amount of space between levels of labels on the horizontal (category) axis. On a chart, click the axis that has the labels that you want to align differently, or do the following to select the axis from a list of chart elements:. Click anywhere in the chart. This displays the Chart Tools, adding the Design and Format tabs. On the Format tab, in the Current Selection group, click the arrow in the Chart Elements box, and then click the axis that you want to select. On the Format tab, in the Current Selection group, click Format Selection.
In the Format Axis dialog box, click Text Options. Under Text Box, do one or more of the following:. In the Vertical alignment box, click the vertical alignment position that you want. In the Text direction box, click the text orientation that you want. In the Custom angle box, select the degree of rotation that you want. Tip You can also change the horizontal alignment of axis labels, by clicking the axis, and then click Align Left, Center, or Align Right on the Home toolbar. Change text of category labels You can change the text of category labels on the worksheet, or you can change them directly in the chart.
Change category label text on the worksheet. On the worksheet, click the cell that contains the name of the label that you want to change. Type the new name, and then press ENTER. Note Changes that you make on the worksheet are automatically updated in the chart.
Change the label text in the chart. In the chart, click the horizontal axis, or do the following to select the axis from a list of chart elements:. Click anywhere in the chart. This displays the Chart Tools, adding the Design and Format tabs. On the Format tab, in the Current Selection group, click the arrow in the Chart Elements box, and then click the horizontal (category) axis. On the Design tab, in the Data group, click Select Data. In the Select Data Source dialog box, under Horizontal (Categories) Axis Labels, click Edit.
In the Axis label range box, do one of the following:. Specify the worksheet range that you want to use as category axis labels. Type the labels that you want to use, separated by commas — for example, Division A, Division B, Division C.
Note If you type the label text in the Axis label range box, the category axis label text is no longer linked to a worksheet cell. Change how text and numbers look in labels You can change the format of text in category axis labels or numbers on the value axis. Format text. In a chart, right-click the axis that displays the labels that you want to format. On the Home toolbar, click the formatting options that you want.
Tip You can also select the axis that displays the labels, and then use the formatting buttons on the Home tab in the Font group. Format numbers. In a chart, click the axis that displays the numbers that you want to format, or do the following to select the axis from a list of chart elements:. Click anywhere in the chart. This displays the Chart Tools, adding the Design and Format tabs. On the Format tab, in the Current Selection group, click the arrow in the Chart Elements box, and then click the axis that you want to select. On the Format tab, in the Current Selection group, click Format Selection.
Under Axis Options, Click Number, and then in the Category box, select the number format that you want. Tip If the number format you select uses decimal places, you can specify them in the Decimal places box. To keep numbers linked to the worksheet cells, select the Linked to source check box. On a chart, click the axis that has the tick marks and labels that you want to adjust, or do the following to select the axis from a list of chart elements:. Click anywhere in the chart. This displays the Chart Tools, adding the Design, Layout, and Format tabs.
On the Format tab, in the Current Selection group, click the arrow in the Chart Elements box, and then click the axis that you want to select. On the Format tab, in the Current Selection group, click Format Selection. Under Axis Options, do one or more of the following:. To change the display of major tick marks, in the Major tick mark type box, click the tick mark position that you want. To change the display of minor tick marks, in the Minor tick mark type drop-down list box, click the tick mark position that you want. To change the position of the labels, in the Axis labels box, click the option that you want. Tip To hide tick marks or tick-mark labels, in the Axis labels box, click None.
On a chart, click the horizontal (category) axis that you want to change, or do the following to select the axis from a list of chart elements:. Click anywhere in the chart. This displays the Chart Tools, adding the Design, Layout, and Format tabs.
On the Format tab, in the Current Selection group, click the arrow in the Chart Elements box, and then click the axis that you want to select. On the Format tab, in the Current Selection group, click Format Selection. Under Axis Options, do one or both of the following:.
To change the interval between axis labels, under Interval between labels, click Specify interval unit, and then in the text box, type the number that you want. Tip Type 1 to display a label for every category, 2 to display a label for every other category, 3 to display a label for every third category, and so on.
To change the placement of axis labels, in the Label distance from axis box, type the number that you want. Tip Type a smaller number to place the labels closer to the axis. Type a larger number if you want more distance between the label and the axis. You can change the alignment of axis labels on both horizontal (category) and vertical (value) axes. When you have multiple-level category labels in your chart, you can change the alignment of all levels of labels. You can also change the amount of space between levels of labels on the horizontal (category) axis.
On a chart, click the axis that has the labels that you want to align differently, or do the following to select the axis from a list of chart elements:. Click anywhere in the chart. This displays the Chart Tools, adding the Design, Layout, and Format tabs. On the Format tab, in the Current Selection group, click the arrow in the Chart Elements box, and then click the axis that you want to select. On the Format tab, in the Current Selection group, click Format Selection.
In the Format Axis dialog box, click Alignment. Under Text layout, do one or more of the following:.
In the Vertical alignment box, click the vertical alignment position that you want. In the Text direction box, click the text orientation that you want. In the Custom angle box, select the degree of rotation that you want.
Tip You can also change the horizontal alignment of axis labels, by right-clicking the axis, and then click Align Left, Center, or Align Right on the Mini toolbar. You can change the text of category labels on the worksheet, or you can change them directly in the chart.
![Axis Axis](/uploads/1/2/5/3/125391294/824820014.jpg)
Change category label text on the worksheet. On the worksheet, click the cell that contains the name of the label that you want to change. Type the new name, and then press ENTER. Note Changes that you make on the worksheet are automatically updated in the chart. Change the label text in the chart. In the chart, click the horizontal axis, or do the following to select the axis from a list of chart elements:.
Click anywhere in the chart. This displays the Chart Tools, adding the Design, Layout, and Format tabs.
![X axis independent variable X axis independent variable](https://library.barnard.edu/sites/default/files/images/inline/slide43.jpeg)
On the Format tab, in the Current Selection group, click the arrow in the Chart Elements box, and then click the horizontal (category) axis. On the Design tab, in the Data group, click Select Data. In the Select Data Source dialog box, under Horizontal (Categories) Axis Labels, click Edit. In the Axis label range box, do one of the following:. Specify the worksheet range that you want to use as category axis labels. Tip You can also click the Collapse Dialog Box button, and then select the range that you want to use on the worksheet. When you are finished, click the Expand Dialog Box button.
Type the labels that you want to use, separated by commas — for example, Division A, Division B, Division C. Note If you type the label text in the Axis label range box, the category axis label text is no longer linked to a worksheet cell. You can change the format of text in category axis labels or numbers on the value axis. Format text. In a chart, right-click the axis that displays the labels that you want to format.
On the Mini toolbar, click the formatting options that you want. Tip You can also select the axis that displays the labels, and then use the formatting buttons on the Home tab in the Font group. Format numbers. In a chart, click the axis that displays the numbers that you want to format, or do the following to select the axis from a list of chart elements:. Click anywhere in the chart. This displays the Chart Tools, adding the Design, Layout, and Format tabs.
On the Format tab, in the Current Selection group, click the arrow in the Chart Elements box, and then click the axis that you want to select. On the Format tab, in the Current Selection group, click Format Selection. Click Number, and then in the Category box, select the number format that you want.
Tip If the number format you select uses decimal places, you can specify them in the Decimal places box. To keep numbers linked to the worksheet cells, select the Linked to source check box. Note Before you format numbers as a percentage, make sure that the numbers on the chart have been calculated as percentages in the source data, and that they are displayed in decimal format. Percentages are calculated on the worksheet by using the equation amount / total = percentage. For example, if you calculate 10 / 100 = 0.1, and then format 0.1 as a percentage, the number will be correctly displayed as 10%.
Add tick marks on an axis An axis can be formatted to display major and minor tick marks at intervals that you choose. This step applies to Word for Mac only: On the View menu, click Print Layout. Click the chart, and then click the Chart Design tab. Click Add Chart Element Axes More Axis Options. On the Format Axis pane, expand Tick Marks, and then click options for major and minor tick mark types.
After you add tick marks, you can change the intervals between the tick marks by changing the value in the Interval between marks box. All about axes Not all chart types display axes the same way. For example, xy (scatter) charts and bubble charts show numeric values on both the horizontal axis and the vertical axis. An example might be how inches of rainfall are plotted against barometric pressure. Both of these items have numeric values, and the data points will be plotted on the x and y axes relative to their numeric values. Value axes provide a variety of options, such as setting the scale to logarithmic. Other chart types, such as column, line, and area charts, show numeric values on the vertical (value) axis only and show textual groupings (or categories) on the horizontal axis.
An example might be how inches of rainfall are plotted against geographic regions. In this example, the geographic regions are textual categories of the data that are plotted on the horizontal (category) axis. The geographic regions will be uniformly spaced because they are text instead of values that can be measured. Consider this difference when you select a chart type, because the options are different for value and category axes. On a related note, the depth (series) axis is another form of category axis. When you create a chart, tick marks and labels are displayed by default on axes.
You can adjust the way that they are displayed by using major and minor tick marks and labels. To eliminate clutter in a chart, you can display fewer axis labels or tick marks on the horizontal (category) axis by specifying the intervals at which you want categories to be labeled, or by specifying the number of categories that you want to display between tick marks.
You can also change the alignment and orientation of the labels, and change or format the text and numbers that they display, for example, to display a number as a percentage.