Microsoft Excel is a commercial spreadsheet application written and distributed by Microsoft for Microsoft Windows and Mac OS X. If your question is about programming Excel in VBA then also tag it excel-vba. If it is about an Excel formula or worksheet function, then also tag it worksheet-function.

learn more… | top users | synonyms (2)

1
vote
1answer
138 views

How can I make Excel NOT ask about saving to a specific file format?

We have a VBScript called from desktops that writes start / end times to a tab delimited file (with an .xls extension) as a timelog. When someone opens this file in Excel, and then wants to close it, ...
1
vote
1answer
952 views

Where is a Web Query stored in an excel file?

I have a Web Query looking at a site for data, and it gets the table selection wrong. Viewing source of the linked to web page I am able to identify the correct table. In an Excel Web Query you are ...
4
votes
1answer
4k views

Reference a range of cells on all sheets in Excel

In Excel, =$J:$J references a column, and ='Sheet2'!$J:$J will reference that column on Sheet2 explicitly. Is there a way to reference column J and all sheets without manually typing all the sheet ...
-1
votes
1answer
484 views

Excel 2007 conditional formatting formula problem

I have the following problem. I have a sheet where the current month should be highlighted. For this I used the formula for conditional formatting. The formula for the first three months is like this: ...
2
votes
2answers
5k views

How find & replace using wildcards with regex substitution?

Is there a way to use regex substitution with Excel find & replace (in the GUI)? Using a JavaScript example, I want to do something like this: "John Smith".replace(/(.*)\s(\.*)/, "$2, $1") // ...
2
votes
1answer
374 views

Add 0's to values in column in Excel? [duplicate]

Possible Duplicate: How do you add zero before values in excel document? I am trying to edit product codes in Excel so that each product code is exactly 8 digits in length. Currently the ...
1
vote
1answer
965 views

Querying big databases with Excel

Is it possible to use Excel's pivot table as a kind of query builder against a database, without copying the entire database into Excel? I know that with the External Data Sources feature, you can ...
0
votes
0answers
936 views

Recover data from corrupt excel file

a friend has a corrupt excel file. When you attempt to open it in excel it says "File format is not valid". None of the tricks here work. The file is Excel 2003 format. She sometimes edits it on ...
10
votes
4answers
25k 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 ...
1
vote
2answers
922 views

Linking a cell to a tab within an Excel workbook

I'm using Microsoft Excel 2011. One of my workbooks contains a tab. I want that tab to open when I click a link. The link should be in a cell in the same workbook. How can I create such a link?
1
vote
1answer
263 views

Show duplicate cell values in an Excel document?

I have an Excel document with two columns of data. I'd like to quickly see what values in column A exist in column B. For example, imagine column A had the list of US President last names and column ...
0
votes
1answer
5k views

Install .xla Excel add-on for all users?

When you add a new .XLA add-on using Excel it only adds it for the current user. Is there a possibility to add it for all the users of the computer even with editing registry?
11
votes
1answer
50k views

Create a pie chart from distinct values in one column by grouping data in Excel

I have an Excel document containing information from a survey. I want to create a pie chart over location (countries). How can I make Excel group all the distinct values together and then display ...
0
votes
2answers
2k views

How can I condense or summarize a list in Excel?

I have a excel file with entries that appear multiple times. Example: A A A B B C C How can I change this to a form that looks like this: A 3 B 2 C 2
0
votes
1answer
240 views

Using QuickText in Text Box inside Excel

I'm trying to insert a Quick Text date (the date always shows the current date or the date the document is printed) inside a Text Box in Excel 2010 and am not finding any Quick Text options inside ...
2
votes
1answer
3k views

Excel count coloured cells

If i have 500 cells filled with red, 500 filled with green and 50 filled with yellow, is it possible to do a countif sorta formula where i will have a cell which will count the number of red cells, ...
0
votes
1answer
135 views

excel sums field overlap other cells

I don't know if this is a trivial problem but it is driving me nuts. I have two columns like this: | A |B| | |5| | |3| |100|2| | |5| | |2| | |3| In column A all cells are merged and I use ...
0
votes
2answers
166 views

