questions-interviews.com

Interview Questions and Answers, Frequently Asked Questions (FAQs) and Useful Articles

Home  >> Interview Questions and Answers  >> Applications Programs  >> Microsoft Excel Interview Questions and Answers

Interview Questions & Answers


Microsoft Excel Interview Questions and Answers


This page contains the collection of Microsoft Excel Interview Questions and Answers / Frequently Asked Questions (FAQs) under category Applications Programs. These questions are collected from various resources like informative websites, forums, blogs, discussion boards including MSDN and Wikipedia. These listed questions can surely help in preparing for Microsoft Excel interview or job.

Page 1 Page 2 Next >>

In MS Access, the long and short date option does not show 4 digit years. How do I achieve the mm/dd/yyyy format?  

How do I put password to protect my entire Spreadsheet so data cannot be changed?  

What is Freeze Panes and how do I do it?  

How do I Format data in MS Excel?  

How do I resize Columns and Rows to better fit the data in MS Excel?  

How do I combine different chart types into my Excel spreadsheet?  

Using Excel is there a way to close all open Excel files at once instead of closing them one at a time?

I have converted my MS Access database to a current conversion. I am getting conversion errors?  

Is there a way to apply the same formatting to every sheet in a workbook in Excel?  

How can I identify which cells in my spreadsheet have a formula and which do not in MS Excel?

How can I printout the formulas in an Excel spreadsheet - rather than the results?

Is it possible to change the color and font of the sheet tabs?  

How do I find the first empty cell in column A?  

How can I unprotect a VBA project using code?

Some of the functions that we want our macros to accomplish are not possible without first unprotecting the worksheet/workbook with the password?

Is it possible to call a macro from the condition true or false side of a worksheet formula? i.e. If(A2="OK",Run macro1,run macro2)?  

Is there a way to protect the macros I create so people can not see or alter them?  

I want to show a userform each time my file is opened?  

I want to add a toolbar to my spreadsheet that when clicked, brings up Userform1?  

How do I run a macro every time a certain cell changes its value?

When a button is drawn onto a sheet the assign macro is not displayed. When right-clicking on the button the "Assign Macro" context menu item is not present?

I have a user defined function that does not recalculate?

My Stop Recording toolbar has disappeared. How do I get it back?

Can I ask my user for confirmation before executing the macro?  

Can I have my Macro make Excel NOT ask "the file already exists, do you want to overwrite" type of questions?  

All of a sudden, when I open the file, it asks if I want to "Enable or Disable a Macro". There are no macros in this workbook?

How can I save a chart as a GIF file?

How can I print a workbooks full path in the header or footer? The Page Setup dialog box does not seem to offer the option.?

How can I enter the current date into a cell so it does not change from day to day? When I use the NOW function, it always shows the current date?

I need to determine the difference between two times. If the difference is positive, it works fine. But Excel does not want to display negative time values. Why not?

Is it possible to determine the day of the week for a particular date?

I have a worksheet that shows total hours and minutes worked, along with the hourly pay rate. When I multiply these vales, I do not get the result I am looking for. What is wrong?

I have a range of time values, but when I try to sum them, the total is never greater than 24 hours?  

How can I calculate the difference between two dates?

I find that sometimes my formulas do not get fully calculated. This often happens when I use custom functions created with Visual Basic?

How can I make text in a cell display in multiple lines?  

When I open a workbook, Excel asks if I want to update the links. I have looked everywhere, and I can not find any links in my formulas?

Can I write a formula that returns the number of distinct entries in a range?

I have created some clever formulas, and I don not want anyone else to see them. Is it possible to hide the formulas but display the results?

I have a price list stored in a worksheet, and I need to increase all prices by 5 percent. Can I do this without reentering all the prices?  

Is there a function that returns the name of the worksheet?  

How can I increase the number of rows or columns in a worksheet?

I have saved my workbook with a password, but Excel does not recognize it and would not let me open the file. Am I out of luck?

Can I change the color of the worksheet tabs in my workbook?

When I enter a value, it appears with two decimal places. For example, when I enter 154 it shows up as 1.54. Whats wrong?  

I am converting from Quatro Pro to Excel. What happened to my cell comments?  

I am trying to open files called "Excel 97 Templates" with my Excel for Windows 95, Version 7.0. I get the message: "(filename).xls: file format is not valid". What gives? Is the "Excel 97" saved file just unreadable by my version?

My question is when your working with multiple worksheets how do you transfer added figures from one worksheet to another without losing the total?

I have a list of 3215 product names on a spreadsheet. Each resides in a single cell. I need to put each name in quotes. The names are all in one column?

I wish to count the number of cells in a col or row that have a value > 0. How is this done? The zero is displayed in the cells. Count() will only ignore a blank cell?

There are many monetary signs in Excel97 but I have not found one yet for the new euro currency. Any suggestions?  

I am trying to get the percentage of two cells by dividing one cell into the other. Some of the time both cells might have a Zero in each. Therefore the DIV?

How do I make a copy or a back up of a workbook that will not fit on a single 3.5 disk, so that I may transfer from home to office?  

I am new at this Excel game. How do you figure elapsed time? I have formatted the cells in military time, create a start time column and an end time column and would like to know the difference?

