Excel Answers

Can I have the sheet name automatically display in the worksheet?: You can use the CELL function to have the sheet name display in the worksheet. The worksheet must be in a saved workbook in order for this function to work.
=MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,255)
The Cell function returns information about the given cell, if the info type (first argument) of filename is requested, the returned info is the complete path to that cell - for example:
C:\Users\UserName\Documents\[sample.xlsx]Sheet1
The Find function is looking for the first instance of a right square bracket in the filename info, which indicates the end of the workbook name, and returns its character position. The Mid function is therefore taking the location of the ] in the path to the current cell, and starting in the next position, returning up to the next 255 characters, which will be your sheetname.
Return to FAQ

How can I figure out someone or something's age in Excel if I have their birth date?: Depending on whether you want an actual age or just to round down to the last whole year, choose one of the following:
=(TODAY()-Cell ref to Birth date)/365.26 to show the exact number of years, including a decimal for a partial year - but don't use this for people's ages.
=INT((NOW()-Cell ref to Birth date)/365.26) will modify the above formula to round down to the whole year only. You can use this one to avoid having ages go up before the actual birth date! If the birth date were in cell B2, the formula would look like this: =INT((NOW()-B2)/365.26)
There is also an undocumented function included with Excel that will allow you to find differences between dates, surprisingly called DATEDIF. Using this function, you could enter the following formula:
=DATEDIF(Cell ref to Birth date, NOW(), "y") or, using our example reference, =DATEDIF(B2, NOW(), "y").
You could also use the TODAY function, making the formula =DATEDIF(B2, TODAY(), "y")
Return to FAQ

How can I add total hours? I always get an answer less than 24:00?: You need to change the format of the cell containing the total hours from a format for a 24 hr clock to a format that can display cumulative hours past 24 - from Format, Cells, select a Time format that shows more than 24 hours, like 37:30:55 or enter a custom format with the hours in square brackets: [h]:mm.
Return to FAQ

If I have a date in Column C, how can I get Column D to automatically show Friday's date?: The following formula will display the date in C2 if it is a Friday, and the next Friday's date for any other day of the week:
=C2+IF(WEEKDAY(C2)=7,13-WEEKDAY(C2),6-WEEKDAY(C2))
Return to FAQ

I have a contract date that I would like to have Excel figure a due date of two business days later. Is there any way I can do that?: =IF(OR(WEEKDAY(A2)=5,WEEKDAY(A2)=6),A2+4,A2+2)
Where cell A2 contains the contract date. The function is checking to see if the contract day is Thursday or Friday, if so it adds 4 days to get the due date. If not, it adds two days to any other contract day.
Now, there is an easier way! =WORKDAY(A2,2) The WORKDAY function even allows a third argument to exclude specific dates for holidays. Check it out.
Return to FAQ

I have an Excel table that has the name all in one field. How can I fix this?: Insert enough extra columns to the right of the name to accommodate the break out of your name field. It may be safer to copy them into a blank worksheet and work on them there. Select the names and from the Menu Bar choose Data and then Text to Columns.... The Convert Text to Columns Wizard will open. The wizard is easy to follow. If your original was lastname, firstname, choose Delimited in step one and Comma in step two. If your original was firstname lastname, choose Delimited in step one and Space in step two. Be aware though, that using spaces as delimiters usually means you have to do a little extra clean up work to fix people with multiple first or last names.; Text to Columns can be found in the Data Tools group of the Data tab on the Ribbon in Excel 2007.
Return to FAQ

I import a lot of stuff from text files, but the numbers come in as text and sometimes include spaces, is there an easy way to fix this?: Enter the number one - 1 - in an empty cell. With that cell selected, click Copy. Select the range that contains numbers formatted as text that you would like to convert. Click the bottom half of the Paste button and Paste Special in 2007, or Edit and Paste Special in earlier versions. In the Paste Special dialog box, under Operation, select Multiply, and click OK. The numbers will now be values and you can delete the 1 you entered in order to perform this operation.
Return to FAQ

How come I have funny characters showing in the formula bar at the beginning of each cell?: Tools, options, transition tab, transition navigation keys is apparently turned on. This is placing Lotus alignment codes at the beginning of each cell.
' (apostrophe) Left align data in the cell
" (quotation mark) Right align data in the cell
^ (caret) Center data within the cell
Remove the check mark to get rid of the 'funny characters'.
Return to FAQ

Is there an easy way to fill alternate rows with color?: Select the area you wish to have shaded and select: format, conditional formatting from the menu bar - click the list button to change Cell Value is: to Formula is: and enter the following:
=MOD(ROW(),2)=0
click the format button and pattern tab to set a background color of your choice.
Click okay and even rows should now be filled, while odd rows are still white. Use:
=MOD(ROW(),2)=1
instead if you wish to reverse that.
Return to FAQ

How can I change the way a new blank workbook looks when it opens? I always want (fill in your choice: a different font, the header, the footer, etc.) to be the same for every workbook, is there a way to do it?: You need to create a new, default workbook template and/or a new, default worksheet template.
Open a new, blank workbook and format all of the worksheets as desired (whatever you find yourself changing for every workbook: footer, font, column width, orientation, etc.). Click Save as and change the file type to Excel Template (*.xltx) for Excel 2007 and Template for Excel 2003. Change the file name to book and save it in the XLSTART folder under your office installation folder. (Usually something like C:\Program Files\Microsoft Office\Office12\XLSTART). This will take care of any new workbooks you open. If you also want your options to apply to new worksheets added to a workbook, create a template for new worksheets as well. Open a new, blank workbook, delete all but the first sheet, set that sheet up as desired and save it as a template with the name sheet in the XLSTART folder.
Return to FAQ

I am trying to use the "&" character in a header/footer. It will not print or show in print preview but will otherwise display. How can I fix this?: Enter the ampersand twice. If you want to print Smith & Jones, enter Smith && Jones in the header.
Return to FAQ