Recent News with K2E Canada Inc. and Technology

K2E Canada Blog

Quickly Entering Dates and Times

Excel provides two excellent shortcuts if you need to enter today’s date and time.

Read more

---

Creating Sheets for Months

When you are creating new workbooks, you will often have a worksheet for each month of the year.  This is a very tedious process so here is a macro that will eliminate a lot of work.

Read more

---

Finding Cells That Use Conditional Formatting

There are times when you want to find cells that have conditional formatting applied to them. This is easy to do using the Go To feature of Excel. Here’s the steps.

Read more

---

Deleting All Tab Stops

Working with tabs is a challenge for most users. Here’s a tip that will enable you to easily delete all the tab stops in a document.

Read more

---

Understanding the PDF/A Format

Everybody is familiar with the standard PDF file format. Very few are familiar with the PDF-a format (sometimes written as PDF-A or, as PDF/A). It is a version of a PDF file that is optimized for long-term storage of the documents. This format has everything embedded in it, rather than allowing internal links to external information.

Read more

---

Rounding by Powers of 10

When you need to round a number to the nearest 10, 100, or 1000, you can utilize the ROUND function with negative values as the second parameter.

Read more

---

Spell-checking Uppercase Words

Excel has a powerful spell-checker that does an excellent job. However, if your worksheets contain lots of acronyms, you'll find that Excel flags a lot of them as misspelled words.

Read more

---

Understanding Outlining

Excel has a feature that allows you to outline your data. When you use this tool Excel analyzes your data and assigns different rows to different "levels". These levels can then be hidden or displayed, according to your requirements. An outline allows you to get a quick understanding of large amounts of data.

Read more

---

How to Align Positive and Negative Whole Numbers in a Column

You have a document where you have a series of whole numbers in a table column. The negative numbers have parentheses around them, such as (2,345). You are looking for a way to align the positive and negative whole numbers in a manner that allows for an "implied" right parenthesis to the right of positive numbers.

Read more

---

Duplicate Workbooks Opening

Sometimes when you open an Excel workbook, you may find that Excel "replicates" the workbook. When it opens, two workbooks are displaying in the taskbar. Both contain the same workbook name, but one is followed by a 1 and the other followed by a 2.

Read more

---

Shortcut Key for Format Painter

For those of you who love to use the Format Painter but are a fan of keyboard shortcuts, here are the shortcut keys to use. Excel doesn't provide a single shortcut key you can use for the Format Painter. You can, however, use the these steps.

Read more

---

Quickly Entering Dates and Times

Here is a quick way to enter the current date or time into a cell.

Read more

---

How to Print an Outline

If you are using the Outline view, Word will print only the heading levels you have chosen to display. This means you can print an outline for your document easily and quickly. To print a single copy of your outline, follow these steps.

Read more

---

Counting with Formulas

Let’s assume you are working with a data table that has a limited number of categories by which you want a count; you can use the COUNTIF worksheet function to solve this problem.

Read more

---

Shading Based on Odds and Evens

Let’s assume you have values in a range of cells, and you want to use a different format to distinguish the odd numbers from the even numbers. You can use the Conditional Formatting feature in Excel to accomplish this. Follow these steps.

Read more

---

Using Strikethrough Formatting

Strikethrough is one of the character formats you can use within Excel. Strikethrough places a horizontal line through the middle of the character (or characters) to which the attribute is applied.

Read more

---

Finding Cells That Use Conditional Formatting

When you inherit a worksheet from someone, you may want to discover which cells have conditional formatting applied to them. This is rather easy to do using the Go To feature of Excel.

Read more

---

Moving a Chart's Legend

Excel provides a wide range of charts you can use to represent the data in your worksheet graphically. The provided charts are just a start, however, as you can modify the base charts quite a bit. For instance, you can add a legend to your chart and position it where you wish on your chart.

Read more

---

Displaying the Ruler

The ruler appears at the top of every Word document window. The ruler is used to adjust formatting and align elements of your page. It is beneficial if you are using Word with a mouse. Word allows you to control whether the ruler is displayed or not.

Read more

---

Ranges on Multiple Worksheets

Use these tips to refer to a range of cells in Excel.

Read more

---

Selecting Paragraphs

Word provides several ways to select an entire paragraph. Learn more with this Quick Tip.

Read more

---

Turning Off Insert Options