Thanks for your great answer, but this results in my data be backwards; i.e. rather than 12345 I need it to be 54321. Any further thoughts?

I download data which comes to me in a column. I want to use it in a complex sheet that needs data in a row. How can I convert the column data into row data?

How can I enter the symbol for the new Euro currency in my spreadsheet?  

I want to add 25 to all of the cells in column A. I do not want a new column, I want to add it right to the cells in column A?  

I have a spreadsheet where I want to sum cells from column E if the corresponding date in column A is within the last 60 days?  

I use a large spreadsheet on a daily basis with 31 worksheets (one for each day of the month). Each worksheet is huge.I would like to insert another sheet in the workbook and insert a button to copy another worksheet to the end of the workbook and then rename the sheet to the correspondingday of the month?

Somehow, my worksheet has external links to files that are no longer around. How can I delete the links?  

Where can I find some really good information on how excel treats year 2000 dates? I need simple (and that is what u guys provide)?  

Ihave been working with downloaded data and pasting a years worth at a time into a "template" that I want all the resulting spreadsheets to look like. Each years data has approximately the same amount of data in it. Seems each spreadsheet is taking more and more space as I work on them? Any ideas what I have done?

I want to count the number of cells in a range that have a particular interior color. The countif function seems like the right kind of answer but how to I return the color of the interior for each cell in a range? I would like to do something like: =countif(b1:b10, cell("interior","Red"))?

Well, then how do I fit something to one page wide?  

I created a worksheet in Excel 95. In Excel 97, Excel ignores my page breaks?  

Maybe you can help.... I use Excel not for calculation but for maintaining large mailing lists which I usually import from text files.Simple question... how do I keep Excel from dropping the leading zero when I import a 4-digit mail code? I NEED that zero there. I understand that I can format the cells to text and THEN TYPE the number in and the zero stays... but who has time for that? Is there a way to IMPORT or OPEN a text file in excel and keep the leading zero?

Can I see a list of the ColorIndex and the corresponding color?  

In visual basic, I can set the color of a cell with the .interior.colorIndex property. How can I read the color of a cell before I change it?  

How can I make the screen stop flashing during macro execution?  

How can I get Excel to accept an entry like "5+5" without the leading equals sign as a formula? I have done this is the past but can not remember how.?

How can I speed up my Macro execution?

How do I have EXCEL execute a macro automatically upon starting EXCEL?

How can I format a worksheet to have alternating green and white rows like a "greenbar" report?  

How can I show the complete path and filename in the Excel title bar?

Page 1 Page 2 Next >>

Top     

In MS Access, the long and short date option does not show 4 digit years. How do I achieve the mm/dd/yyyy format?
In the date field properties of the table, form, or properties manually input the format you would like. It is not required to choose one of the date formats in the drop down box. Example would be mm/dd/yyyy.

How do I put password to protect my entire Spreadsheet so data cannot be changed?
1. Click Tools
2. Scroll down to Protection, then Protect Sheet
3. Enter a password, Click OK
4. Re-enter password,
Click OK

What is Freeze Panes and how do I do it?
1. Row - Select the row below where you want the split to appear
2. Column - Select the column to the right of where you want the split to appear
3. Go to the Menu Bar
4. Click Windows
and then click Freeze Panes

How do I Format data in MS Excel?
1. Must Always highlight the data before formatting
2. Click Format
3. Then go to Cells

How do I resize Columns and Rows to better fit the data in MS Excel?
1. Move the mouse in between any two labels (Rows/numbers or Columns/letters)
2. The pointer will turn into a vertical (letters) or horizontal (numbers) line with arrows on both ends
3. Simply drag the column or row to the desired size

How do I combine different chart types into my Excel spreadsheet?
To combine chart types, follow these steps:
1. If the Chart toolbar isn't already displayed, right-click any Toolbar and select Chart.
2. On the chart, click the series you want to change.
3. On the Chart toolbar, click the arrow next to the Chart Type button and then select the new chart type for the series (in our example, a line chart).

Using Excel is there a way to close all open Excel files at once instead of closing them one at a time?
Yes, you can close down all your Excel files at once by using the following instructions:
1. Hold down the Shift key.
2. Choose File + Close All from the menu. Holding down the Shift key changes Excel's File + Close command to a File + Close All command.

I have converted my MS Access database to a current conversion. I am getting conversion errors?
Design specs have changed in new releases of MS Access. Visit the on-line help option for "conversion and compatibility" that explains different portions of Access databases that will have difficulty converting. Some reprogramming may be required.

Is there a way to apply the same formatting to every sheet in a workbook in Excel?
Yes. To do this, you will need to right click on one of the worksheet tabs and then choose Select All Sheets. After you do this any formatting that you apply or text you enter will show up on all the sheets in your workbook. In order to eliminate certain sheets from the changes, hold down the Ctrl key and click on the tab of the worksheet you want excluded from the others. You can also group sheets by holding the shift key and selecting the worksheet tab.

How can I identify which cells in my spreadsheet have a formula and which do not in MS Excel?
Option A:
1. Choose Edit + Go To (or press Ctrl + F5).
2. Select Special.
3. Select Formulas.
4. Click OK.

Option B:
1. Choose Tools + Options.
2. Select the View Tab
3. In Window Options choose the check box 'Formulas'.
4. Click OK

