Skip Navigation LinksHome > January/February 2011 - Volume 29 - Issue 1 > Creating an Excel Chart With a Double Axis to Compare Two Da...
CIN: Computers, Informatics, Nursing:
doi: 10.1097/NCN.0b013e3181fb5bf5

Creating an Excel Chart With a Double Axis to Compare Two Data Types

Johns, Tracy BSN, RN, CPHQ; Sewell, Jeanne P. MSN, RN

Section Editor(s): Thede, Linda Q. PhD, RN-BC

Free Access
Article Outline
Collapse Box

Author Information

Tracy Johns, BSN, RN, CPHQ, Critical Care Quality Coordinator, Medical Center of Central Georgia.

Jeanne P. Sewell, MSN, RN, Assistant Professor, School of Nursing, Georgia College & State University.

Key Points:

* Effective chart design

* Chart with a double axis to compare two data types

* Creating a combination bar/line chart

Nurses want to deliver safe, quality care and improve patient outcomes. To effectively do this, the performance of patient care processes must be measured, and the data then used for improvement. This requires the ability to create reports with aggregated data that allow people to visualize and analyze the events that affect patient care. By summarizing large sets of numbers, a well-designed chart (graph) can allow people to easily visualize the relationships in quantitative data and support effective decision making.

Charts have numerous uses in nursing. For example, to plan staffing levels, nursing administrators use charts to review the nursing unit occupancy rates. By graphing medication errors, nursing, pharmacy and safety personnel can analyze patterns and trends to use for decreasing the number and severity of errors.

In many of these situations such as showing falls and pressure ulcers over a given period, a simple bar or line chart will suffice. In these cases, the x-axis (horizontal scale) will show the category such as falls and pressure ulcers, whereas the y-axis (vertical scale) will show the quantity of each. However, if one wishes to show how RN staffing affects the number of falls and pressure ulcers, because this is measured in different units, it is necessary to add a second y-axis. This article will explain how to create a double-axis chart that uses data measured in two different measurement units to show a cause-and-effect relationship. Elements of effective chart design will be discussed first.

Back to Top | Article Outline


Few,2 a well-known information design expert once wrote, "Numbers are not intrinsically boring. Neither are they intrinsically interesting." (p15). A chart is both an artistic and scientific way to make numbers appealing and engage an audience. However, given all the "bells and whistles" current spreadsheets offer for charts, it is important to keep in mind that the real purpose of a chart is to show the data,1,2 not demonstrate artistic ability. The key to focusing on the data is to know the target audience and to emphasize the most important results or conclusions of the data. Explain your data in ways tailored to your audience, the goal being to increase their interest in the information on the graph. When creating a chart, evaluate your data carefully. The chart should include only the most important data and emphasize the key points.

Because charts represent data as visual objects, several design practices should be followed. Overall, it is best to keep the design clean and simple. This means removing anything that is not essential to the message of the data and summarizing detail that is not vital. Table 1 is a guide for effective chart design practices.

Table 1
Table 1
Image Tools

Creating a bar chart in Microsoft Excel (Microsoft, Redmond, WA) is relatively easy. Adding comparative data with another unit of measure (a second y-axis) and line chart type requires only a few extra steps. This combination of bar/line chart with a double-axis feature is not available in other spreadsheet software, such as Google Docs Spreadsheets or Calc. The chart created for this example uses Excel 2007 and displays 2008 falls and pressure ulcers in comparison to percentage of RN hours. The data source used for this demonstration was from the National Database of Nursing Quality Indicators.

Back to Top | Article Outline


