Friday, February 11th, 2011

Excel 2007 & 2010 – Hidden add-ins

Excel 2007 2010 analysis toolpak lookup wizard solver add-in

Excel 2007 & 2010 come with a number of add-ins that you can get to by choosing the Options button (File in Excel 2010) and choose Excel Options (Options in Excel 2010) and click Add-ins. From the Manage dropdown list choose Excel Add-ins and click Go.

Image explaining how to install the Excel 2007 2010 analysis toolpak lookup wizard solver add-in

A list of available Add-ins appears in the list. Any that don’t have their checkboxes checked aren’t enabled right now.

One you might want to enable is the Analysis Toolpak – this gives you access to functions like RANDBETWEEN and NETWORKDAYS. There’s also possibly a Lookup Wizard (in Excel 2007 only – it was discontinued in Excel 2010) and a Solver add-in in the list.

Click on any of the Add-ins to add them to Excel. Once you do, they’ll be available every time you launch Excel.

RANDBETWEEN is a handy function for filling cells with a random value. It’s syntax is RANDBETWEEN(startvalue, endvalue) so to fill a range with values between 100 and 200 use =RANDBETWEEN(100,200) then copy it to the range to fill. You can read more about the function here: Random numbers in Excel.

NETWORKDAYS  will calculate the number of working days between two dates. You can read more about this function here: Excel – calculating workdays with Networkdays.

Helen Bradley

Monday, April 6th, 2009

Excel – calculating workdays with Networkdays


Excel has lots of very cool functions for doing all sorts of calculations. One of these is the NETWORKDAYS function.

You can use it to calculate the number of days between two dates taking into account holidays.

Start by placing the dates for the holidays in a range of cells across a row or down a column. Select this range and name it holidays using Insert > Name > Define.

The function calculates the number of workdays between two dates so place one, for now, in cell A1 and the other in A2. This function will calculate the days between the dates in cells A1 and A2 taking into account the holidays listed in the range called Holidays:

=NETWORKDAYS(A1,A2,Holidays)

If the NETWORKDAYS function returns an error make sure that you have the Analysis Toolpak installed as this function is stored in this toolpak. To install it in Excel 2003 choose Tools > Add-ins and enable its checkbox. In Excel 2007, click the Microsoft Office Button > Excel Options > Add-Ins and from the Manage list choose Excel Add-ins and click Go. In the Add-Ins Available list enable the Analysis ToolPak checkbox and click OK.

Helen Bradley

Tuesday, January 30th, 2007

Random numbers in Excel

When you’re testing a workbook it is handy to be able to fill a set of cells with a sample value. The Randbetween function included in the Analysis Tookpak does this for you.

To enable this, choose Tools, Add-Ins and enable the checkbox for the Analysis Toolpak. Now write the function, for example, this provides a random number between 1 and 100 (including both numbers):

=randbetween(1,100)

Copy it to all the cells to fill. To fix the values so they don’t change each time something in the worksheet changes, select the cells with the formula, choose Edit, Copy then Edit, Paste Special, Values.

Helen Bradley