facebook google plus twitter
Webucator's Free Advanced Microsoft Excel Tutorial

Lesson: Random Useful Items

Welcome to our free Advanced Microsoft Excel tutorial. This tutorial is based on Webucator's Advanced Microsoft Excel 2019 Training course.

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

Lesson Goals

  • Learn to insert sparklines into worksheets.
  • Learn to customize sparklines by:
    1. Changing the type of sparkline.
    2. Highlighting key points.
    3. Changing colors.
    4. Changing the axis.
    5. Preparing a workbook for internationalization, accessibility, and compatibility.
  • Learn to import text files into Microsoft Excel.
  • Learn to copy cells from a worksheet into Microsoft Word.
  • Learn to 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. Here are some examples:

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

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:Sparkline Type
  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:Create Sparklines Dialog Box

Customizing Sparklines

When you insert sparklines into a cell or select a cell that already has sparklines, the Sparkline Tools Design tab appears:Sparkline Tools Design

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: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: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:Marker Colors
  4. Change the axis. You can change the axis using the Axis drop-down menu:Change Axis

Inserting and Customizing Sparklines

Duration: 10 to 20 minutes.

In this exercise, you will practice using sparklines by creating sparklines similar to the examples above.

  1. Open Sparklines.xlsx from your Excel2019.3/Exercises folder.
  2. On the sheet named Revenue, insert sparklines that look like the following:Sparklines
    1. Note that:
      1. The Vertical Axis Minimum Value Option has a Custom Value of zero (0).
      2. The High Point is highlighted in a different color.
  3. On the sheet named "Stocks", insert sparklines that look like the following:Sparklines
  4. On the sheet named "WinLoss", insert sparklines that look like the following:Sparklines

Solution:

  1. To insert column sparklines on the sheet named Revenue:
    1. Select cell C3.
    2. On the Insert tab, in the Sparklines group, click the Column command:Column Command
    3. In the Create Sparklines dialog box, enter D3:O3 in the Data Range and then click OK:Data Range
    4. On the Sparkline Tools Design tab, in the Show group, click the High Point command:High Point Command
    5. On the Sparkline Tools Design tab, in the Group group, click the Axis command:Axis Command
    6. Under Vertical Axis Minimum Value Options, click Custom Value:Vertical Axis Minimum Value Options
    7. In the Sparkline Vertical Axis Setting dialog box, accept the default value of "0.0" and click OK:Values
    8. Copy cell C3 to cells C4:C6.
  2. To insert line sparklines on the sheet named Stocks:
    1. Select cell B2.
    2. On the Insert tab, in the Sparklines group, click the Line command:Line Command
    3. In the Create Sparklines dialog box, enter C2:G2 in the Data Range and then click OK:Data Range
    4. Copy cell B2 to cells B3:B6.
  3. To insert win/loss sparklines on the sheet named "WinLoss":
    1. Select cell D2.
    2. On the Insert tab, in the Sparklines group, click the Win/Loss command:Win/Loss Command
    3. In the Create Sparklines dialog box, enter E2:N2 in the Data Range and then click OK:Data Range
    4. Copy cell D2 to cells D3:D5.

Using Microsoft Translator

New to Excel 2019 on the Review is Microsoft Translator. You can use Translator to translate text into different languages.

To access Translator:

  1. From the Review tab, select Translate from the Language group. Translate Command
  2. If prompted, turn on Intelligent Services. Intelligent Services
  3. The Translator tab will launch on the right.Translator Tab

Preparing a Workbook for Internationalization and Accessibility

You may need your workbook to be able to be available to an international audience or an audience of disabled users, and if so you may need to make certain adaptations to it.

Display Data in Multiple International Formats

To display data in multiple international formats:

  1. From the Home tab, select Format in the Cells group, and then select Format Cells. Format Cells
  2. Select Date or Time from the Category list.
  3. From the Locale drop-down list, select a location and click OK. Locale List

Modify Worksheets for Use with Accessibility Tools

Excel 2019 has been updated to support international standards of accessibility. To check and modify worksheets for accessibility:

  1. Select the File menu tab, and from the Info section, select Check for Issues.
  2. From the drop-down list, select Check Compatibility. Check Compatibility
  3. The Accessibility Checker pane will launch, with any issues listed. Issues Listed
  4. To correct an issue, select it and view information about how to fix it. Fixing Information

Accessibility: Using Sounds

You can add sounds in Excel to provide audio cues. To do so, select File > Options.

Select Ease of Access on the left, and then use the Feedback options section to turn on sound effects.

Audio Cues