When you insert a row or column, Excel displays a small, floating "button" near the inserted row, column, or cell. This button, called "Insert Options," has a small paintbrush on it, similar to the Format Painter tool. When you click on this button, Excel displays options about how the inserted column or row should be formatted. 

Turn off the Insert Options button if you find it distracting or if you never use it with these steps.

Read more

---

Print Area Setting

Excel enables you to define the area of your worksheet you want to print. Setting the print area using these steps.

Read more

---

How to Filter a Date Range in the Past

I have a data table with over 75 columns and 20,000 rows. Column Ans contain a record date, and these cover the last five years of data. I need to filter the data to only those records with dates between 180 and 210 days ago. A solution to this problem is to add a new column B to your data set that shows if the date in column occurs the desired timeframe.

Read more

---

Double-Clicking to Widen Columns Won't Work

Normally, Excel allows you to double-click on the divider bar between column heads in order to widen a column. For instance, if you need to widen column C, all you need to do is move the mouse so it points to the divider between column headers for column C and column D; the mouse pointer changes to a double-headed arrow. When you double-click, column C is widened to accommodate the widest information in its cells.

Read more

---

Sorting Text

Word provides quite a few different tools on its various ribbons. If you do some exploring, you’ll notice the Sort tool, in the Paragraph group on the Home tab of the ribbon. Using this tool, you can easily sort any information you desire.

Read more

---

Sorting Data on Protected Worksheets

When you protect a worksheet, Excel stops users from performing a wide variety of tasks on the data in the worksheet. One of the things that the user can no longer do is to sort data. What if you want the user to be able to sort data, but still have the sheet protected?

The answer is quite easy: Excel allows you to specify what users can and cannot do with a protected worksheet.

Read more

---

Turning Headers On and Off

You already know that the headers in a worksheet come in handy. This is the gray area, at the left and top of a worksheet, which indicates the row and column label used by Excel. You click in the header area if you want to select either a row or header. You also know that you can adjust the height or width or rows or columns by using the row and column header area.

Even though this area is very useful, there may be times when you do not want it displayed. For instance, if you are using Excel to create an on-screen form, then the header areas may be distracting to the intended users of the form.

Read more

---

Too Many Formats When Sorting

Once in a while, I have run into a problem when I try to sort a worksheet. Instead of doing the Sort, I get a message that says, “Too Many Different Cell Formats.”

According to Microsoft, this error occurs when your workbook gets close to exceeding one of the built-in limitations of Excel. A workbook can contain only about 64,000 “custom formats.”

Custom formats are unique combinations of formatting attributes, applied to cells.

Read more

---

Opening Personal.xlsb

Every time you start Excel, it is supposed to open a blank worksheet. However, for some Excel users Excel always opens Personal.xlsb instead of a fresh worksheet.

The Personal.xlsb worksheet is used, most often, to contain macros that you want available whenever you are using Excel. Normally the worksheet is hidden, unless it has been specifically unhidden and then saved.

Read more

---

Setting Vertical Alignment

Excel provides several different ways you can align information from top to bottom (vertically) within a cell. You set the alignment by first selecting the cells you want to format and then displaying the Alignment tab of the Format Cells dialog box. (To display the dialog box, display the Home tab of the ribbon and click the small icon at the bottom-right of the Alignment group.)

Read more

---

Using the Selection and Visibility Pane

Word includes a handy management tool called the Selection and Visibility pane (in Word 2010) or the Selection pane (in Word 2013 and beyond). This tool is meant for working with objects in your document, particularly shapes and images. To use the tool, display the Page Layout tab of the ribbon and click the Selection Pane tool in the Arrange group. The pane appears at the right side of the document. (The Selection Pane tool is a toggle; clicking it a second time hides the pane.)

Read more

---

Combining Word Documents

Word provides a very easy way to combine documents, without the typical cut-and-paste routine. This is great for boilerplate text in your document. 

Read more

---

Number Formatting Shortcuts

The typical way to apply formatting to cells is to use the tools on the ribbon or display the Format Cells dialog box and make formatting choices. There are several handy shortcuts you can use to format cells.

Read more

---

Pasting a Hyperlink

A common editing task is copying information from one source and pasting it in your workbook. By using a minor variation on pasting, you can actually cause your pasted information to appear as a hyperlink in an Excel worksheet.

Read more

---

Using List Box Controls

Excel allows you to define graphical controls within your worksheet. One of the form controls you can place on your worksheet is called a list box, a scrollable list of options from which the user can choose.

Read more

---