Excel Multiple Conditions Summing

I have Column A to A1600. In that column I have two project names "CP" and "GFI", in column B I have Project Phase 1-8, in column C I have the hours worked in each phase. The projects intertwine so ...
4
votes
6answers
1k views

Transform Fixed Width to CSV?

I have a whole bunch of data files with different content but identical layout. I need to convert them to CSV before importing them into a CRM application. I usually open the tab-delimited file in ...
1
vote
1answer
59 views

Trying to build two graphs/charts from a two column spreadsheet in excel 2007

I have a two column spreadsheet like so that indicates the the usage of a website. A snap shot is taking every 15 minutes and shows what pages of the website are currently displayed: Time ...
0
votes
2answers
362 views

Regex in excel: Fixing dates, from Apr 1/11 to 1-Apr-11

I have a fairly large spreadsheet and the dates column hasn't been locked down with an input mask/format and users have been using an incorrect dating scheme. Users are inputting dates as Apr 1/11 ...
1
vote
1answer
355 views

How to calculate the total sum of hours with automatic filter

I have an Excel sheet where I sum hours to get the total amount of my work. It works without problems, but if I add an automatic filter I'd like the calculations to be done only on the portion of ...
4
votes
1answer
21k views

Conditional formatting of TRUE / FALSE values in an Excel 2010 range

I have a range containing formulas that evaluate to TRUE or FALSE. How would one apply conditional formatting to this range, so that TRUE cells are Green, and FALSE cells are RED?
1
vote
3answers
17k views

Adding a cumulative sum column to a pivot table

I have a pivot table where I am representing a series of data across a number of categories as follows: Sum of amount Area Status Priority Project Department 1 ...
1
vote
1answer
6k views

Excel: Charting date, time elapsed, and another value

I'm trying to chart growth in our nightly backups. The three values I'm using are the date of the backup, the backup job size in bytes, and the time the job took to completely in hh:mm:ss. Using ...
0
votes
2answers
55 views

How to open a certain document at login?

I have a file: mydoc.xlsx (Excel spreadsheet). Is there some sort of terminal command or other setting in Excel or otherwise that makes this particular document open automatically when I login?
1
vote
3answers
2k views

Getting rid of whitespace in upper part of excel cell

I have pasted text into a cell of an excel spreadsheet but there is lots of white-space above this text making the cell almost twice the size it needs to be. Clicking on backspace does nothing to ...
3
votes
2answers
546 views

How do I get the newline character to be processed so that lines wrap?

I have set column to wrap text but if I press Enter to create a new line I don't get a new line inside the cell. It's like it does not treat the newline at all. What else do I need to set to make ...
2
votes
1answer
109 views

Formula error while counting unique entries in Excel

200 Pass ABC 201 Pass 202 fail 300 Pass DEF 201 Pass 202 Fail I have a table which looks like the one shown above. The cells on the left ...
1
vote
1answer
1k views

Excel database criteria, use string as input instead of multiple cells

In Excel, I got a small database. I want to use DMAX() to find a value in it. +--------+---------+---------+---------+ | | Cost | | | ...
1
vote
2answers
406 views

Advanced spreadsheet auto-formatting

Is it possible to autoformat spreadsheet like this IF A1 = C1 then A1:E1 cells background becomes light gray I use OO Calc, but it's ok if you know how to do it in MS Excel or Google spreadsheet
1
vote
2answers
896 views

Why is there a date in my Excel time format, and how do I get rid of it?

I am using Excel at work and I have a list of users' online times. I get a total say 180:30:00, which is 180hrs 30 mins and 0 seconds. I need to keep this cell as the time, but I want to work out ...
3
votes
3answers
8k views

Excel: Why doesn't “File->Save As” remember the last directory between sessions?

I have already read and implemented the answer in the following post. Excel: why doesn't "File-> Save As" remember the last directory that you saved a file in However I did not have ...
0
votes
1answer
590 views

Setup Excel 2007 To Open New Sheets In New Window