Inspect a Workbook for Compatibility Issues

You can check a workbook for compatibility with previous versions of Excel:

  1. Select the File menu tab, and from the Info section, select Check for Issues.
  2. From the drop-down list, select Check Compatibility. Check Compatibility
  3. The Compatibility Checker will launch if any compatibility issues exist. Any Issues

Use International Symbols

To use international symbols in a workbook:

  1. From the Home tab, select Format in the Cells group, and then select Format Cells. Format Cells
  2. On the Number tab, select Currency. Currency
  3. From the Symbol drop-down list, select a unit of currency and click OK.

Manage Multiple Options for +Body and +Heading Fonts

To manage multiple options for +Body and +Heading fonts:

  1. Select the Home tab of the Ribbon.
  2. In the Styles group, select Cell Styles. Cell Styles
  3. From the drop-down list, select a body or heading option. Select Option

Importing and Exporting Files

Importing Delimited 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 delimited 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: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:Text Files
  3. Select the text file you want to import and click Open:Select File
  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:Wizard Step 1
  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.Wizard Step 2
  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:Wizard Step 3

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 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: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:Paste Command
  5. Select one of the Paste Options by clicking it:Paste Options

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:Copy
  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:Paste
  5. Select one of the Paste Options by clicking it:Paste Options

Importing Text Files

Duration: 5 to 15 minutes.

In this exercise, you will practice importing text files into a Microsoft Excel workbook.

  1. Use Microsoft Excel to import Contact Information.txt from your Excel2019.3/Exercises folder.
  2. Use Microsoft Excel to import Capitals.txt from your Excel2019.3/Exercises folder.

Solution:

  1. To import Contact Information.txt from your Excel2019.3/Exercises folder:
    1. Open Microsoft Excel.
    2. From the File menu, select Open:Open File
    3. In the Open dialog box, navigate to your Excel2019.3/Exercises folder and in the file type drop-down box, select Text Files:Select Text Files
    4. Select Contact Information.txt and click Open:Click Open
    5. In Step 1 of the Text Import Wizard, select Delimited and click Next:Click Next
    6. In Step 2 of the Text Import Wizard, select Tab and click Next:Click Next
    7. In Step 3 of the Text Import Wizard, click Finish:Click Finish
  2. To import Capitals.txt from your Excel2019.3/Exercises folder:
    1. From the File menu, select Open:Open Option
    2. In the Open dialog box, navigate to your Excel2019.3/Exercises folder and in the file type drop-down box, select Text Files (if it isn't already selected):Text Files Command
    3. Select Capitals.txt and click Open:Click Open
    4. In Step 1 of the Text Import Wizard, select Delimited and click Next:Click Next
    5. In Step 2 of the Text Import Wizard, select Comma and click Next:Click Next
    6. In Step 3 of the Text Import Wizard, click Finish:Click Finish

Copying Data from Excel to Word

Duration: 5 to 10 minutes.

In this exercise, you will copy data from Microsoft Excel to Microsoft Word.

  1. Open Copy to Word.xlsx and Pizza and Wings.docx from your Excel2019.3/Exercises folder.
  2. In Copy to Word.xlsx, copy cells A1:M7 and paste them into Pizza and Wings.docx between the two paragraphs such that the formatting is the same as in Excel and the data is linked to the Excel worksheet.

Solution:

  1. Select cells A1:M7 in Copy to Word.xlsx.
  2. On the Home tab, in the Clipboard group, click the Copy command:Copy
  3. In Pizza and Wings.docx, place your cursor between the two paragraphs.
  4. On the Home tab, in the Clipboard group, click the drop-down arrow below the Paste command:Paste
  5. Click the Link & Keep Source Formatting icon:Paste Link
  6. Note that when you change the numbers in Copy to Word.xlsx, they change in Pizza and Wings.docx as well.

Copying Charts from Excel to Word

Duration: 5 to 10 minutes.

In this exercise, you will copy a chart from Microsoft Excel to Microsoft Word.

  1. If they aren't already open, open Copy to Word.xlsx and Pizza and Wings.docx from your Excel2019.3/Exercises folder.
  2. In Copy to Word.xlsx, copy the chart and paste it as a picture into Pizza and Wings.docx below the second paragraph.

Solution:

  1. Select the chart in Copy to Word.xlsx.
  2. On the Home tab, in the Clipboard group, click the Copy command:Copy Command
  3. In Pizza and Wings.docx, place your cursor below the second paragraph.
  4. On the Home tab, in the Clipboard group, click the drop-down arrow below the Paste command:Paste Command
  5. Click the Picture icon:Paste Options