Sign up for a free trial of our self-paced courses.

Advanced Microsoft Excel 2013 Tutorial

Random Useful Items

In this lesson, you will learn about some useful tricks and tools in Excel.

Lesson Goals

  • Insert sparklines into worksheets.
  • Customize sparklines by:
    1. Changing the type of sparkline.
    2. Highlighting key points.
    3. Changing colors.
    4. Changing the axis.
  • Import text files into Microsoft Excel.
  • Copy cells from a worksheet into Microsoft Word.
  • Copy and paste a chart into Microsoft Word.

Sparklines

Sparklines make it easy to visualize data in a worksheet. They are essentially miniature charts that appear in a cell representing data in the worksheet. (Sparklines are not available in Excel 2007.) Here are some examples:

  1. Revenue from the local pizza joint:
  2. Daily stock prices:
  3. The win-loss records for the last 10 games for four baseball teams:

Here are some things to know about sparklines:

  1. Unlike charts, which float above the grids in worksheets, sparklines live in a cell. To make a sparkline bigger or smaller, just make the cell bigger or smaller.
  2. Sparklines are great for spotting trends.
  3. Hidden cells don't show up on sparklines.
  4. You can autofill sparklines.
  5. Sparklines are just plain cool.

Inserting Sparklines

To insert a sparkline in a cell:

  1. Select the cell you want to insert a sparkline in.
  2. On the INSERT tab, in the Sparklines group, depending on the type of sparkline you want to create, click either the Line, Column, or Win/Loss command. You can always change the type of sparkline later:
  3. In the Create Sparklines dialog box, enter the Data Range (the range of cells the sparkline will chart), the Location Range (if necessary), and click OK:

Customizing Sparklines

When you insert sparklines into a cell or select a cell that already has sparklines, the SPARKLINE TOOLS DESIGN tab appears:

There are many ways to customize sparklines, including:

  1. Change the type. You can always change the type of the sparkline by selecting a different type in the Type group:
  2. Highlight key points. You can draw attention to key (high, low, first, last, negative) points by selecting them in the Show group:
  3. Colors. You can change the color of the sparkline or of any of the key points using the Sparkline Color and Marker Color drop-down menus:
  4. Change the axis. You can change the axis using the Axis drop-down menu:

Importing and Exporting Files

Importing Text Files

It is often necessary to import data from other applications. Data can be imported from other spreadsheet applications and from database applications, but most of the time when you need to import data into Excel, it will be from a text file. When you do need to import data from another application, it is usually easiest to first convert it from the other application to a text file and then import it into Excel as a text file.

The process of importing text files is very similar to the process of converting text to columns. To import a text file into Microsoft Excel:

  1. From the FILE menu, select Open:
  2. Navigate to the location of the file and in the Open dialog box, in the file type drop-down box, select Text Files:
  3. Select the text file you want to import and click Open:
  4. In Step 1 of the Text Import Wizard, select either Delimited (if your data is separated by commas, tabs, or spaces) or Fixed Width (if your data contains a certain number of characters in each field). You will usually select Delimited in this step. Click Next:
  5. In Step 2 of the Text Import Wizard, select the Delimiters (assuming you selected Delimited in the prior step). If you aren't sure what to select, you can select and deselect the options and see the results in the Data preview. After making your selection(s), click Next.
  6. In Step 3 of the Text Import Wizard, select the data format for each column or you can elect not to import a column. Simply select the column under Data preview and then select the Column data format above. When you are done, click Finish:

Exporting Worksheet Data to Microsoft Word

It is easy to copy and paste data from Microsoft Excel into Microsoft Word. When you do so, you can choose from a number of Paste options, including (these options vary slightly in Excel 2007 and 2010):

  1. Keep Source Formatting. The data will be pasted as a Word table using the formatting applied in Microsoft Excel.
  2. Use Destination Styles. The data will be pasted as a Word table using the formatting of the destination in which it is pasted.
  3. Link & Keep Source Formatting. The data will be pasted as a Word table using the formatting applied in Microsoft Excel and the data will be linked to the Excel worksheet from which it was copied, meaning that updating the Excel worksheet will automatically update the Word table.
  4. Link & Use Destination Styles. The data will be pasted as a Word table using the formatting of the destination in which it is pasted and the data will be linked to the Excel worksheet from which it was copied, meaning that updating the Excel worksheet will automatically update the Word table.
  5. Picture. The data will be pasted as a picture.
  6. Keep Text Only. The data will be pasted as text.

To copy and paste data from Microsoft Excel into Microsoft Word:

  1. Select the data in Microsoft Excel.
  2. On the HOME tab, in the Clipboard group, click the Copy command:
  3. In Microsoft Word, place your cursor where you want to paste the data.
  4. On the Home tab, in the Clipboard group, click the drop-down arrow below the Paste command:
  5. Select one of the Paste Options by clicking it:

Exporting Excel Charts to Microsoft Word

When you copy and paste charts from Microsoft Excel to Microsoft Word, you can choose from a number of Paste options:

  1. Use Destination Theme & Embed Workbook. The chart will be pasted using the formatting of the destination in which it is pasted and the Excel workbook from which the chart was created will be embedded into Word so the chart can easily be updated later.
  2. Keep Source Formatting & Embed Workbook. The chart will be pasted with the same formatting applied in Microsoft Excel and the Excel workbook from which the chart was created will be embedded into Word so the chart can easily be updated later.
  3. Use Destination Theme & Link Data. The chart will be pasted using the formatting of the destination in which it is pasted and the chart will be linked to the Excel worksheet from which it was copied, meaning that updating the Excel worksheet will automatically update the chart in Word.
  4. Keep Source Formatting & Link Data. The chart will be pasted with the same formatting applied in Microsoft Excel and the chart will be linked to the Excel worksheet from which it was copied, meaning that updating the Excel worksheet will automatically update the chart in Word.
  5. Picture. The chart will be pasted as a picture.

To copy and paste charts from Microsoft Excel into Microsoft Word:

  1. Select the chart in Microsoft Excel.
  2. On the HOME tab, in the Clipboard group, click the Copy command:
  3. In Microsoft Word, place your cursor where you want to paste the chart.
  4. On the HOME tab, in the Clipboard group, click the drop-down arrow below the Paste command:
  5. Select one of the Paste Options by clicking it: