K2E Canada Office Tips November 2019


Excel Tips

Understanding Regions

Summary:  Regions are important in Excel. This tip will help you understand how Excel uses regions.

Much of the work that you do in Excel is done using a specific area of the worksheet. These areas are called Regions. For example, when you sort data, Excel first selects a region that it will sort. A region is defined as the area surrounding an active cell that is bounded by blank columns and rows. Regions are often called a list or a table.
 
To see what Excel considers to be the region surrounding a cell, simply select the cell and press Shift+Ctrl+*  (that's the asterisk.) The region is then highlighted.

How to Display a Negative Percentage in Red

Summary: Excel allows you to easily adjust how you want to display information in a cell. It is not obvious how to display a negative percentage in Red. Here is how to do this:

Select the cell or cells that may contain negative percentages.
  1. Click on the Home tab. 
  2. Click on the small icon in the lower right-hand corner of the Number Group.
  3. Select Custom.

In the Type Box enter the following:
 
                _(* 0.00%_);[RED]_(* (0.00%);_(* 0.00%_) 
 
      5. Click OK.
 
The format you specified above will display positive percentages with two decimal places and will display negative percentages in red with brackets and two decimal places.

Word Tip

Adding a Break in Your Document

Summary: Word automatically adds page breaks. There may be times when you want to control a page break. Here is how to do one manually:

Word supports the following types of breaks:
  • Page breaks. Causes the following text to begin at the top of the next page. 
  • Column breaks. Causes the following text to begin at the top of the next column. (If there is only one column in your page layout, the column break functions the same as a page break.)
  • Section breaks. Causes the following text to begin a new section of your document. Where the actual break occurs depends on the type of section break you use. There are four different types of section breaks that can be inserted in your document. 
  • Text-wrapping breaks. These breaks are closely akin to line breaks.
  • (Shift+Enter). A text-wrapping break breaks a line of text and moves the text to the next line. This type of break is intended for use with text that wraps around graphics.

To insert a break in your document, follow these steps:
  • Position the insertion point at the position where you want to insert the break. 
  • Display the Page Layout tab of the ribbon. 
  • Click the Breaks tool, in the Page Setup group. Word displays a list of breaks you can insert.
  • Click the type of break you want to insert.
Vertical Divider
We hope that you are enjoying our monthly newsletter featuring tips that will help you to be more effective using Microsoft Office products.

More Ways to
Earn Verified CPD


Access the many courses from the leader in providing CPD to professionals throughout North America.
Catalogue of upcoming webinars

Video Tip

Relative vs Absolute Macros

Summary: Understanding the difference between a relative and an absolute macro when you are doing a recorded macro.

When you record a macro in Excel, it includes Absolute references to the cells by default.  This means that it records the specific cell addresses of the cells you are using as you record. For example, if your macro begins and you are on cell A1, then you record clicking on cell A10, when you reply the macro it will select cell A10.  The macro will go to cell A10 even if you start on cell C10. An Absolute macro is useful when you will be replaying the macro on different worksheets all with the same layout and format.

If you want your cell selections to be recorded relative to the cell you begin with, click the Use Relative References button before you start recording.   If you record the same macros as described above with Use Relative References applied, if when your macro begins you are on cell C1, then it will select cell C10.  A Relative macro is useful when you want to be able to apply it to varying groups of cells.

The video tip is from the webinar K2's Road to Excellence - Part 6 Excel Macros and Adagio

Click above to view the video tip