microsoft-excel-2010 interview questions

Top microsoft-excel-2010 frequently asked interview questions

Pasting text into Excel without splitting on spaces (or other delimiter)

I recently used the Text to Columns feature in Excel to split a column of data into multiple columns based on the space character. Now when I paste text into Excel it automatically splits it into multiple columns without using the Text to Columns feature. Is there a way to revert this functionality back to normal?

Source: (StackOverflow)

Excel 2010 on multi monitor screen

With office 2010 I would like to open two excel document on different monitor. The only way I achieved to do this was to widden the Excel main window over the both screen which is not very convenient because:

  • the mouse operation is not so easy.
  • it hides what is between the two excel sheet.

Excel 2007 used to perform this very easily with a multi window system. Is there a tricks for 2010?

Source: (StackOverflow)

How to add cell padding in Excel 2010?

Is there a way to add padding to cells in Excel 2010?

The spreadsheet I'm using has cells in one column with varying row heights. I don't want to center the text, I just want it padded slightly from the left side.

Source: (StackOverflow)

Median in Pivot Table in Excel 2010?

Astonishing that this functionality is not present in such an ancient application

Is there a known workaround?

I'm on about the part where you can change the aggregation type for a value field: It has sum, min, max, avg etc but not median

Source: (StackOverflow)

How to control CSV import into Excel 2010

When I open CSV files in Excel 2010 I get entire rows in cells, not distributed according comma separation.

How to control this?

Source: (StackOverflow)

Why does Excel treat long numeric strings as scientific notation even after changing cell format to text

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):

  1. Take a long number like 1240800388917 and paste it in a cell in a new worksheet.
  2. Excel's default cell format is general, so the string is presented in scientific notation as 1.2408E+12
  3. 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:

  1. Format an empty cell as text. Right click on the cell, select Format Cells, set the format to Text
  2. 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)

How to make Excel's "Auto Fit Row Height" feature actually auto fit the row height?

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%.)

Auto Fit fails for the first row, succeeds for the second

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)

Excel - can I set a cell to equal a certain value, no matter what is typed in it?

This is to play a joke on someone...not sure if it's possible to do what I want. We are circulating an Excel file among friends to select players for a golf tournament. I would like to set a cell (call it A1) to to show a certain name (call it Joe) no matter what someone types into it.

A1 should be blank until someone tries to type a name into it. When someone types a name - any name - it automatically changes to Joe after they hit enter.

Conditional formatting doesn't work as it appears it only addresses look of the value in cell A1.

A formula doesn't work because they would see something in the cell.

Auto-correct - while it would have the exact effect I'm looking for - doesn't work b/c that's stored on my computer and wouldn't transfer with the file.

Any ideas?

Source: (StackOverflow)

Easiest way to open CSV with commas in Excel

CSV files are automatically associated with Excel but when I open them, all the rows are basically in the first column, like this:

enter image description here

It's probably because when Excel thinks "comma-separated values", it actually searches for some other delimiter (I think it's semicolon but it's not important).

Now when I have already opened this file in Excel, is there a button or something to tell it "reopen this file and use comma as a delimiter"?

I know I can import the data into a new worksheet etc. but I'm asking specifically for a help with situation where I already have a CSV file with commas in it and I want to open it in Excel without creating new workbook or transforming the original file.

Source: (StackOverflow)

What does the [@[bar foo]] operator do in Excel, in particular the at sign?

I've seen someone construct what seemed like cell references in Excel that looked like

=[@[column title]]*someothercell

What is this feature called, are the rectangular brackets and the at sign independent or do they belong together? How does it work precisely?

I've googled around for a while and tried by hand in Excel, but couldn't get it to work.

Source: (StackOverflow)

How do I export a chart as a png in Excel 2010?

Given a chart (normal column chart of whatever), how do I export that chart and only that chart as a PNG?

Source: (StackOverflow)

How to copy conditional formating to another Excel Worksheet

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.


Source: (StackOverflow)

How to remove the print preview lines in Excel 2010?

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)

Ignore multiple "number stored as text" errors at once

I have a spreadsheet where a certain column has a lot of data which generates "number stored as text" errors. I want the numbers to be stored as text in this column for certain formatting purposes. Is there a way for me to quickly dismiss all these errors at once, or tell Excel to ignore this error for the entire row as a rule, without entirely disabling the error for the whole sheet or program?

Source: (StackOverflow)

How can I create a properly aligned curly brace that spans multiple rows or columns in Excel?

I would like to group a couple of columns or rows together and add a note to them. When I try that with normal letter-resizing, it gets ugly, misaligned to the rows, too bold and the centered text next to it aligns oddly.

Ideally, I would have the curly brace character ({) automatically resize with the cell, or with the grouped (how?) rows or columns.

The curly brace misalignes with text and grouped rows

Source: (StackOverflow)