Microsoft Office Excel is a spreadsheet application written and distributed by Microsoft. Use this tag for problems specific to Excel 2007 for Windows.

learn more… | top users | synonyms (1)

44
votes
3answers
23k views

Is it possible to paste CSV formatted data into Excel 2007?

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 ...
31
votes
9answers
32k views

Is it possible to “unpivot” or “reverse pivot” in Excel?

I have data that looks like this: Id | Loc1 | Loc2 | Loc3 | Loc4 ------------------------------ 1 | NY | CA | TX | IL 2 | WA | OR | NH | RI And I want to convert it to this: Id | ...
18
votes
4answers
37k views

Selecting whole column except first X (header) cells in Excel

I know I can select all cells in a particular column by clicking on column header descriptor (ie. A or AB). But is it possible to then exclude a few cells out of it, like my data table headings? ...
16
votes
5answers
4k views

How can I use custom colours in the Microsoft VBA editor?

I'm working on a few projects for work using Excel and VBA, so I have no choice but to use the Microsoft Visual Basic editor. I normally wouldn't mind, but I can't figure out how to set a custom ...
16
votes
15answers
38k views

How to make cells perfect squares in Excel?

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, ...
14
votes
5answers
64k views

Excel: Find Items in one column that are not in another column

I have two columns in excel, and I want to find (preferably highlight) the items that are in column B, but not in column A. What's the quickest way to do this?
13
votes
14answers
79k views

How to easily remove hyperlinks from multiple cells?

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 ...
13
votes
3answers
23k views

Open Multiple Instances of Excel without PERSONAL.xlsb Lock Message

How does one disable or hide the "PERSONAL.xlsb is locked for editing" message when opening more than one instance of Excel 2007? I have at least two instances of Excel open at all times, since I ...
13
votes
5answers
57k views

Need two excel windows side by side, on different monitors, in the same instance

I have to do work between two spreadsheets and I need to view them side by side. I can't just open the workbooks in separate instances because I need to make use of features like paste formatting, and ...
12
votes
3answers
49k views
12
votes
7answers
28k views

Table Formatting in Excel 2007: How do I remove it?

I've used the new Table Formatting option in Excel 2007. Now I can't remove it. I've dragged the little blue square up to the last cell on the top left, but it just won't go any further. In fact it ...
11
votes
16answers
68k views

How to get the current column name in Excel?

What is the function to get the current line number and the current column name for a cell in Excel?
11
votes
2answers
52k views

Run an SQL Query With a Parameter from Excel 2007

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 ...
9
votes
8answers
43k views

Excel Auto-Fill Without Mouse (Keyboard Only)

So when I want to extend a series or something along those lines, I can highlight a group of cells, then click on the bottom right corner of the highlighting box and drag down. Excel will then ...
9
votes
7answers
13k views

How to remove white space from a number

I have a column of number with white spaces in them. Like this: "32 445 423". How do I get rid of all the white space to get excel to treat it as numbers rather than strings? EDIT: Let me fill in ...
9
votes
3answers
17k views

Turn off scientific notation in Excel

I would like to prevent Excel behavior that any large number I enter is stored as a number, then abbreviated to scientific notation. Is it possible to tell Excel 2007 to stop doing this?
9
votes
2answers
5k views

Keyboard shortcut to switch between sheets in Excel

Does Excel 2007 have a keyboard shortcut that switchs between worksheets?
9
votes
2answers
35k views

Excel keyboard shortcut to copy/fill down for all cells with non-blank adjacent cells?

I'm looking for a keyboard shortcut that will duplicate the functionality available by double-clicking the "handle" in the bottom-right corner of a cell. Is there something that can do this, without ...
9
votes
1answer
2k views

Setting a default value for a dynamic cascading validation list in Excel 2010

I have finally figured out how to build a dynamic cascading set of lists in Excel using Data Validation and the INDIRECT function. However, I'm running into a problem. I would like to set up my ...
8
votes
3answers
8k views

Why does Excel 2007 changes the text “HSA”to “HAS”?

Working on Excel 2007 I am trying to populate a cell with the text HSA. However, when pressing enter, the text changes automatically to HAS. I assume there is a kind of auto spelling correction here, ...
8
votes
6answers
32k views

How to turn hundreds of text URLs in Excel into clickable hyperlinks?

How do I turn a column of urls into hyperlinks, so that users can click on the url in a cell and have it open in a browser? I pasted 100 urls and each went into its own cell. When I double-click in ...
8
votes
3answers
24k views

What the heck have I done to my Excel sheet (and how to undo it)?

I have a lot of Excel sheets for all sorts of things - but none has ever acted up like this one here: This used to be a totally normal Excel 2007 sheet which I was able to resize, maximize etc. - ...
8
votes
1answer
12k views

Adding Weeks to Current Date in Excel 2007

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?
8
votes
10answers
29k views

Multi-row headers and excel 2007 sort/filtering

I have a spreadsheet which uses 2 rows for header information. Filtering implicitly is assuming only 1 row of header information. Sorting has a header row checkbox but that only toggles treating the ...
8
votes
1answer
9k views

How can you make Excel 2007 stop formatting large numbers as scientific notation?

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 ...
8
votes
2answers
667 views

How can I get Excel's undo/redo functionality to work in a more traditional *per window* manner (and not for the entire instance of Excel)?

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 ...
8
votes
1answer
15k views

