microsoft-excel interview questions
Top microsoft-excel frequently asked interview questions
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'm trying to figure out how to "group by" values in an Excel spreadsheet.
Given the following example (this is an inventory sheet I use at my restaurant:)
At the far right of the spreadsheet there is a column named Cost of Goods Sold.
I would like to be able to come up with a total COGS figure for each Category.
Is this possible in excel? Moreover, I'd like to do it in a separate worksheet so that the first worksheet (what is pictured) isn't affected.
Any ideas?
Thanks!
Source: (StackOverflow)
I have to do some manual reordering or rows in Excel and cut/paste is too hard to use. I'm looking for something that would enable me to use drag'n'drop or to add some buttons to move-up/down move-top/bottom.
Source: (StackOverflow)
Excel only places quotes around certain fields, how do I force excel to save a CSV file with quotes around every column?
Source: (StackOverflow)
I'd like to sum all the values in a column starting from a certain minimum value, but I don't want to give an upper bound because any value in the table will be appropriate for summing (minus the header of course) and I'd like to do it the "right" way instead of summing A2:A65535.
Is this possible?
Source: (StackOverflow)
If I have a column with values, and I want to find out what distinct values are in there (not how many - but the actual distinct values), how can I do that?
In SQL Server I would do something like
SELECT Distinct(MyColumn) FROM MyTable
Source: (StackOverflow)
I want to write multi-lines in one MS Excel cell.
But whenever I press the Enter key, the cell editing ends and the cursor moves to next cell. How can I avoid this?
Source: (StackOverflow)
What does the caret operand in Excel do to a range when it is followed by some other numbers in brackets? For example lets say we have the following table:
A B C
1 1.5 0 0
2 -0.5 0 0
3 4 0 0
4 5 0 0
Then, what is the following formula doing?
= A1:A4^{1,2,3}
Source: (StackOverflow)
Somehow or other I have managed to switch on the print margin lines in Excel 2010. I cannot seem to turn them off. I went to Page Layout->Page Setup->Print Area->Clear Print Area but the lines remain on my screen. Any ideas to get rid of them?
Source: (StackOverflow)
I'm trying to process some data in Excel. The data includes numeric account numbers and other long numeric strings (like cell phone MEIDs). I am not doing math operations on these strings, so I want Excel to treat them as plain text.
Here is what is making me nuts (this is Excel 2010):
- Take a long number like 1240800388917 and paste it in a cell in a new worksheet.
- Excel's default cell format is general, so the string is presented in scientific notation as 1.2408E+12
- Right click on the cell, select Format Cells, set the format to Text
The cell is still displayed in scientific notation, even though the format has been set to text.
Now, if I do the steps in a different order:
- Format an empty cell as text. Right click on the cell, select Format Cells, set the format to Text
- Take a long number like 1240800388917 and paste it in to the text formatted cell
Now, the cell is displayed as a string and not in scientific notation.
The results remaining in scientific notation even though the cell is formatted as text just seems broken to me. I've seen suggested work-arounds like: use CSV import and set the format to text, add a space character to the beginning of each numeric string, and others.
Is there a simple good work around to easily keep these strings formatted as text?
Why on earth does Excel do this?
Related SU questions I found:
How can you make Excel 2007 stop formatting large numbers as scientific notation? and
Is this Excel behaviour with a large hex number expected?
Source: (StackOverflow)
Though I try to avoid it, I occasionally have to open a CSV file in Excel. When I do, it formats columns containing numbers, which makes them useless for my purposes. As far as I can tell, the only way to prevent this from happening on import is to rename the file so the extension isn't .csv and use the import wizard to specify the format of each column individually. For files with 50-60 columns, this is impractical.
Since every answer for this oft-asked question on the internet suggests either some means of converting the formatted numbers back once the file is open (which won't work for me - I want to solve the general problem, not a few specific cases) or manually selecting the format type of each column (which I don't want to do), I'm looking for a way to set a global preference or style such that all columns of all CSV files opened are always formatted as text. I know about "armoring" the numbers with quotes, too, but the files I get don't come like that and I was hoping to avoid having to pre-process the files so Excel doesn't screw them up.
Is there a way to do specifically this: Always format all columns in opened CSV files as text, without manually selecting each column every time during import?
I'm using Excel 2003, but I'll take answers for 2007 if that's what you know.
Source: (StackOverflow)
Microsoft Excel's undo / redo feature behaves unlike any other program I know. The undo stack seems to be global across all open files, so that undoing sometimes switches to another file and undoes something you didn't want to undo. And if an edit you want to undo was before an edit in another file, you have no choice but to undo the other file.
I am not the first to complain about this - see "Excel's undo madness", about halfway down.
Besides "edit only one file at a time", is there a way to make Excel's Undo apply to the current file only?
I'm using Excel 2003 if it makes a difference.
Source: (StackOverflow)
When you create a multi-line string in an excel cell (by using Alt-Enter), if you copy that cell to a text editor, excel will automatically add double quotes (") around the full string, ie:
Cell1 | Simple String 1
Cell2 | First line of a
| Multiline string
| with 3 lines
Cell3 | Another simple line 2
When you copy just the column with values to a text editor, we get:
Simple String 1
"First line of a
Multiline string
with 3 lines"
Another simple line 2
How can I tell excel not to add the quote around multi-line text when copying from excel?
Edit: Text Editors that I've tried that display this behaviour:
- MS Word
- Wordpad
- Notepad
- Notepad++
- SQL Server Studio
If you have a suggestion on using a particular editor (or one of the above) please tell me which one & how to use it...
Source: (StackOverflow)
This question already has an answer here:
I have copied some data into Excel 2010. I have found that some of the cells need to be widened to allow the data to fit. How do I automatically adjust the height of the cells to fit the content?
Source: (StackOverflow)
For every generation of Excel I can remember (including 2010, which I'm using now), Excel's "Auto Size Row" features sometimes fails to actually auto size a row when the cell contains wrapped text. When it works properly, all the text is revealed and there is no additional space below the last line of text. When it fails, it adds extra space below the text. To make matters worse, what you see is not always what you get, i.e., text that appeared okay on screen gets cut off when it's printed. You also get different sizing depending on whether you are zoomed in/out or at actual size.
Simple test case:
Why is there a one-line gap after the text in cell A1 but not in A2?
(To reproduce: set width of column A to 17.14 (125 pixels), text wrap on, and cell alignment top.)
(I double-checked that I applied Auto Fit Row Height to both rows. Zoom level is 100%.)
Is there any known remedy for this without resorting to manually adjusting the row heights (which is not practical for more than a handful of rows)?
Source: (StackOverflow)