microsoft-excel-2010 interview questions

Top microsoft-excel-2010 frequently asked interview questions

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

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)

Entering lots of dates without having to physically key the / (forward slash)

I enter a lot of columns of dates, for instance 05/12/1943. I would like it to appear as such, but I would only like to type 5121943. I am trying to conserve on my key strokes. I have been able to use the custom formula mm/dd/yyyy, and I only need to type 5/12/1943. I am repeating: I would like to not have to type the /.

I've tried to use a non-date formular to Concatenate text: =CONCATENATE (##,"/",##."/",####).

How can I achieve this?

Source: (StackOverflow)

Is there an Excel function to create a hash value?

I'm working with a number of data lists that are keyed by document name. The document names, while very descriptive, are quite cumbersome if I need to view them on (up to 256 bytes is a lot of real estate) and I'd love to be able to create a smaller keyfield that's readily reproducible in case I need to do a VLOOKUP from another workseet or workbook.

I'm thinking a hash from the title that'd be unique and reproducible for each title would be most appropriate. Is there a function available, or am I looking at developing my own algorithm?

Any thoughts or ideas on this or another strategy?

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)

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)

convert text string to date/time format

I have a column that is text formatted like 20110408_2041. How can I format it to date/time format?

Source: (StackOverflow)

Display Blank when Referencing Blank Cell in Excel 2010

I have an Excel 2010 workbook that contains a number of individual worksheets. The cells on one of the sheets are linked to individual cells on two other worksheets in the same workbook. I'm using a direct cell reference that essentially says that whatever value is entered into a particular cell on one sheet also populates cells on two other sheets. I used the (=) function with the cell reference to accomplish this.

The issue I'm running into is that, even when the primary cell is left blank, the cells that populate from that primary cell will display 0, rather than remaining blank themselves.

I want the subordinate cells to remain blank if the primary cell they're linked to is blank.

Source: (StackOverflow)

Excel pie chart: How to combine smaller values in a single "Other" slice?

I have this pie chart in Excel 2010 (based on a pivot table):

enter image description here

I'd like to combine all the values smaller than X% in a single "Other" slice. In previous versions of Excel, it was possible to use the "split series" option to do this. That option seems to be gone in the latest version. Is there still a way to do this?

Source: (StackOverflow)

How do I keep Conditional Formatting formulas and ranges from automatically changing?

I've found that Conditional Formatting formulas and ranges will automatically adjust when you copy, delete, or move data around in a spreadsheet. While this is a nice idea, it tends to break things for me in some rather weird ways.

To avoid this, I tried writing rules that applied to the entire spreadsheet and keyed off of column headers to highlight the data I wanted to check.

Example: =AND(A$1="Check This Column For Blanks),ISBLANK(A1)) applied to =$1:$1048576

However, even with the rule explicitly applied to the entire sheet, it was still automatically adjusting (and breaking in weird ways by doing so) as I worked in the sheet.

How can I avoid this?

Source: (StackOverflow)