If my users have Excel 2007 open and double click on another Excel document in explorer (or goto file open and open a new one), this document is opened within the same window. Can I configure every ...
3
votes
3answers
23k views

How to execute a stored SQL procedure in Microsoft Excel?

How can I execute a stored procedure in Microsoft Excel and get all the data it returns?
0
votes
1answer
56 views

Can I make a data cell link to a sheet in Excel?

On my xls, I have a columm that represents the index of 15 diferent sheets. I have a columm with 1,2,3,4...15 and 1,2,3,4....15 named sheets. I want to link them as data hyperlinks. How can I make ...
2
votes
1answer
642 views

Is there a keyboard shortcut to circularly switch sheets in Excel?

In this question, he was asking about keyboard shortcut to switch tabs. But this shortcut moves the sheet backward or forward, which means that pressing Ctrl+PageUp on the last sheet doesn't move the ...
2
votes
1answer
5k views

Two Way Link between excel workbooks

Is it possible in Excel to link two cells together (in different workbooks) so that a change in either is reflected in the other cell? The one-way linking whereby one cell is a view of the value in ...
5
votes
2answers
8k views

How do I force a user to use a specific format when entering time data?

I need to ensure the users of a particular spreadsheet are entering the time data in military time. I'm finding some folks are omitting the colon, and it's causing problems. Is there a way to force ...
0
votes
1answer
1k views

How do I clear cells with list data validation based on another column's value?

I have a test planning worksheet with the following columns: Test Name, Run?, Tester, Tester Location, Environment, etc. The "Run?" column has data validation on for valid values of Yes and No. The ...
4
votes
2answers
403 views

Entering only Digits in Excel DataSheet Cell

I want users to enter only 1 or 0 in a cell column in Excel 2007. Users should not be able to enter anything else than 1 or 0. How can I do this in Excel?
3
votes
1answer
23k views

How to automatically delete rows in Excel 2007 based on cell value?

I'm looking to automatically delete rows in Excel 2007 based on whether or not there is a cell with a specific value in that row. I've Googled around, but I've only found old information on VBA ...
3
votes
3answers
1k views

Is there a way to set the name box of a cell programmatically in Excel

I would like to programmatically set the name box of a cell in a worksheet I'm working on, either through a formula or through a macro. The end point is that I don't want to set it by hand. In my ...
40
votes
9answers
46k views

How to copy multi-line text from Excel without quotes?

When you create a multi-line string in an excel cell (by using Alt-Enter), if you copy that cell to a text editor, excel will automatically add double quotes (") around the full string, ie: Cell1 | ...
2
votes
1answer
1k views

Excel 2010 changes number precision automatically

I've run into an issue in Excel 2010 where precision in percentage type cells is changing. To reproduce: Create two cells in A1 and B1, both with Percentage format (2 decimals) and numbers with ...
4
votes
1answer
71 views

Keyboard shortcut in Excel to jump to next visible region/area

On an Excel spreadsheet, the visible columns based on my typical screen resolution range from A to O (15 columns). Assume that the cell pointer is in A1. Is there a shortcut to move the cell pointer ...
0
votes
1answer
330 views

Save As a new Excel file changes links on dependent file

I have two Excel files. One is linked to the other. If I take the link source .xls and save that as another file, then the dependent file's links are pointed automatically by Excel to that new ...
0
votes
1answer
706 views

Compute SLA between dates including weekends

I need help on an Excel formula that will compute the SLA between 2 dates. NETWORKDAYS() doesn't include weekends and I need something that will include Saturday and Sunday.
0
votes
3answers
20k views

Compare Two Columns (from two different worksheets) and show result of another column - excel

I have this scenario: Worksheet 1 with Column A only Worksheet 2 with Column A and B I have to compare Column A (Worksheet 1) and Column A (Worksheet 2). If a value in Column A (Worksheet 1) is ...
1
vote
1answer
277 views

conditional formatting of entire row from a cell's value in that particular row in Excel 2007

I have viewed the answer for a similar question. If I have several rules for a particular row, (e.g. if cell A2 is 1 then the row is red, if 2 then row is blue, if 3 then row is green, etc...). Having ...