How can I printout the formulas in an Excel spreadsheet - rather than the results?
The trick is to change the way Excel displays the worksheet before you choose to print. Check the box Tools, Options, View, Formulas and you'll see the formulas appear in each cell (with the columns changed to fit). When you print the sheet the formulas will be printed instead of the values

Is it possible to change the color and font of the sheet tabs?
Yes we can change the color of sheet tabs. By right clicking on sheet tabs and you will get option change color but i didn't find any option to change the font of sheet tabs.

How do I find the first empty cell in column A?
If ActiveSheet.UsedRange.Count < 2 Then
MsgBox 1
Else
MsgBox Columns("A:A").Find(What:="", LookAt:=xlWhole).Row
End If

How can I unprotect a VBA project using code?
You cannot. A workaround is to simulate keystrokes with the SendKeys method

Some of the functions that we want our macros to accomplish are not possible without first unprotecting the worksheet/workbook with the password?
Worksheets("MySheet").Unprotect password:="drowssap"
'your code here
Worksheets("MySheet").Protect password:="drowssap"

Be sure to protect your macro code to hide the sheet password.

Is it possible to call a macro from the condition true or false side of a worksheet formula? i.e. If(A2="OK",Run macro1,run macro2)?
Basically, the answer is No. You can write functions in VBA that you can call from worksheet cells, but these functions can only return a value. They can't modify other cells or alter any part of the Excel environment. (You may be able to use a worksheet change event to call the macro.)

Is there a way to protect the macros I create so people can not see or alter them?
Go to Tools > VBAProject properties, lock the project for viewing, and enter a password.

I want to show a userform each time my file is opened?
Combine the two solutions above:

Private Sub Workbook_Open()
UserForm1.Show
End Sub

or

Sub Auto_open()
UserForm1.Show
End Sub

I want to add a toolbar to my spreadsheet that when clicked, brings up Userform1?
Assign the toolbar button to this macro, which should be in a standard VBA module:

Sub ShowForm ()
Userform1.Show
End Sub

How do I run a macro every time a certain cell changes its value?
There is an event called Worksheet_Change which is triggered when a value is entered (it will not fire when a formula result changes). One of the arguments to this event is 'Target' which is a reference to what changed. Since this event will occur whenever a value changes - you can use the target to see if it is the cell you are interested in:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("C5")) Is Nothing Then
Exit Sub
Else
'The cell you are monitoring has changed!
'Do whatever you need to do...
End If
End Sub

When a button is drawn onto a sheet the assign macro is not displayed. When right-clicking on the button the "Assign Macro" context menu item is not present?
There are buttons from the Forms toolbar and there are buttons from the Control Toolbox. If "Assign Macro" is not an option then it's from the Control Toolbox.

I have a user defined function that does not recalculate?
Include all the cells that your UDF depends on in the argument list. Or enter this as the first statement in your Function:

Application.Volatile

This will cause the function to be executed whenever a calculation occurs in the workbook

My Stop Recording toolbar has disappeared. How do I get it back?
To reactivate the Stop Recording toolbar:

1. Choose Tools | Macro | Record New Macro
2. Click OK
3. Choose View | Toolbars | Stop Recording
4. Click the Stop Recording button (the blue square)
The next time you record a macro, the toolbar should automatically appear.

Can I ask my user for confirmation before executing the macro?
Sub AskAndDo()
If MsgBox("Are you sure ?", vbYesNo + vbQuestion) = vbNo _
Then Exit Sub
'Code goes here instead of
MsgBox "Actions here"
End Sub

Can I have my Macro make Excel NOT ask "the file already exists, do you want to overwrite" type of questions?
Application.DisplayAlerts = False
'code to save, overwrite, delete, whatever goes here
Application.DisplayAlerts = True

All of a sudden, when I open the file, it asks if I want to "Enable or Disable a Macro". There are no macros in this workbook?
A macro has been added and then removed, leaving an empty module. Empty modules trigger the macro query, as does an actual macro.
1. Warning: As a precaution, you should make a backup copy of the file, before you remove any code.
2. Right click on any sheet tab and choose View Code, to open the Visual Basic Editor.
3. In the Project Explorer at the left of the screen, find the workbook. In the sample shown here, Book4 is the workbook name -- VBAProject (Book4)
4. Look for a Modules folder, and open it. (If there is no Modules folder, go to Step 6.)
5. For each module in the folder:
1. Right-click on the module name.
2. Choose Remove Module1 (the name of your module may be different)
3. Click No when asked if you want to Export.
6. Open the Microsoft Excel Objects folder.
7. For each worksheet, and for ThisWorkbook:
1. Double-click on the object name, to open its code module. In this sample, you'd double-click on Sheet1 (Sheet1)
2. On the keyboard, press Ctrl+A to select all the code (even if the code module looks empty)
3. Press the Delete key.
8. Look for a Forms folder, and open it.
9. Delete any UserForms that it contains.
10. Look for a Class Modules folder, and open it.
11. Delete any class modules that it conta
12. Close the Visual Basic Editor.
13. Save the changes to the workbook.

How can I save a chart as a GIF file?
You can save your worksheet as an HTML file, and Excel will automatically convert any charts to GIF files.

