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)

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)

Unable to get Excel to recognise date in column

I constantly have problems working with dates in Excel, I must be doing something wrong but I don't understand what.

I have a spreadsheet, exported from our exchange server, that contains a column with dates on. They have come out in US format even though I'm in the UK.

The column in question looks like this


In excel I have highlighted the column and selected 'format cells...', in there I have navigated to 'Date', selected 'United States' as the locale and chosen the matching date format from the list. I hit ok and go to sort the data by this column.

In the sort dialogue I choose this column, sort on Values but the order only gives me options for A to Z, not oldest to newest as I would expect.

This in turn sorts the date data by the first two digits.

I am aware I could re-format this data to ISO and then the A to Z sort would work but I shouldn't have too, I'm obviously missing something. What?

EDIT : I messed up the bounty but this should have gone to r0berts answer, his first suggestion of Text to Columns with no delimiter and choosing 'MDY' as the type of data works. Additionally, if you have a time (ie '04/21/2015 18:34:22'), you need to first need get rid of the time data. However after that the method suggested by r0berts works fine.

Source: (StackOverflow)

Grouping labels and concatenating their text values (like a pivot table)

I've got a spreadsheet with data like this:

Product   | Attribute
Product A | Cyan
Product B | Cyan
Product C | Cyan
Product A | Magenta
Product C | Magenta
Product B | Yellow
Product C | Yellow
Product A | Black
Product B | Black

What I'd like to do group everything by Column A and have Column B be a comma-delimited list of values that share Column A in common, like so:

Product   | Attribute
Product A | Cyan,Magenta,Black
Product B | Cyan,Yellow,Black
Product C | Cyan,Magenta,Yellow,Black

Unfortunately, Pivot Tables only know how to work with number values, and the furthest it goes towards this is counting the number of times Column A occurs.

I was able to pull this off ultimately by importing the data into a MySQL database and using MySQL's GROUP_CONCAT(Attribute) function in a query with a GROUP BY Product clause, but after banging my head on my desk repeatedly while attempting to figure out an Excel solution.

For future reference, is this possible in Excel without macros? Whether it is or not, how would one pull this off?

Source: (StackOverflow)

show Excel sheet tab in >=2 row

Some days I'm working with extremely large Excel files, they have to many sheet that can't be show in 1 sheet tab. (see image)

too many sheet in an Excel files

So, I'm wondering that whether or not we can show Excel sheet on more than 1 row. Many tries with Google don't help much then I post this question here!

Many tries but I still can't make Excel show sheet bar the way I want (>=2 rows), so I'm working with some VBA Scripts kopischke suggested.

FYI, right click on |< << >> >| button on the bottom-right corner show you a list of upto 15 sheets. and for more, click on View more in the list appeared.

Source: (StackOverflow)