Excel
- Overview
- Language Translators
- 10 Educational Resources
- 10 Important Skills Students need for the Future
- 10 Self Help Sites for Technology
- 10 Sites for Differentiated Instruction
- 10 Tips for writing a better Resume Than Yahoo’s CEO
- 100 iOS Apps
- 15 Sites for Creating Flashcards
- 15 Sites for Finding Images and Clip Art for Education
- 15 Technologies That Were To Change Education for Ever
- 20 Free iPad Apps Educators Can’t Live Without!
- 25 Free Resources from Discovery Education
- 3D Modelmaking
- 4 Web Based Tools to Record Your Voice
- 45 Web Tools for Education
- 5 Things students want teachers to know about online learning
- 50 Sites and Apps for Digital Storytelling
- 6 Web Tools to Edit Pictures without Installing any Software
- 8 Apps to Prepare You for Your Next Presentation or Webmeeting
- A to Z Web Tools
- Answers
- Anti-Bulling Awareness Apps
- Apps for Story Telling
- Bucket List of On Line Ed Resources
- Checkbook Videos
- China Apple Factory
- Circus Posters
- Classroom Schedule
- Creating Charts and graphs
- CTE Frameworks on state standards
- Cyberbullying
- Demand for Computer Science Classes Grows, Along With Digital Divide
- DYMO/MIMIO Content Packs
- Evernote
- Excel
- Favorite resources for students and educators
- Fonts
- Free Alternatives to Fee Based Software
- Free Coding Instruction Codecademy
- Free Site to Learn Coding
- GEOMETRY
- Google Advance Search
- Google Maps
- Great Sites for Classroom Use
- Historic Items
- Homework: Jazz it up with technology
- How To Do Your Banking
- How to grow a textbook
- Interesting Web Sites
- Internet Safety – World’s Simplest Online Policy
- ipad
- K12 HOTSPOTS on the Web
- Keyboarding sites
- Language Translator
- Learning Page
- Lesson Plans
- Letter from Santa to Teachers
- Library Resources
- Math Resources on the Web
- Math Site
- More Engineering Resources for K12 Educators and Students
- Music Notation
- NFPA Fluid Power Challenge
- Online PD Destinations
- Photo Editing
- Search Engines
- September 11 Resources
- Software
- Special Sites to Tour
- Surfing Techniques with Google
- Teach a lot of content using free resources while teaching kids to make real money!
- Ten Apps to Die For
- The Basics of the Binary Code
- The obituary of the student desk: 1887-2013
- The power of Industry
- Tinker Cad
- Tiny House Movement
- Tools for Tracking Energy Usage
- Top 10 Apps for Digital Story Telling
- Top 10 Sites for Educational Apps
- Top 10 sites for note taking.
- Top 10 Sites for Video Editing
- Top 101 Sites and Apps
- Top 100 Sites and Apps for 2013
- Top 111 Sites for 2011
- Top 50 Sites and Apps of 2015
- Top sites
- Top Ten Ipad Tips
- Top-notch Educational Videos – Elementary
- Translate and speak
- Valentines Day Activities
- Video Page
- Video Resources
- Voki – Voice Over
- Warehouse Summer Art Project 2012
- Websites Educational
- Websites Interests and Hobbies
- Websites Teacher Development
- Wen 2.0 Sites for Education
- Widespread 3D Printing in Classrooms Still a Decade Out
- You Tube Help
- Stop Video
- Stop Motion Video
- Fractions Calculator / Decimal to Fraction Calculator
- Green Screen by DoInk
- Calculators
- 20 Sites for Creating Quizzes
- Free Online Learning Resources For Schools Affected by Coronavirus/COVID-19
- Graduation 2020
- STEM EdTech Ideas
- Accounting Instructional Videos
- Careers
- Minnesota Manufactures
- WEB sites for class activities
- Careers Class Instruction
- Keyboarding 2022
- E-Learning Careers Class
-
Use sparklines to show data trends
New in Microsoft Excel 2010, a sparkline is a tiny chart in a worksheet cell that provides a visual representation of data. Use sparklines to show trends in a series of values, such as seasonal increases or decreases, economic cycles, or to highlight maximum and minimum values. Position a sparkline near its data for greatest impact.
“Sparklines enable us to create visual representations of data with one click. It makes it easier to grasp data, and it’s helping our CFO analyze trends on the spot.”
Matt Stuckey, IT Director of Levick Strategic CommunicationsIn this article
What are sparklines?
Unlike charts on an Excel worksheet, sparklines are not objects — a sparkline is actually a tiny chart in the background of a cell. The following picture shows a column sparkline in cell F2 and a line sparkline in F3. Both of these sparklines get their data from cells A2 through E2 and display a chart inside a cell that shows the performance of a stock. The charts show the values by quarter, highlight the high value (3/31/08) and the low value (12/31/08), show all the data points, and show the downward trend for the year.
A sparkline in cell F6 shows the 5-year performance for the same stock, but displays a Win/Loss bar chart that shows only whether the year had a gain (as in the years 2004 through 2007) or a loss (2008). This sparkline uses values from cells A6 through E6.
Because a sparkline is a tiny chart embedded in a cell, you can enter text in a cell and use a sparkline as its background, as shown in the following picture.
Fig. 1 In this sparkline, the high value marker is green, and the low value marker is orange. All other markers are shown in black.You can apply a color scheme to your sparklines by choosing a built-in format from the Style gallery (Design tab, which becomes available when you select a cell that contains a sparkline). You can use the Sparkline Color or Marker Color commands to choose a color for the high, low, first, and last values (such as green for high, and orange for low).
Why use sparklines?
Data presented in a row or column is useful, but patterns can be hard to spot at a glance. The context for these numbers can be provided by inserting sparklines next to the data. Taking up a small amount of room, a sparkline can display a trend based on adjacent data in a clear and compact graphical representation. Although it’s not mandatory for a sparkline cell to be directly next to its underlying data, it is a good practice.
You can quickly see the relationship between a sparkline and its underlying data, and when your data changes you can see the change in the sparkline immediately. In addition to creating a single sparkline for a row or column of data, you can create several sparklines at the same time by selecting multiple cells that correspond to underlying data, as shown in the following picture.
You can also create sparklines for rows of data that you add later by using the fill handle on an adjacent cell that contains a sparkline.
The range of data used by a group of sparklines
A group of sparklines
One advantage of using sparklines is that, unlike charts, sparklines are printed when you print a worksheet that contains them.
Create a sparkline
- Select an empty cell or group of empty cells in which you want to insert one or more sparklines.
- On the Insert tab, in the Sparklines group, click the type of sparkline that you want to create: Line, Column, or Win/Loss.
- In the Data box, type the range of the cells that contain the data on which you want to base the sparklines.
Note You can click
to temporarily collapse the dialog box, select the range of cells that you want on the worksheet, and then click
to restore the dialog box to its normal size.
When one or more sparklines are selected, the Sparkline Tools appear, displaying the Design tab. On the Design tab, you can choose one or more of several commands from among the following groups: Sparkline, Type, Show/Hide, Style, and Group. Use these commands to create a new sparkline, change its type, format it, show or hide data points on a line sparkline, or format the vertical axis in a sparkline group. These options are described in detail in the following section.
Customize sparklines
After you create sparklines, you can control which value points are shown (such as the high, low, first, last, or any negative values), change the type of the sparkline (Line, Column, or Win/Loss), apply styles from a gallery or set individual formatting options, set options on the vertical axis, and control how empty or zero values are shown in the sparkline.
Control which value points are shown
You can highlight individual data markers (values) in a line sparkline by making some or all of the markers visible.
In this sparkline, the high value marker is green, and the low value marker is orange. All other markers are shown in black.
- To show all values, select the Markers check box.
- To show negative values, select the Negative Points check box.
- To show the highest or the lowest values, select the High Point or Low Point check boxes.
- To show the first or the last values, select the First Point or Last Point check boxes.
Change the style of or format sparklines
Use the Style gallery on Design tab, which becomes available when you select a cell that contains a sparkline.
- Select a single sparkline or a sparkline group.
- To apply a predefined style, on the Design tab, in the Style group, click a style or click the arrow at the lower right corner of the box to see additional styles.
- To apply specific formatting to a sparkline, use the Sparkline Color or the Marker Color commands.
Show or hide data markers
On a sparkline that has the Line style, you can show data markers so that you can highlight individual values.
- Select a sparkline.
- In the Show/Hide group, on the Design tab, select any of the check boxes to show individual markers (such as high, low, negative, first, or last), or select the Markers check box to show all markers.
Clearing a check box hides the specified marker or markers.
Show and customize axis settings
You can select Date Axis Type (in the Group group, click Axis) to format the shape of the chart in a sparkline to reflect any irregular time periods in the underlying data.
In a line sparkline, applying the Date Axis type can change the slope of a plotted line and the position of its data points in relation to each other.
In a column sparkline, applying the Data Axis type can change the width of and increase or decrease the distance between the columns, as shown in the following image.
In the example shown here, there are two column sparklines that use data from the same range. The sparkline with the “Trend” label uses the General Axis type, and the sparkline with the “Trend (Data Axis Type)” label uses the Date Axis type. In each sparkline, the first two data points are separated by two months, and the second and third are separated by seven months. By applying the Date Axis type, the space between the three columns changes proportionally to reflect the irregular time periods.
You can also use these Axis options to set minimum and maximum values for the vertical axis of a sparkline or sparkline group. Setting these values explicitly helps you control the scale so that the relationship between values is shown in a more meaningful way.
- With the sparkline or sparkline group selected, in the Group group, click Axis.
- Under Vertical Axis Minimum Value Options or Vertical Axis Maximum Value Options, click Custom Value.
- Set minimum or maximum values that you feel will best emphasize the values in the sparklines.
You can increase the height of the row that contains the sparkline to more dramatically emphasize the difference in data values if some is very small and some is very large.
You can also use the Plot Data Right-to-Left option to change the direction in which data is plotted in a sparkline or sparkline group.
If there are negative values in your data, you can emphasize this by showing a horizontal axis in your sparkline.
- With the sparkline or sparkline group selected, in the Group group, click Axis.
- Under Horizontal Axis Options, click Show Axis.
Any sparklines that contain negative data will display a horizontal axis at 0.
Handle empty cells or zero values
You can control how a sparkline handles empty cells in a range (and thus how the sparkline is displayed) by using the Hidden and Empty Cell Settings dialog box.
2. Pivot Charts Pivot Charts allow you to provide a picture for the data in your Pivot Table. Use Pivot Charts to help better explain data to your intended audience. Learn how to add a Pivot Chart – See more at:http://www.techlearning.com/pd-tips/0051/3-things-you-didnt-know-microsoft-excel-could-do-/54085#sthash.zKaoKpc3.dpuf3) Conditional Formatting By using conditional formatting, you are able to apply cell formatting based on specific conditions or criteria. Use conditional formatting to help make your data clear and easy to read, helping your team make more informed decisions. Learn how to utilize conditional formatting – See more at:http://www.techlearning.com/pd-tips/0051/3-things-you-didnt-know-microsoft-excel-could-do-/54085#sthash.Izjk51jy.dpuf