When you open Excel, you will see a grid with columns and rows. Each rectangle in the grid is called a cell.3 The columns are labeled using letters, and the rows are numbered, resembling the organization of a geographical map. A tabbed menu is located at the top of the work screen. (For help with Excel, see For information about chart terminology, use the Excel Help by tapping the F1 key and typing "overview of charting." Additional learning resources for creating charts with Excel are available at

To follow along with the text, enter into the worksheet the table data from cell A1 through 4E as shown in Table 2. Note that the table in Figure 1 includes two data types and two different units of measurement. The falls and pressure ulcers are the actual numbers per quarter, while the RN hours are in percentage per quarter (Table 2). Once you have entered the data, click anywhere in the table (you do not have to highlight the table data), then click on the Insert tab from the menu. Under the subcategory, Charts, click on the Column icon, and then 2-D Column > Clustered Column (first icon on the left in the first row). A Chart Object displaying the table data will appear on the spreadsheet.

Table 2
Table 2
Image Tools
Figure 1
Figure 1
Image Tools

To add the second y-axis, right-click on any one of the bars in the chart representing % RN hours. A pop-up menu will appear. Click on Format Data Series located at the bottom of the menu. The Format Data Series pop-up will appear with Series Options as the default. Under "Plot Series On" at the bottom of the menu, click the radio button by "Secondary Axis." Click the Close button to close the menu. A second y-axis scale should be visible for % of RN hours.

To change % of RN hours from a bar to a line, right-click on any of the bars for RN hours and select Change Series Chart Type from the pop-up menu. Select Line with Markers, the fourth chart under Line. Click the OK button to close the menu. The % RN hours should now be depicted with a line, instead of a bar. Note that the primary axis uses a zero-based scale; however, the secondary axis does not because the data type is different.

Effective chart design dictates that only the essential elements needed to communicate the data are included. Some ways to improve data communication include widening the plot area and moving the legend to the bottom of the graph. The legend describes the data categories used in the chart; in this graph, that is the color bars next to Falls etc. To move the legend, right-click on the legend area, click on Format Legend from the bottom of the pop-up menu. Click the radio button for Bottom legend position. The legend will now appear below the chart. Place the Legend Box as close as possible to the data while not obscuring it.

Back to Top | Article Outline


The final step is to communicate the purpose with chart and axes titles. The chart title offers a short explanation of what is in the chart. The axes titles describe how the categories are measured. To add a chart title, click anywhere on the chart. From the Chart Tools menu at the top of the screen, choose the Layout tab (Figure 2).

Figure 2
Figure 2
Image Tools

Next, click on the subsection "Chart Titles," in the Labels section (third from left). Select "Above Chart." The words "Chart Title" will appear on the chart. Without moving the insertion point, type "Number of Falls & Pressure Ulcers." Tap the Enter key. To enter the next line, right click on the title and select "Edit Text." Place your insertion point after the word "ulcers," tap enter and type "Compared with Percentage of RN Hours" on the second line. Tap the Enter key for a third line and enter "FY 2008," then tap the Enter key. If the title obscures the graph, you can lengthen the chart by placing the mouse pointer on the center of the bottom line where the four dots are until it is a double-edged arrow. Then drag the bottom line to the size you want the graph section to be.

To give a title to the axes, click in the chart, and again open the Layout tab from the Chart Tools (Figure 2). Select Axes Titles > Primary Vertical Axis Title > Rotated Title. Type "Number of Fall/Pressure Ulcers" and tap Enter. The title will appear on the left side of the graph. To name the other axis, again select "Axis Titles" in the Layout tab menu, but this time select "Secondary Vertical Axis Title" and "Rotated Title." Type "Percent RN Hours" for the secondary axis title and tap Enter. The chart should now look like Figure 1.

Back to Top | Article Outline


The purpose for charts is to display the relationships of data. A chart with a double axis and two chart types allows for comparison of two different data types. Using Excel spreadsheet software, a combination of a clustered bar and a line chart was used to depict a comparison of number of patient falls and pressure ulcers with the percentage of RN hours. Although the percent of RN hours varied by only 6%, the chart showed that the small variation appeared to make difference in patient outcomes for the year 2008. Application of effective information design techniques provides a rich medium to communicate aggregated data for informed decision making in nursing.

Back to Top | Article Outline


Ms Johns thanks her informatics professor, Ms Sewell, for her dedication to informatics and education.

Back to Top | Article Outline


1. Koomey J. Turning Numbers Into Knowledge: Mastering the Art of Problem Solving. 2nd ed. Oakland, CA: Analytics Press; 2008.

2. Few S. Show Me the Numbers: Designing Tables and Graphs to Enlighten. 1st ed. Oakland, CA: Analytics Press; 2004.

3. Wallenbach J. Introducing Excel chart. Accessed April 18, 2010.

4. Microsoft. Overview of charting. Accessed April 18, 2010.

© 2011 Lippincott Williams & Wilkins, Inc.



Article Tools



Article Level Metrics