Array formulas in Excel workbooks - use COUNTIFS & SUMIFS instead

Often people have array formulas set up in their Excel workbooks but are not sure how they work. (you will usually know an array formula because of the { and } brackets at the ends. Often these array formulas are counting or summing some values based on multiple criteria (city 'ABC" and month "Jan" for instance (note the count array function will use SUM & IF)
How these array formulas work is too complex for this most BUT the good news is that COUNTIFS & SUMIFS in Excel 2007 or excel 2010  functions will usually replace them. Just work out what the criteria is and test

MS Excel 2010 formulas, COUNTIFS & SUMIFS Toronto Excel training

Bert Twomey
MS Office & Excel trainer
Mississauga, Toronto, Ontario
905-271-1119

MS Excel 2010 - Sort by Colour, Training
In Excel 2010 it is possible to sort by colour, this can be useful on it's own but also used with conditional formatting )where Excel applies the colours)

Excel 2010 training Toronto, Mississauga, Ontario

Bert Twomey
MS Office, Excel MOS Trainer

A good way to organize and protect Named Ranges in Excel is to place all of them on a separate  worksheet. This sheet can then be protected and also hidden from view (but the named ranges still work)
An added bonus is the ability to copy this sheet to a new workbook so all your named ranges are ready for use. Excel training Toronto, Ontario

Bert Twomey
Excel 2010/2007/2003 trainer
Mississauga, Toronto, Ontario

Pivot Tables in Excel 2007 & Excel 2010

The Pivot Tables in Excel 2007 & 2010 are easier to use that the older Excel 2003 versions. To sort values just right click on the relevant number and a context menu will show the "Sort" option.
That's all you have to do
Excel 2010 training Toronto, Mississauga, Ontario

Bert Twomey
Excel 2010 & 2007 trainer Toronto

MS Excel 2007 & 2010 shortcut keys Training Toronto

While it is great to have the Ribbon, some people do not like it. Instead you can use the shortcut keys such as Control & 1 keys to pop up the Format cell dialogue box. This Excel 2007 2010 training Toronto can help you

Bert Twomey
MS Office & Excel Trainer

MS Excel 2007, Excel 2010 Camera Icon to Display Data

There is camera feature in Excel 2007 & Excel 2010 BUT you have to find it. Use Customize Toolbar or Ribbon in Excel 2007/2010 - find the Camera icon (looks like a camera) and drag to toolbar or Quick Access Toolbar. This camera can then take a screen shot of data and can be placed elsewhere AND the data updates
MS Excel 2003/2007/2010 camera Toronto, Mississauga, Ontario Canada

Bert Twomey
MS Excel Training Toronto, Mississauga Ontario
ACTION eBIZ INC

MS Excel 2007/2010 Training Tables Toronto Mississaugaga

In MS Excel 2007/2010 using the Home Tab you can format lists as Tables. Just use the "Format as table" button. This table has filters applied and also expands as you add to it - so handy for expanding pivot tables etc. Colour bands can be applied also
Go to MS Excel 2007/2010 Training Tables Toronto Mississaugaga
for more info

Bert Twomey
MS Office & Excel trainer
Toronto, Mississauga, Ontario