Microsoft Office Tutorials and References
In Depth Information
Summary
Start this process by creating a dynamic range name. Here’s how:
1. Choose Formulas Deﬁ ned Names Deﬁ ne Name, specify Last7 as the Name,
and enter the following formula in the Refers To ﬁ eld:
=OFFSET(\$B\$2,COUNTA(\$B:\$B)-7-1,0,7,1)
This formula calculates a range by using the OFFSET function. The ﬁ rst argument
is the ﬁ rst cell in the range (B2). The second argument is the number of cells in
the column (minus the number to be returned and minus 1 to accommodate the
label in B1).
This name always refers to the last seven nonempty cells in column B. To display a
different number of data points, change both instances of 7 to a different value.
2. Chose Insert Sparklines Line.
3. In the Data Range ﬁ eld, type Last7 (the dynamic range name); specify cell E4
as the Location Range. The sparkline shows the data in range B11:B17.
4. Add new data to column B. The sparkline adjusts to display only the last seven
data points.
Summary
In this chapter, you learned about features you can use to organize and communicate the
meaning of data in a visual way. The chapter introduced tables, conditional formatting, and
sparklines in Excel. Your Excel skill set now includes the ability to:
Convert a range to a table and apply a table style.
Navigate in and edit a table.
Sort and ﬁ filter a table, and display and use a total row.
Convert a table back to a regular range of cells.
Apply conditional formatting using data bars, color scales, and icon sets.
Edit a conditional formatting rule or create your own rule.