Maximizing Connectivity & Productivity

Archive for the MICROSOFT OFFICE Category

Don’t use Yes/No fields to store preferences

This is an article by Allen Browne, about the use of creating a list of choices to select from, rather than creating seperate fields that are boolean (Yes/No) fields. Using choice fields, preferably choices laid out in a seperate table, rather than a list of values in […]

Read more

Excel – Change the default number format

The default number format in Excel is General. To  set the default format, you have two choices.  One is to preformat the range of cells, or select and preformat the entire worksheet.  And the second, more preferable method,  is to change the Normal style, as the default […]

Read more

Excel – Changing the link source

If your source workbook changes, you may need to change your external references.  For example if your original source file was Annual Sales, but the final file is then called Annual Sales 2012. To change the link source, use the Edit Links dialog box.  File-> Info -> […]

Read more

Excel – Summarizing data in a table

Convert a range of data into a table by choosing Insert ->Tables ->Table. The table is named Table1 by default. In the bottom row of the table, you now have a summary formula for a table column..

Read more

Excel – Copying a formula to another cell

Ever try copying a formula to another cell and then found that all the references change? To copy a formula so that it does not change when moved to another cell: Simply put an apostrophe before the formula that you want to copy, copy and paste into […]

Read more

Excel – Creating and Testing new custom number formats

Creating new custom number formats is a create, test – rework, retest cycle.  It is best to check how the number appears for positive, negative, zero and text values.  However, each time the format is tested, the custom string will appear in the list to be selected. […]

Read more

Excel – Draw circles around invalid entries

Once you have set your validation rules, use the circle feature in Excel to circle the invalid entries.  This is an oft forgotten useful tool that is simple to use and apply. To use, highlight the column containing the validation rule(s).  Goto Data-> Data Tools-> Data Validation […]

Read more

Excel – Add a macro to the Quick Access toolbar

To add a macro to the Quick Access toolbar: Create the macro Right-click the Quick Access toolbar Click Customize Quick Access toolbar Select Macros from the drop-down list on the top left Select the macro Click the Add button To change the icon, click the Modify button

Read more

Ways to add commands to the Quick Access Toolbar

There are different ways to add commands to the Quick Access Toolbar.  Here are a few. Right-click the Quick Access Toolbar and select ‘Customize Quick Access Toolbar’ Dropdown the little arrow to the right of the Quick Access Toolbar.  Any of the commands listed there can be […]

Read more

Excel – Using SUMIF() to add only certain figures using criteria

So you don’t want all the numbers in a column added, using the SUM() function. Use the SUMIF() function to specify which figures are to be added, by specifying the criteria that Excel should use to identify these figures. ex. if you want to add only the […]

Read more
Page 1 of 512345
Mississauga, ON, Canada 905.607.3500