Maximizing Connectivity & Productivity

Archive for the Excel Category

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

Excel – Increase number of rows

Spreadsheets in versions of Excel prior to Excel 2010 were limited to 65536 rows and 256 columns.  To increase these, simply open the workbook in Excel 2010, and save.

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