How to Zoom in on an Excel Graph
How to Add Zoom & Scroll to an Excel Chart
- Small Business |
- Accounting & Bookkeeping |
- Excel
Excel charts containing large amounts of data prevent readers for easily reading small segments. For example, if a chart tracks your company's daily sales over the course of several years, you cannot read any single day's sales from the chart. In fact, the chart may not even show a trend. It may look like a compressed, blurry mess. Zoom and scroll bars solve this problem. The zoom bar focuses on small segments of the chart. The scroll bar changes the segment that the chart displays.
Create a Variable Data Range
Identify the range that the chart tracks. For example, suppose the chart tracks data in cells B2 to B802.
Choose an empty range of cells the same size as the range you identified. For example, the range containing cells C2 to C802 is the same size as the previous one.
Type the following formula into the first cell in the range from the previous step:
=OFFSET(A1, $D$1, 0))
Replace "A1" with the first cell from the range in Step 1. With this example, change the formula to:
=OFFSET(B2, $D$1, 0))
Replace "$D$1" with a blank cell outside both ranges. Later, the scroll bar will change this cell's value . For example, to designate cell E10 for this purpose, change the formula to:
=OFFSET(B2, $E$10, 0))
Extend the formula to every cell in the range from Step 2.
Type "0" into the cell from Step 5. The two ranges will now contain the same values.
Right-click the chart's data series. Click "Select Data" to open the Select Data Source dialog box.
Click the button in the text box labeled "Chart data range." Select the range from Step 2.
Click "OK" to close the dialog box. The chart does not change in appearance.
Add Scrolling
Click "Developer" from Excel's ribbon. Click "Insert" from the Controls group, and click the scroll bar icon.
Click and drag over the chart to add a scroll bar.
Right-click the scroll bar. Click "Format Control" to open the Format Control dialog box.
Type the cell you selected in the last section's Step 5 into the text box labeled "Cell link."
Type the cell range's number of rows into the text box labeled "Maximum value." With this example, type "801."
Type "0" into the text box labeled "Minimum value."
Click "OK." The scroll bar will now scroll your data series along the chart.
Create a Dynamic Named Range
Repeat Step 2 from "Create a Variable Data Range" to find a new range the same size as the two previous ones. A range containing cells D2 to D802 is the same size as the example's previous two ranges.
Select all the cells in this new range.
Click "Formulas" in the program's ribbon. Click "Define Name" in the Defined Names group to open the New Name dialog box.
Type the following formula into the text box labeled "Name":
=OFFSET(A1, 0, 0, $D$1, 0)
Replace "A1" in the formula with the first cell from the second range. With this example, change the formula to:
=OFFSET(C2, 0, 0, $D$1, 0)
Replace "$D$1" with the address of a new blank cell. Later, the zoom bar will change this cell's value. For example, to choose cell F10 for this purpose, change the formula to:
=OFFSET(C2, 0, 0, $F$10, 0)
Type "10" in the cell you selected in the previous step.
Right-click the chart's data series. Click "Select Data" to open the Select Data Source dialog box.
Click the button in the text box labeled "Chart data range." Select the new named range that you created.
Click "OK" to close the dialog box. The chart now tracks only 10 values.
Add Zooming
Click "Developer" from Excel's ribbon. Click "Insert" from the Controls group and click the scroll bar icon.
Click and drag over the chart to add a second scroll bar.
Right-click the scroll bar. Click "Format Control" to open the Format Control dialog box.
Type the cell you selected in the last section's Step 6 into the text box labeled "Cell link."
Type the cell range's number of rows into the text box labeled "Maximum value." With this example, type "801."
Type the minimum number of values that the chart must track in the text box labeled "Minimum value." For example, if the chart should show 10 days' sales when zoomed as far in as possible, type "10."
Click "OK." The second scroll bar will now zoom in and out of your chart's data.
References
Resources
Writer Bio
Ryan Menezes is a professional writer and blogger. He has a Bachelor of Science in journalism from Boston University and has written for the American Civil Liberties Union, the marketing firm InSegment and the project management service Assembla. He is also a member of Mensa and the American Parliamentary Debate Association.
How to Zoom in on an Excel Graph
Source: https://smallbusiness.chron.com/add-zoom-scroll-excel-chart-41823.html
0 Response to "How to Zoom in on an Excel Graph"
Post a Comment