Intermediate level information
Excel 2010 and 2007 for Windows have the option to import data from the web. Excel for Mac users don’t. An integral part of working with Excel is using keyboard shortcuts. They make your life so much easier (in the Windows versions at least, in the Mac version I think they tend to shorten your …
The INDEX and MATCH functions are used together to perform lookups. The INDEX functions grabs a cell from an array, and the MATCH function contributes the row and/or column position. INDEX and MATCH Example Familiarity with the VLOOKUP function can cause you to restrict INDEX and MATCH to less than they can be. The example …
The INDEX and MATCH functions working together are more flexible than using the VLOOKUP function. However, if you’ve never used INDEX and MATCH to perform a lookup it can get confusing. I’m going to cover the INDEX function here so you can learn it well and don’t have to think about it too much when …
The MATCH and INDEX functions working together are more flexible than using the VLOOKUP function. However, if you’ve never used MATCH and INDEX to perform a lookup it can be confusing at first. I’m going to cover the MATCH function (Excel) here so you can learn it well and don’t have to think about it …
Formulas and Functions give you answers based upon input. If you know what a formula’s answer should be, but can’t derive the input value, Goal Seek is the tool for you. I have a simple formula from my last post that takes one input, a Date in “m/d/yyyy” format, and returns the Day of the …
Given the Date in this spreadsheet, a simple formula using the DATE Function will return the Day of Year. Go ahead and type a Date value in cell A2 to see how the formula works, the spreadsheet is embedded from my SkyDrive. (Date format is US, “m/d/yyyy”) Click here if you can’t see the embedded …
The DATEDIF Function comes in handy when you want to know how many years, months, and days there are between two dates. And even though it may be slightly flawed, it still beats the alternatives. How Many Years, Months and Days Has it Been? The following spreadsheet works well for birthdays or anniversaries. The DATEDIF …
Adding the Repeat command icon to the Quick Access Toolbar in Excel for Windows is a simple process that I explained in a blog post last week. Excel for Mac — 2011 or 2008 — doesn’t have a Quick Access Toolbar, but you can customize the toolbars and menus. Add the Repeat Icon to the …
I always thought the F4 key was used to Repeat the last command, and the keyboard shortcut Ctrl+Y was used to activate the Redo command. I don’t use Redo very much, but the keyboard shortcut Ctrl+Z for the Undo command is one of my favorites. Toolbar icons for Undo and Redo command are common, yet …