If that seems like overkill, you can write a simple macro that will do the job. Press Alt-F11 to activate the Visual Basic editor. Select your workbook in the Projects window, and choose Insert, Module to insert a new VBA module. Then type the following four-line procedure into the module:

Sub SaveChartAsGIF ()

Fname = ThisWorkbook.Path & "" & ActiveChart.Name & ".gif"

ActiveChart.Export FileName:=Fname, FilterName:="GIF"

End Sub

After the macro is entered, reactivate Excel and click the chart to be saved. Press Alt-F8 to display the Macro dialog box. Select the SaveChartAsGIF macro and click Run. The procedure uses the chart's name as the GIF file name, and the file is stored in the same directory as the workbook.

This simple macro does no error checking, so it will generate an error if a chart is not selected or if the workbook has not been saved.

How can I print a workbooks full path in the header or footer? The Page Setup dialog box does not seem to offer the option.?
For whatever reason, Microsoft continues to ignore what must be thousands of requests for this feature. Although Microsoft Word offers this feature, Excel offers no direct way to print a workbook's full path in the header or footer. The only solution is to create a macro. The technique described below works with Excel 97 and later.

In Excel, press Alt-F11 to activate the Visual Basic editor. In the Project window, double-click the project that corresponds to your workbook. The project list will expand to show several objects. Double-click the item labeled Microsoft Excel Objects, and then double-click the object labeled ThisWorkbook. Enter the following three lines of VBA code into the code module for the ThisWorkbook object (usually in the right pane of the window you're seeing at this point).

Private Sub Workbook_BeforePrint(Cancel As Boolean)

ActiveSheet.PageSetup.LeftHeader = ThisWorkbook.FullName

End Sub

After inserting the code, press Alt-Q to return to Excel.

This procedure will be executed before you print or preview your workbook. It simply inserts the workbook's path into the left header position. If you prefer to put the path in a different position, substitute any of the following for LeftHeader: CenterHeader, RightHeader, LeftFooter, CenterFooter, or RightFooter.

How can I enter the current date into a cell so it does not change from day to day? When I use the NOW function, it always shows the current date?
The easiest way to enter the current date into a cell is to use the Ctrl-; key combination (press the Ctrl key and type a semicolon). Similarly, you can enter the current time by pressing Ctrl-Shift-; (press the Ctrl and Shift keys and type a semicolon). Both of these key combinations enter the information as a volatile value, not as a formula.

I need to determine the difference between two times. If the difference is positive, it works fine. But Excel does not want to display negative time values. Why not?
Excel stores dates and times as numeric values, so it should be possible to add or subtract one from the other. The problem occurs if you have a workbook containing only times and no dates. As you discovered, subtracting one time from another doesn't always work. Negative time values appear as a series of pound signs, even though you've assigned the [h]:mm format to the cells. By default, Excel uses a date system that begins with January 1, 1900. A negative time value generates a date-time combination that falls before this date, which is invalid.

The solution is to use the 1904 date system. Select Tools, Options. In the Options dialog box, click the Calculation tab and check the 1904 date system option to change the starting date to January 2, 1904. Your negative times will now be displayed correctly.

If you use the 1904 date system, be careful when linking to date cells in other workbooks. If the linked workbook uses the 1900 date system, the dates retrieved by the links will be incorrect.

Is it possible to determine the day of the week for a particular date?
Yes, and Excel offers several ways to do so. Assume that cell A1 contains a date value. The formula below uses the WEEKDAY function, which returns an integer between 1 and 7 (1 for Sunday, 2 for Monday, and so on).

=WEEKDAY(A1)

If you'd prefer to see words rather than integers, modify the formula as follows:

=CHOOSE(WEEKDAY(A1), "Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday")

Another approach is to change the number format for the date cell. Activate the cell that contains your date, and then choose Format, Cells. In the Format Cells dialog box, click the Number tab. Choose Custom from the Category list, and type a custom number format string into the box labeled Type. The trick here is to use dddd as part of the format string. For example, a format string of dddd mmmm d, yyyy will display the date and the day of the week, like this: Thursday November 23, 2000.

I have a worksheet that shows total hours and minutes worked, along with the hourly pay rate. When I multiply these vales, I do not get the result I am looking for. What is wrong?
You need to multiply the result by 24, for the number of hours in a day. If cell A1 contains the number of hours worked (for example, 16:45, for 16 hours and 45 minutes) and cell B1 has the hourly rate, the formula below will calculate the total wages:

=A1*B1*24

Make sure the cell that contains the formula is formatted as a number, not a time.

I have a range of time values, but when I try to sum them, the total is never greater than 24 hours?
When you add a range that contains time values, Excel ignores the hours that exceed 24. The solution is to use a custom number format. Activate the cell that contains your total time, and then choose Format, Cells. In the Format Cells dialog box, click the Number tab. Choose Custom from the Category list, and type [h]:mm into the box labeled Type. Using brackets around the hour portion of the format string tells Excel to display hours that exceed 24 hours.

How can I calculate the difference between two dates?
Excel stores dates as serial numbers. The number 1 represents January 1, 1900, the number 2 represents January 2, 1900, and so on. Formatting these numbers using a date format causes them to appear as actual dates. Therefore, if you have dates stored in two cells, you can simply create a formula that subtracts one from the other to get the number of intervening days. You'll want to make sure that the formula cell is formatted as a number, not a date.

You might also find the DATEDIF function useful. This function, which was not documented prior to Excel 2000, returns the difference between two dates, expressed in years, months, or days. You might use the DATEDIF function to calculate how many months the payment on an invoice is overdue or to determine a person's age when you know their birth date.

Excel's DATEDIF function takes three arguments. Its syntax is:

=DATEDIF(start_date,end_date,units)

In the syntax, start_date is a date or reference to a date, end_date is a date or reference to a date, and units is a one- or two-digit string (in double quotes) specifying the units for the difference between the two dates. Acceptable values for the units argument are shown below.

* y returns the number of full years in the period.
* m returns the number of full months in the period.
* d returns the number of full days in the period.
* md returns the number of full days in excess of the last full month.
* ym returns the number of full months in excess of the last full year.
* yd returns the number of full days in excess of the last full year.

For example, assume cells A1 and B1 contain dates. The formula below returns the number of full years between the dates (useful for calculating a person's age):

=DATEDIF(A1,B1,"y")

The formula below calculates the number of full months between the two dates:

=DATEDIF(A1,B1,"m")

I find that sometimes my formulas do not get fully calculated. This often happens when I use custom functions created with Visual Basic?
Microsoft has acknowledged some problems with the Excel calculation engine. In order to be assured that all of your formulas have been calculated, press Ctrl-Alt-F9 to force a complete recalculation.

How can I make text in a cell display in multiple lines?
When entering text into the cell, press Alt-Enter to insert a line break. When you do so, Excel will automatically apply text wrapping to the cell.

To reformat existing cells so they sport wrapped text, select the cells and then choose Format, Cells. On the Alignment tab, select "Wrap text," and click OK.

When I open a workbook, Excel asks if I want to update the links. I have looked everywhere, and I can not find any links in my formulas?
I've never known Excel to be wrong about identifying links, so there's an excellent chance your workbook does contain one or more links--but they are probably not formula links.

* If you have a chart in your workbook, click each data series in the chart and examine the Series formula in the formula bar. If the formula refers to another workbook, you've identified the link. To eliminate it, move the chart's data into the current workbook and recreate your chart.
* If your workbook contains any dialog sheets, select each object in each dialog box and examine the formula bar. If any object contains a reference to another workbook, edit or delete that reference.

If these two approaches don't solve your problem, follow these steps:

1. Select Edit, Links. (In some cases, this command is not available. If you can't select it, skip to step 4.) The Links dialog box will appear.
2. Click the Change Source button and change the link to the active file.
3. Select Insert, Name, Define. Scroll down the list in the Define Name dialog box and examine the "Refers to" box. Delete names that refer to another workbook or that contain an erroneous reference (such as #REF!). This is the most common cause of "phantom links."
4. Save your workbook. When you reopen it, Excel won't ask you to update links.

Can I write a formula that returns the number of distinct entries in a range?
First, let's clarify the question. We're hunting for a formula that, given the range 100, 99, 98, 100, 98, 100, 98, would return 3. This type of counting requires an array formula. The formula below, for example, counts the number of distinct entries in the range A1:D100.

=SUM(1/COUNTIF(A1:D100, A1:D100))

When you enter this formula, you must press Ctrl-Shift-Enter. Pressing only Enter will give you the wrong result. Excel will place brackets around the formula to remind you that you've created an array formula.

The preceding formula works fine in many cases, but it will return an error if the range contains any blank cells. The formula below (also an array formula, so input it with Ctrl-Shift-Enter) is more complex, but it will handle a range that contains a blank cell.

=SUM(IF(COUNTIF(A1:D100,A1:D100)=0, "", 1/COUNTIF(A1:D100,A1:D100)))

I have created some clever formulas, and I don not want anyone else to see them. Is it possible to hide the formulas but display the results?
step 1:
Every cell has two key properties: locked and hidden. A locked cell can't be changed, and the contents of a hidden cell don't appear in the formula bar when the cell is selected. By default, every cell is locked and not hidden. But it's important to remember that these attributes have no effect unless the worksheet itself is protected. First, to change the attributes, select the appropriate cell or range and then choose Format, Cells. In the Format Cells dialog box, click the Protection tab and select Locked or Hidden (or both). Unlock cells that accept user input, and lock formula and other cells that should stay unchanged (such as titles). To prevent others from seeing your formulas, lock and hide the formula cells: The results of the formulas will be visible, but the formulas will not.

step 2:
Now, to protect the worksheet, choose Tools, Protection, Protect Sheet to bring up the Protect Sheet dialog box. Make sure the Contents box is checked. You can enter a password to prevent others from unprotecting the sheet. Locked cells in a protected sheet cannot be edited, and other worksheet changes are disabled. For example, no one can insert rows or columns, change column width, or create embedded charts.

I have a price list stored in a worksheet, and I need to increase all prices by 5 percent. Can I do this without reentering all the prices?
Excel provides two ways to accomplish this. The "traditional" technique goes something like this: Insert or find a blank column near the prices. In that column's first cell, enter a formula to multiply the price in that row by 1.05. Copy the formula down the column. Then select and copy the entire column of formulas, select the original prices, and choose Edit, Paste Special. In the Paste Special dialog box, select Values to overwrite the original prices with the formulas' results. And finally, delete the column of formulas.

The other, more efficient approach also uses the Paste Special dialog box. To increase a range of values (prices, in this example) by 5 percent, enter 1.05 into any blank cell. Select the cell and choose Edit, Copy. Then select the range of values and choose Edit, Paste Special. Choose the Multiply option and click OK. Then delete the cell that contains the 1.05.

Is there a function that returns the name of the worksheet?
Excel's CELL function comes close. The following formula displays the workbook's full path along with the worksheet name:

=CELL("filename")

For example, this formula might return something like:

C:WindowsDesktop[Budget.xls]Sheet2

Returning only the sheet name requires a more complex formula:

=MID(CELL("filename"), FIND("]",CELL("filename"))+1, LEN(CELL("filename"))-FIND("]", CELL("filename")))

How can I increase the number of rows or columns in a worksheet?
In Excel, every workbook has 255 columns and 65,526 rows. These values are fixed and cannot be changed. If you need more rows, you might want to consider using a database (such as Access) or another spreadsheet (Quattro Pro can handle a million rows) for data storage. In most cases, you probably don't need to work with all of your data at once. You'll usually need only a subset of your data. You can use Excel's Data, Get External Data command to query the database and bring in only the records you need.

I have saved my workbook with a password, but Excel does not recognize it and would not let me open the file. Am I out of luck?
First, remember that passwords are case sensitive. So if you originally entered your password as Xyzzy, typing xyzzy won't work. If you know you're entering the password correctly, then it's time to start looking for a password recovery utility. Several utilities exist, and none of them are free. Do an Internet search for "Excel password recovery," and you'll find several products that will come to the rescue. By the way, the fact that these products exist might raise some alarms for the security-minded. Bottom line? Excel password protection isn't as secure as you might expect.

Can I change the color of the worksheet tabs in my workbook?
In a word, no. It would certainly be helpful to be able to color-code your worksheet tabs. For some reason, Microsoft hasn't implemented this feature, which has been available in 1-2-3 and Quattro Pro for quite a while.

When I enter a value, it appears with two decimal places. For example, when I enter 154 it shows up as 1.54. Whats wrong?
Somehow Excel's fixed-decimal mode was turned on. To return to normal, select Tools, Options to display the Options dialog box. Then click the Edit tab and remove the check mark from the "Fixed decimal" option. Of course, this feature can be useful when entering some types of data, but most of the time, you'll want to keep the fixed-decimal mode turned off.

I am converting from Quatro Pro to Excel. What happened to my cell comments?
Excel comments are indicated by a small red dot in the upper right corner of the cell. Float your mouse pointer over the dot and the comment pops up.

I am trying to open files called "Excel 97 Templates" with my Excel for Windows 95, Version 7.0. I get the message: "(filename).xls: file format is not valid". What gives? Is the "Excel 97" saved file just unreadable by my version?
Excel 97 is not backwards-compatible with Excel 95/7.0. Files saved as Excel 97 files do not open in Excel 95/7.0. The standard (and not very friendly) message offered by Excel when you try to open a 97 file in 95 is the "File format is not valid".
If you know someone with Excel 97, they should be able to open the files and then do a "Save As" Excel 95/7.0, but any new features from 97 would be lost.

My question is when your working with multiple worksheets how do you transfer added figures from one worksheet to another without losing the total?
First method: Copy the cell from the original sheet. Go to the new sheet, but do not paste. Do a Edit>Paste Special>Values. This will copy the value instead of the formula to the new sheet.
Second method: set up a formula on the new sheet that points to the total on the back sheet. If your total is on Sheet2, cell D20 you would enter this formula on sheet 1:
=Sheet2!D20
Then, as you change data on Sheet 2 and the totals change, they will automatically update on Sheet 1.

I have a list of 3215 product names on a spreadsheet. Each resides in a single cell. I need to put each name in quotes. The names are all in one column?
If all of your names are in column A, insert a new, temporary column B. The formula for cell B2 would be:
=""""&A2&""""
Yes, there are four sets of quotes there. The first and fourth quote indicate that you need to append text before the value in A2. The 2nd and 3rd quote is a special Excel code to put in a quote mark. Copy the formula from B2 down to B3216. Then, copy column B, and use Paste Special, Values to change the formulas to values.

I wish to count the number of cells in a col or row that have a value > 0. How is this done? The zero is displayed in the cells. Count() will only ignore a blank cell?
In Excel, you would use something called an array formula. If your range of cells is in A1:A100, you would type the following formula:
=SUM(IF(A1:A100>0,1,0)) instead of hitting enter, you would hit Ctrl+Shift+Enter.
This keystroke tells Excel it is an array formula. It will evaluate each cell in the range, and if it is greater than 0, add 1 to your result. Otherwise, it will add zero to your result.
A second solution in Excel 97 is to use Countif. =COUNTIF(A1:A100,">0")

There are many monetary signs in Excel97 but I have not found one yet for the new euro currency. Any suggestions?
Excel 2000 (due out later this year) adds support for the Euro.

I am trying to get the percentage of two cells by dividing one cell into the other. Some of the time both cells might have a Zero in each. Therefore the DIV?
This seems like an incredible pain, but I have to use it all of the time.
If your spreadsheet is set up with the numerand in column A, the divisor in B, and in C you are trying to calculate A/B, then you would enter this formula in cell C2:
=IF(B2=0,"NA",A2/B2)
At my job, we use NA to indicate that a number divided by 0 is not applicable. If, as you say, you want a zero there, then you would use this formula:
=IF(B2=0,0,A2/B2)

How do I make a copy or a back up of a workbook that will not fit on a single 3.5 disk, so that I may transfer from home to office?
I run into this a lot. I save the file to disk, then use the shareware utility WinZip to copy the Excel file into a .zip file. Copy the Zip file to a 3.5 disk, bring it home, copy the zip file to my hard drive and then use WinZip to extract the files. I am usually able to fit 3-4MB of files into a Zip file which will fit on a 3.5" disk.

I am new at this Excel game. How do you figure elapsed time? I have formatted the cells in military time, create a start time column and an end time column and would like to know the difference?
Enter a time in cell A2 as 1:23:45. Enter a second time in cell B2 as 7:45:08. Then, in C2 enter =B2-A2, you will get a series of pound signs. It is because column B isn't wide enough to display a time. Make column C wider. Format C2 as time, if necessary. Now, you should have an elapsed time.

Thanks for your great answer, but this results in my data be backwards; i.e. rather than 12345 I need it to be 54321. Any further thoughts?
Before doing the copy, paste special, transpose, could you sort the original dataset so that it is in reverse order?
Say your original data is in A1:100. Add a sequence of numbers in B1:B100 that run from 1 to 100. (This is easy - enter 1 in B1, 2 in B2, highlight B1:B2 and double click the little square in the lower right corner of B2.)
Now, highlight A1:B100 and use Data Sort to sort by column B, Descending.
Now do the original trick. Highlight A1:A100, Copy, move to D2 and Paste Special, Transpose.
Finally, go back, resort A1:B100 by column B, Ascending, erase the values in B and you are all set.

I download data which comes to me in a column. I want to use it in a complex sheet that needs data in a row. How can I convert the column data into row data?
Great question. Easy answer: Highlight the data in your column. C to copy it.
Then go to a blank section of your spreadsheet. pick Edit > Paste Special.
In the paste special dialog box, check the little box that says "Transpose". Then click OK. Your column data now turns into row data.

How can I enter the symbol for the new Euro currency in my spreadsheet?
Microsoft offers a new Tahoma font with the Euro symbol.

I want to add 25 to all of the cells in column A. I do not want a new column, I want to add it right to the cells in column A?
Temporarily enter 25 in a cell in an out of the way place. Highlight that cell and hit Ctrl-C to copy the cell. Now, highlight your range of cells in column E. Select Edit>Paste Special. Click the add radio button and click OK. The contents of the clipboard (25) will be added to each cell in column A.

I have a spreadsheet where I want to sum cells from column E if the corresponding date in column A is within the last 60 days?
First, I set up a cell in an out-of-the-way section of the worksheet. In cell Z1, enter the formula:
=now()-60
This formula calculates a day which is 60 days prior to today.

Then, the formula you want in cell E101 is: =sumif(A1:A100,">"&$Z$1,E1:E100)

I use a large spreadsheet on a daily basis with 31 worksheets (one for each day of the month). Each worksheet is huge.I would like to insert another sheet in the workbook and insert a button to copy another worksheet to the end of the workbook and then rename the sheet to the correspondingday of the month?
If you worksheet names are numeric, this will do the trick:
Public Sub CopyIt()
' This macro will only work if your sheet names are numeric
' Example: the sheet for the first day is 1
'
' Count how many sheets are in this workbook
LastSheet = ActiveWorkbook.Sheets.Count
' What is the name of the last sheet?
LastName = ActiveWorkbook.Sheets(LastSheet).Name
' Add one to the last name to get the new name
NewName = LastName + 1
' Make a Copy of the last sheet
ActiveWorkbook.Sheets(LastSheet).Copy after:=Worksheets(LastName)
'Rename the sheet for today
NewLast = LastSheet + 1
ActiveWorkbook.Sheets(NewLast).Name = NewName
End Sub

Somehow, my worksheet has external links to files that are no longer around. How can I delete the links?
This happens all of the time to MrExcel. I downloaded a little macro from Microsoft which helps you delete these links. Microsoft has now upgraded the macro to a full blown add-i

Where can I find some really good information on how excel treats year 2000 dates? I need simple (and that is what u guys provide)?
Any 2 digit dates since January of 1930 will be assumed to be 1930 or after. Microsoft converts an entry of 1/1/30 to be 1/1/1930. (It does the conversion in memory - it may not show it in the spreadsheet)
Any 2 digit dates lower than 12/31/29 are assumed to be 2029 or before.
You can avoid the problem altogether by entering the dates with 4 years. Enter 5/27/1922 to enter a date in 1922, or 5/27/2034 for a date in year 2034

Ihave been working with downloaded data and pasting a years worth at a time into a "template" that I want all the resulting spreadsheets to look like. Each years data has approximately the same amount of data in it. Seems each spreadsheet is taking more and more space as I work on them? Any ideas what I have done?
Are you getting just a few percent increase with each file, or was there one file where you picked up a huge increase?
One thing to check it to see where the last active cell on the sheet is. Hit the end key, then hit the home key. (don't hold them both down together. Hit one, then the other). This will take you to the last active cell on the worksheet. If you data extends out to column H1 and down to cell A366, the End-Home should take you to cell H366.
If, for some reason, End-Home takes you to WAY below where you think your data ends, then check to see if you have a blank entry down there. Or, just delete the rows between then end of your data and that last active cell. You then have to save and re-open the file to reclaim the space previously used by those rows.
The other possibility under Excel 97 - do you have Excel set to "Track Changes"? This would be located under the Tools menu.

I want to count the number of cells in a range that have a particular interior color. The countif function seems like the right kind of answer but how to I return the color of the interior for each cell in a range? I would like to do something like: =countif(b1:b10, cell("interior","Red"))?
As long as you are not using conditional formatting to turn your cells red, this will work.
It assumes you have used the red which is in the first column, third row of the interior color dropdown in Excel 97. This is known as colorindex number 3.
In Excel 95, use Insert Module. In Excel 97, use Alt-F11 to open the visual basic editor. Paste in the following code:
Public Function SumRed(Inrange As Range)
SumRed = 0
For Each cell In Inrange
If cell.Interior.ColorIndex = 3 Then
SumRed = SumRed + cell.Value
End If
Next
End Sub
Now, enter the function =Sumred(B1:B10) in your worksheet.

Well, then how do I fit something to one page wide?
Set up the document. Change the scaling to "Fit to 1 pages wide, blank pages tall. Do a page preview. Close the page preview. Back on the Page Setup dialog, Excel has now changed the "Adust to: x%" value to be the proper percentage to make your document fit on one page wide. Change your scaling option from "Fit To:" to be "Adjust to:" and leave the percentage where Excel set it. If you add columns or adjust the size of columns, you will have to repeat this trick.

I created a worksheet in Excel 95. In Excel 97, Excel ignores my page breaks?
If you set the Page Setup Scaling Options to be "Fit to: 1 Pages wide and (blank) Pages tall", you could insert page breaks in Excel 95. Excel 97 ignores all page breaks if you have scaling set to this option.

Maybe you can help.... I use Excel not for calculation but for maintaining large mailing lists which I usually import from text files.Simple question... how do I keep Excel from dropping the leading zero when I import a 4-digit mail code? I NEED that zero there. I understand that I can format the cells to text and THEN TYPE the number in and the zero stays... but who has time for that? Is there a way to IMPORT or OPEN a text file in excel and keep the leading zero?
In Step 3 of 3 of the text import wizard, you have to specify that your Mail Code field is text. In the step 3 dialog box, go down to the preview panel. Scroll right until you can see the Mail Code field. Click the grey box at the top of this column which probably says "General" right now. The whole column will highlight. In the upper right corner there is a box called Column Data Format. Click Text for that particular field (and for any other fields that need the leading zeroes).
Excel will keep the leading zeroes. It will *not* keep leading spaces, so I hope you don't need those.
If you have vast amounts of data that you have already imported and you need to quickly fill will leading zeroes, you can use the formula: If mail codes are in H2, enter this in I2:
=right("0000"&H2,4)
Copy this from I2 down to I9999. Highlight column I. Hit Ctrl-C to Copy, the Edit, Paste Special, Values, OK to change the formulas to text. Then copy column I over top of H.

Can I see a list of the ColorIndex and the corresponding color?
To see the colors, go to the visual basic editor. Hit the help question mark. Type ColorIndex. From the list of topics, select the one called "ColorIndex Property". They have a nice visual table at the bottom of this help topic.
You could also build a color table with a quick macro

Public Sub ColorTable()
For i = 1 To 56
Range("A" & i).Interior.ColorIndex = i
Range("B" & i).Value = i
Next i
End Sub

In visual basic, I can set the color of a cell with the .interior.colorIndex property. How can I read the color of a cell before I change it?
Use a line of code like this:
OrigColor = Selection.Interior.ColorIndex

How can I make the screen stop flashing during macro execution?
Turn off the screen updating during macro execution. Insert this line at the beginning of your macro:
Application.ScreenUpdating = False

How can I get Excel to accept an entry like "5+5" without the leading equals sign as a formula? I have done this is the past but can not remember how.?
Go into Tools>Options. On the Transition tab, check the box for Transition formula entry.

How can I speed up my Macro execution?
Turn off the screen updating during macro execution. Insert this line at the beginning of your macro:
Application.ScreenUpdating = False

How do I have EXCEL execute a macro automatically upon starting EXCEL?
Rename the macro to be AUTO_OPEN.

Or - add the macro to the Workbook_Open procedure on the code pane for ThisWorkbook.

How can I format a worksheet to have alternating green and white rows like a "greenbar" report?
Select the range you want to format. From the menu, select Format > Conditional Formatting.
In the left dropdown, change "Cell Value is" to "Formula Is".
In the formula box type =MOD(ROW(),2)=0.
Click the "Format..." button. Click the Patterns tab and pick a color.

How can I show the complete path and filename in the Excel title bar?
Run a macro that has this line:
ActiveWorkbook.Windows(1).Caption = ActiveWorkbook.FullName

Top     
Page 1 Page 2 Next >>