Wednesday, June 16th, 2010
If you are having difficulty understanding how a formula is calculating in Excel – perhaps because it appears to give you the wrong results - you can step through it to see how it is working.
To do this, select the cell containing the formula and choose Tools > Formula Auditing > Evaluate Formula – in Excel 2007 find the Evaluate Formula option on the Formulas tab.
Click Evaluate and each time you do this, a portion of the formula will be evaluated and you can see it at work.
Use the Step In and Step Out options to see the actual values in place of any appropriate cell references.
This step by step processing should show you what is happening in your formula allowing you to troubleshoot any difficulties with it.
Labels: Excel 2003, Excel 2007, Excel 2010, Excel formulas, formula auditing, troubleshoot
Categories:Uncategorized
posted by Helen Bradley @ 7:04 am1 Comment links to this post
Thursday, November 5th, 2009
When you need to enter data from a small subset of entries into a range in Excel 2007 you can do it more easily using a custom designed dropdown list.
To configure a dropdown list in a cell type the list of items to use in a single column in a spare sheet in the workbook.
Select these cells and choose the Formulas tab Define Name, type DataForList as the name in the dialog, set the scope to Workbook and click Ok.
Switch to the sheet where you want to add your dropdown list to some cells, select the cells that should display a list of data to choose from and choose Data tab > Data Validation > Settings tab.
From the Allow: dropdown list choose List and, in the Source area, type =DataForList and click Ok.
Now, whenever you click a cell in this range you’ll see a dropdown list appear from which you can choose a list entry for that cell.
If you’re using Excel 2003, here is a link to an earlier post explaining how to do this in Excel 2003:
Automatic cell entries in Excel 2003
http://www.projectwoman.com/labels/validation.html
Labels: automatic, data entry, dropdown list, Excel 2003, Excel 2007
Categories:Uncategorized
posted by Helen Bradley @ 8:18 pmNo Comments links to this post
Friday, October 23rd, 2009
The new Excel 2007 has far superior tools for finding and removing duplicate entries in a list. Thankfully – because this has been a nightmare in earlier versions.
To find and remove duplicates from a list of data in Excel 2007 first format the area as a table by selecting it and, from the Home tab, choose Format as Table.
Click on any cell in the table and choose Table Tools > Design tab on the Ribbon.
Select Remove Duplicates to display the Remove Duplicates dialog. In this dialog are the Column headings for your data and all are selected by default. To remove the duplicate data from your worksheet leave all the column headings selected and click Ok.
If you want to remove rows where only certain data matches, leave the column headings for those particular rows selected and deselect the column headings for those columns which may have data that differs from one row to another. Now click Ok.
It is sensible to save your worksheet before running this Remove Duplicates option just in case you delete data by accident. If this happens and if you haven’t closed the file, you can recover it using the Undo button.
If you are using an earlier version of Excel, here are links to earlier relevant posts:
Excel – finding duplicates
http://www.projectwoman.com/2007/02/excel-finding-duplicates.html
Check for duplicates in an Excel list
http://www.projectwoman.com/2007/01/check-for-duplicates-in-excel-list-1.html
Labels: Excel 2007, list, remove duplicates, table
Categories:Uncategorized
posted by Helen Bradley @ 6:59 pmNo Comments links to this post
Wednesday, July 29th, 2009
You can do so much with Excel macros – they can be so powerful.
Here is a macro that formats a cell depending on its contents when you type something in it.
If you type a number, or a formula that returns a number, it is formatted one way, if you type a date it is formatted another way and if you type a word it is formatted a different way.
The macro uses the OnEntry event which fires whenever something is entered into a cell. If you attach the macro to an Auto_Open macro you’ll ensure it is run whenever the workbook is opened.
To create the macro, choose Tools > Macro > Visual Basic Editor and, choose Insert > Module to add a module to the current worksheet. Type the code into the dialog.
Sub Auto_Open()
ActiveSheet.OnEntry = “formatCell”
End Sub
Sub formatCell()
If IsNumeric(ActiveCell) Then
ActiveCell.Font.Name = “Verdana”
ActiveCell.Font.Size = 12
ActiveCell.Font.ColorIndex = 46
ElseIf IsDate(ActiveCell) Then
ActiveCell.Font.Name = “Verdana”
ActiveCell.Font.Size = 10
ActiveCell.Font.ColorIndex = 50
Else
ActiveCell.Font.Name = “Times New Roman”
ActiveCell.Font.Size = 12
ActiveCell.Font.ColorIndex = 5
End If
End Sub
Sub Auto_Close()
ActiveSheet.OnEntry = “”
End Sub
Back in Excel choose Tools > Macro > Auto_Open to run the macro the first time to test it. Provided you have Excel configured to run macros, it will run automatically every time you open the workbook in future.
To learn more about Auto_open, AutoOpen and other fun macro naming conventions in VBA, visit this blog post:
What’s in a name? Auto_Open or AutoOpen What’s in a name? Auto_Open or AutoOpen
http://www.projectwoman.com/labels/Auto_Open.html
Labels: Auto opens, autoopens, conditioanl format, Excel 2007, format cells. number, VBA
Categories:Uncategorized
posted by Helen Bradley @ 7:12 pmNo Comments links to this post
Tuesday, July 14th, 2009
When you need to print one version of a worksheet for yourself and another for the boss and you like it small and he likes it to be – well just how he likes it, then you need Views. The Excel Views tool lets you configure a worksheet for different printing options and to save these so you can use them again later on.
You can set views up so you do one for your boss and one for you. Or, you can set one up to print only the summary part of a worksheet and another to print the lot. Even if the print areas and the print settings change, Views let you preconfigure them so you don’t have to set them up manually every time. Better still, Views are saved with the worksheet so they’re always available.
Step 1
To save a set of printing settings, first set up your worksheet with the print settings you want to use including setting a print area if needed.
Step 2
To save this set up, choose View > Custom Views > Add (in Excel 2007 choose the View tab > Custom Views > Add). Type a name for the view that explains what settings you have selected. Enable the Print Settings checkbox and click Ok. You can now create another view and save it. Do this for as many different settings as you need. Save your worksheet.
Step 3
In future, before you print, choose View > Custom Views > select the View you want to use and click Show. Now go and print the worksheet – your settings were saved so you don’t need to configure them.
Labels: Excel 2003, Excel 2007, print layout, printing, Views
Categories:Uncategorized
posted by Helen Bradley @ 5:11 pm1 Comment links to this post
Wednesday, June 10th, 2009
It isn’t always the case that you want to chart multiple series of data on a single chart. Sometimes you only have a single series and Excel, by default, plots all the bars or columns so they are colored identically. Boring!
Luckily, in Excel 2007 a solution is at hand. Simply select and right click the series and choose Format Data Series > Fill > Vary Colors by Point. Excel colors each bar a different color. Best of all, the colors are linked to themes so you can change the colors by changing the Theme – the theme tools are on the Page Layout tab.
So, no more boring single color charts – ever – please!
Labels: chart, Excel 2007, multicolored, series
Categories:Uncategorized
posted by Helen Bradley @ 5:19 amNo Comments links to this post
Monday, April 6th, 2009
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.
Labels: analysis toolpak, date functions, Excel 2003, Excel 2007, holidays, NETWORKDAYS
Categories:Uncategorized
posted by Helen Bradley @ 3:00 pmNo Comments links to this post
Tuesday, February 24th, 2009
I work with monthly worksheets and they develop over the month in such a way that I want the starting point for the next month to be where this month stops. So, I need to copy this month’s worksheet to make next month’s.
In Excel this is simple. Right click the tab at the foot of the worksheet and choose Move or Copy sheet. Make sure you click the Create a Copy checkbox so you make a copy or you’re toast – you’ll lose your original.
Now choose the position in the current workbook to make the copy or choose another or new workbook. Click OK and the copy is made.
I then strip out all of last month’s variable data and start over for this month… but I always make sure I made a copy before I do this, you never can be too sure.
Labels: Excel 2007, named range
Categories:Uncategorized
posted by Helen Bradley @ 4:53 amNo Comments links to this post
Sunday, February 22nd, 2009
I earlier versions of Excel there were some pretty horrid autoformats you could use to quickly format your tables. These are gone from Excel 2007 – thankfully!
Instead, Excel 2007 offers sophisticated formatting options, but they can be applied only if you’re prepared to convert your range to a table. However, this isn’t always a desirable outcome but luckily there is a work around.
Start by selecting your data and click Home tab > Format as Table and select a table format. You will notice that Excel applies an AutoFilter to your data which is typically the most obvious part of the conversion that you don’t want. Other things come with this table format too – if you have data alongside the table – but not part of it – and you delete a row from a table or insert a row into a table, the data alongside remains in place.
So, if you want to retain the nice formatting but not the table behaviour, click in the table and choose Table Tools > Design tab and click the Convert to Range option and click Yes when prompted to convert the table to a normal range. The range will go back to behaving like a typical Excel range and the formatting will be retained.
Labels: autoformat, Excel 2007, workaround
Categories:Uncategorized
posted by Helen Bradley @ 5:49 amNo Comments links to this post
