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)

87
votes
5answers
44k 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 ...
60
votes
2answers
3k views

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 ...
49
votes
11answers
63k 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 | ...
35
votes
3answers
135k views
34
votes
8answers
150k views

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?
30
votes
9answers
3k views

Limit Excel CPU Usage

Is there a way to limit how much CPU usage excel has access to when running? I have a VBA script that calculates a bunch of giant array formulas. The entire calculation takes maybe 20 minutes using ...
28
votes
4answers
82k 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? ...
25
votes
11answers
141k views

Excel Auto-Fill a Series 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 ...
25
votes
17answers
114k 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, ...
25
votes
6answers
139k 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 ...
25
votes
3answers
90k 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?
21
votes
7answers
11k 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 ...
20
votes
6answers
117k 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 ...
18
votes
3answers
29k views

Open Multiple Instances of Excel without PERSONAL.xlsb Lock Message

Whenever I open more than I instance of Excel, I get the following message: PERSONAL.xlsb is locked for editing Q: How can I disable or hide this message? Alternatively, if someone could ...
17
votes
3answers
82k 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? ...
16
votes
16answers
132k 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?
15
votes
2answers
42k 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 ...
15
votes
5answers
58k 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. - ...
15
votes
2answers
95k 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 ...
14
votes
1answer
15k 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 ...
13
votes
15answers
108k 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
8answers
45k 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 ...
13
votes
3answers
95k views

2 Monitors 2 Excel Sheets? [duplicate]

I have 2 Monitors and want to observe on one monitor 1st sheet and on the other monitor 2nd sheet but no matter how many excel files I open they are all on one monitor. Can I split them to two ...
13
votes
2answers
3k 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 ...
12
votes
14answers
77k views

Copy Excel worksheet and maintain relative cell reference in formulas

Another copy problem in Excel: How can I copy a Worksheet from WorkbookA.xlsx into WorkbookB.xlsx without the copied Worksheet still referencing WorkbookA.xlsx e.g. the formula =B!23 becomes ...
12
votes
1answer
42k 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?
12
votes
2answers
13k views

Keyboard shortcut to switch between sheets in Excel

Does Excel 2007 have a keyboard shortcut that switchs between worksheets?
11
votes
8answers
257k views

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

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?
11
votes
11answers
57k 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 ...
11
votes
3answers
65k views

Color Cell Based On Text Value

An Excel column contains a text value representing the category of that row. Is there a way to format all cells having a distinct value a unique color without manually creating a conditional format ...
11
votes
4answers
29k views

Excel shortcut to go back to previous cell (last position)

There is a cool shortcut to go to a precedent cell: ctrl+[ This takes you to the source of your data in a cell that is linked from another worksheet for example. I'm trying to find a shortcut that ...
11
votes
1answer
4k 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 ...
10
votes
8answers
37k 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 ...
10
votes
5answers
36k 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 ...
9
votes
6answers
54k 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 ...
9
votes
3answers
30k views

Fixing the first row as sortable title in Excel 2007

I know this is super easy, I just couldn't find it. I'd like the first row in an excel document to be the "title" field. I want that: Whenever I scroll down, this row will remain fixed I can sort ...
9
votes
3answers
50k 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 ...
9
votes
2answers
32k 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 ...
9
votes
4answers
6k views

Is there a way to scroll, by pixel, instead of by cell, in either Excel or OpenOffice Calc?

We have lots of spreadsheets that have lists of numbered sound files for an educational app. We use spreadsheet features such as "fill down" for the numbering, and we often use formulas to check ...
8
votes
2answers
73k 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.
8
votes
6answers
22k 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
9answers
61k views

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 ...
8
votes
3answers
25k 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 ...
8
votes
1answer
24k views

In Excel how do I plot date and time with an occurance?

Aloha, I need to plot (over a period of day measurements) date of occurrence and time. An example is "we are tracking Billy's occurrences of aggression, date and time of the event". How can I show ...
8
votes
2answers
10k 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 ...
8
votes
1answer
1k 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 ...
7
votes
3answers
4k 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
3answers
33k views

How to format a Microsoft Excel 2007 cell in hexadecimal?

I would like a format a cell in Microsoft Excel 2007 in hexadecimal but am not finding how to do this. Is there a built-in way to change the base from 10 to 16 for a cell?
7
votes
3answers
24k 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 ...
7
votes
2answers
19k 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 = ...