Hyperlink font in Excel always changes to the default font face/size

For cells containing hyperlinks the font changes automatically into the default one (even though I have selected all cells and applied a different font). How can you configure Excel to use the same ...
7
votes
2answers
2k views

Excel elapsed time format

In Excel, given a number representing some amount of time (e.g. 25.5 hours), is there a way to format it so that it shows as 1d 1h 30m? Bounty question : Is there a way to add months to this format? ...
7
votes
2answers
11k views

Excel 2007 - Custom Y-axis values

I am making a basic column chart with a few columns. The only catch is instead of showing numbers as the grid marks, I want to show words like "Very Low" and "Medium". For example, I have Data1 = ...
7
votes
3answers
39k views

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? ...
7
votes
3answers
15k views

How to stop Excel from Auto-formatting and making it work like a number crunching program

I have a real problem with Excel in csv files where it is formatting numbers in certain ways and I cannot seem to get Excel to display the data right no matter what I do. Imagine I have a colum ...
7
votes
3answers
1k views

Splitting Excel 2007 in task bar

I wish to view 2 Excel 2007 files in 2 different Excel windows, although there are 2 "Excels" in the task bar, they both points to the same app. how can I make it split (like word)?
7
votes
1answer
988 views

Where can I find guidelines for how to use Excel's “Data and Model” cell styles (specifically “Linked Cell”)?

(I originally asked for "well-known" guidelines, but got no answers. In light of that I think it's fine is anyone just wants to answer with their own guidelines or a link to some they like.) I have ...
6
votes
2answers
26k views

Excel: Find a subset of numbers that add to a given total?

I have a column of numbers (lets say it's A1:A100) and I need to find a subset of them that sum to a certain total.
6
votes
7answers
101k views

How can I get a cell in Excel to automatically adjust its height to fit the wrapped text contents?

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?
6
votes
2answers
16k views

How can I prevent the privacy warning in Excel?

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 ...
6
votes
3answers
12k views

Excel chart on one column of date/times

I have a list of date/timestamps, and I would like to plot these to a chart in Excel. What I'm looking for is a chart that shows "events per hour" or something similar. Can this be done easily if I ...
6
votes
2answers
6k views

Cannot connect to SQL Server from Excel 2007

I am trying to connect to Microsoft SQL Server 2008 R2 from Excel 2007. When it tries to connect I get the error: [DBNETLIB][ConnectionOpen (Invalid Instance()).]Invalid connection. Steps to ...
6
votes
4answers
34k views

How to change x-axis min/max of Column chart in Excel?

Here i have a column chart of binomial distribution, showing how many times you can expect to roll a six in 235 dice rolls: Note: You could also call it a binomial mass distribution for p=1/6, ...
6
votes
4answers
39k views

How to combine values from multiple rows into a single row in Excel?

I have a data dump in Excel that consists of annual customer data for two different values. The data was provided with a separate row for value for every year and customer. I.e. it looks like this: ...
6
votes
3answers
20k views

Double Clicking an Excel File Will Open Excel but not the Spreadsheet itself

The OS is Windows XP and the program is Microsoft Office Standard 2007 (Excel). This computer recently upgraded from Office 2003 to Office 2007 (Yeah, I know don't shoot me). Upon clicking any .xls ...
6
votes
2answers
1k views

Two large, linked Excel files take 30 minutes to save, except in VMWare environment

I support some tax consultants who love to use Excel when they should probably be using Access. Anyway, they have created two Excel files, A and B. File B has cells linked to file A. File A is 27 ...
6
votes
2answers
502 views

How to tell Excel not to hide selection when it becomes inactive?

Excel has an annoying habit of making the current selection invisible when it becomes inactive - including the "focused cell" rectangle. When I try to process a spreadsheet in a certain way I may put ...
5
votes
2answers
14k views

Setting up a column of “enum”-fields in Excel

I'd like to setup a column in excel with several fixed possible values (like 'in-progress', 'done', 'canceled'). I'd like to set it up so the only possible values in these column are from the fixed ...
5
votes
3answers
10k views

Find missing entries from a list of sequential numbers in a column?

I have an Excel sheet containing the results of 66000 test cases. Or, at least it should... Now, because I was running the tests asynchronously and kept stopping and starting I had the foresight to ...
5
votes
3answers
7k views

Excel: Custom number formatting

I don't know much about the rules on custom number formatting in Excel, and I need to learn more. When I open the related window (Format Cells > Numbers > Custom), there are some predefined ...
5
votes
4answers
11k views

Excel Cell Length Limit

Excel is refusing to open some of the files I programmaticaly create. I think what is happening is that it pukes because a particular column has very long strings (up to 1850 chars), but for the ...
5
votes
3answers
11k views

Text cell is displayed with hash characters

In Excel 2007, I see that the content of some cells are displayed as hash characters (###). These cells are formatted as text and their contents are displayed like this if the number of characters are ...
5
votes
4answers
8k views

How can I force Excel 2007 on Windows 7 to always open another instance of the application?

I want to be able to compare to large spreadsheets side by side... Since you can not natively expand to all monitors I would like to have the two documents open in two instances. These files are ...
5
votes
4answers
21k views

How can I obtain the year quarter from a date?

In Excel, how can I obtain the year quarter from a date, using a worksheet formula? I have a column with dates spanning multiple years. Jan-01 to Mar-31 = Q1 Apr-01 to June-30 = Q2 etc... Bonus ...