microsoft-excel-2007 interview questions
Top microsoft-excel-2007 frequently asked interview questions
How can a given number of weeks be added to the current date in Excel 2007? For example, if I need to add 12 weeks to the current date, 12/6/2010, how can that be done?
Source: (StackOverflow)
I have Excel 2007. When I want to save a spreadsheet, I often see this message:
I've seen this with macro spreadsheets (using the .xlsm extension) and with non-macro sheets (using the .xlsx extension).
How can I prevent this warning every time I save my spreadsheet?
Source: (StackOverflow)
What is the function to get the current line number and the current column name for a cell in Excel?
Source: (StackOverflow)
I have a query in an Excel table. I followed this to do it.
But how can I add a parameter from a cell into my query?
I am looking for a way to do this:
select *
from dbo.Customers
where CustomerID = Cell.A2
The Cell.A2 does not work. But how can I add in something like it, so that the contents of a cell are used in my query?
Source: (StackOverflow)
I want to copy and paste CSV formatted text into Excel 2007.
Is there a way of doing this without saving it to a temporary file first?
I'm sure in previous versions of Excel there was a way to trigger the CSV import wizard by hand in this situation, but I don't know how do this in Excel 2007.
For reference, in OpenOffice this Just Works - pasting in 2+ lines of CSV text will trigger the CSV import wizard.
Source: (StackOverflow)
I have data exported from a tool as XLS. In this, one of the column has text which is a hyperlink to information in the tool. I can remove the hyperlink for each cell. But I want to remove the hyperlink for all the cells in the column in a easier way. Is there an option in Excel itself?
Source: (StackOverflow)
In Microsoft Excel,
I want to make the height of first three rows and first four columns (12 cells in the top left corner) such that these cells are squares. How can this be done?
Surprisingly, Excel says:
Row height: 15
Column width: 8.43
So, these are not on the same scale.
Making both of them 8.43 gives me this:
Now, what should I do?
Source: (StackOverflow)
I have set up conditional formatting in a set of cells of "Sheet2" on my workbook. I'd like to reuse this formatting on "Sheet1" (I've spent quite some time setting it up). Is there a way to do it?
I know that you can copy conditional formatting in a single sheet by selecting the new cells, but I don't recall it is possible to select cells across multiple sheets.
Thanks!
Source: (StackOverflow)
This has been driving me crazy lately. I'm not used to having having a program have a global undo/redo functionality (as in when I undo something, it will undo the last action in any currently open Excel window). I'm used to having each open window having its own undo/redo list. How can I get Excel 2007 to function more traditionally like that?
Source: (StackOverflow)
An Excel column contains a text value representing the category of that row.
Is there a way to format all cells having a distinct value a unique color without manually creating a conditional format for each value?
Example: if I had the categories bedroom, bedroom, bathroom, kitchen, living room, I would want all cells containing bedroom to be a particular color, bathroom a different color, etc.
Source: (StackOverflow)
Kind of the inverse of this question.
I frequently download CSVs from outside sources as part of my programming work. I like to examine their data in Excel because, let's face it, Excel is an ideal tool for this sort of thing.
Except for one behavior: Excel attempts to outwit the data in the CSV, and therefore, if something looks like a number (like, say, many ISBN/EAN codes), Excel will treat it as such. And if this number is really big (like pretty much all ISBN/EAN codes), Excel will turn the number into scientific notation. Finally, if the number isn't particularly big, with most of these data formats, that indicates that Excel has decided that my number shouldn't have leading zeroes.
So ... Is there a way to open a CSV (via double-click if possible, but I'm willing to compromise here) in such a way that I can have Excel 2007 treat all the columns as text?
Source: (StackOverflow)
This question already has an answer here:
I have a cell with wrapped text content in Excel and I want to format the cell so that its height will adjust to fit the content which can span over several lines. How can I achieve this behavior?
Source: (StackOverflow)
Another copy problem in Excel:
How can I copy a Worksheet from WorkbookA.xlsx into WorkbookB.xlsx without the copied Worksheet still referencing WorkbookA.xlsx e.g. the formula =B!23 becomes =[WorkbookA.xlsx]!B!23 when copied over.
I want to maintain "relative" cell references instead of "absolute" cell references (i shall invent this terminology in Excel world if it does not yet exists).
Another possible alternative that I cannot get it to work is the option to paste cell "values" only. Excel treats "values" as calculated values rather than the actual formulas in the cell. If I choose paste formula, it still gives absolute references.
More About Why I Need This:
I have a production xlsx in use for daily operations. We constantly need to make "upgrades" to this xlsx and so one person may create a copy and his changes there for a single sheet. Concurrently, another person may also be making changes to another sheet. Given that these sheets have no dependant cells on other sheets, like a summary report, it is desirable for us to just copy and merge the sheets back into the original xlsx. But the "absolute" referencing is giving a lot of trouble.
Source: (StackOverflow)
This question already has an answer here:
I have 2 Monitors and want to observe on one monitor 1st sheet and on the other monitor 2nd sheet but no matter how many excel files I open they are all on one monitor.
Can I split them to two screens?
I have Excel 2007 and working on 2003 file. Thanks a lot for your time!
Source: (StackOverflow)
Whenever I open more than I instance of Excel, I get the following message:
PERSONAL.xlsb is locked for editing
Q: How can I disable or hide this message?
Alternatively, if someone could suggest a way to improve Excel 2007's support for multiple monitors within a single instance, that would get around the problem as well
Note: I don't want to use "Ignore other applications that use Dynamic Data Exchange (DDE)" since it cripples other features that I use regularly.
Source: (StackOverflow)