If you find yourself opening the same workbooks each day when you start work, or when you do a particular job then you can save time by grouping these workbooks as a Workspace. It’s dead easy to do. How to Save a Workspace Open all the files you want saved in your Workspace. Note: [...]
June 2011
If you ever work with large tables of data and you want to insert a VLOOKUP formula that dynamically updates to the next column as you copy it across, then the VLOOKUP with the COLUMNS function is what you need. That is; the col_index_num part of the VLOOKUP function dynamically updates as you copy it [...]
Knowing Excel array formulas will catapult you to ‘Guru Status’ in your office and I’ll do my best to make this painless, but you might need to get a brainbooster (a fruit or veg snack given to the 5 year olds at my son’s school at 10am each morning when they’re starting to lose focus). [...]
Excel’s SUMPRODUCT function has some handy uses for Excel 2003 users who desperately want the SUMIFS, COUNTIFS or AVERAGEIFS functions (the *IFS series of functions). And if you’re an Excel 2007 or 2010 user keep reading because there’s a cool way to use SUMPRODUCT which gets around the limitations of Excel’s *IFS series of functions. [...]
Importing data to Excel from other programs and sources enables you to further analyse and generate reports or dashboards from what is often 1000′s of rows of data. On the Data tab of the ribbon in the section Get External Data there are four options: From Access – a Microsoft Access Database From Web – [...]
If you import data from another program it often comes in with additional spaces either at the beginning, end or sometimes between words. Thankfully the TRIM function will remove the excess spaces from text, except for single spaces between words. How to use the TRIM function It’s a simple function =TRIM(text) Where ‘text’ is the [...]
... I would highly recommend My Online Training Hub for all your Microsoft Office needs .... Geniuses
