A spreadsheet application by Microsoft. Use this tag along with [vba] if your question involves programming Excel in VBA, and with [worksheet-function] if it involves an Excel formula or worksheet function.

learn more… | top users | synonyms (2)

2
votes
2answers
6k views

Excel 2007: how to work out percentages of groups (top 10% of…)

I've recently read the following paragraph, and wondered: how you would organise the data (possibly Column A = country, Column B = salary, Column C = tax paid) but what formulas/calculations are ...
2
votes
3answers
6k views

Scientific notation in Excel

I need make Number Format like scientific notation, but without E nor e. Just classic like this: (In latex its 2.3\times10^3) Maybe Excel doesn't support this format. (I have on mind Number ...
10
votes
4answers
23k views

Where to find a VBA function's source in Excel

I have a workbook that uses a certain vba function which doesn't appear in the Insert | Insert Function... dialog box list of available functions. How do I find it's source (to see what it does)?
4
votes
2answers
2k views

How can I write “use this cell or, if blank, use this other cell” in Excel 2007?

I am trying to show the Days between NOW() and the dates (dd/mm/yy) in either Column B or Column C - depending which one is not blank. A B C 29/03/10 01/04/10 29/03/10 ...
3
votes
2answers
63k views

Automatically update cell in Excel with current timestamp when another cell is changed

In an Excel spreadsheet, I'd like to automatically update a cell with the current date and time when another cell is changed (like an update timestamp). There is a timestamp for each row. I'd like to ...
6
votes
3answers
3k views

Excel: Update a value in a named range

I've got some cells in an Excel doc populated from a named range (data/validation/list source=MyNamedRange): Here, the A1:A3 range is named Foobar B5:B6 is under data validation with it's source ...
2
votes
4answers
8k views

How to make bar graph shorter for higher numbers?

I would like to inverse the bar graph in a way that the lower the number, the higher the bargraph should be. Currently in the picture below, those items ranked 200 have the highest bar height while ...
0
votes
2answers
331 views

EXCEL generate a character in one column of a row, only if other columns in the same row are not blank

My speadsheet keeps track of when patients leave a specific floor of the hospital. There are columns in which where each patient goes is documented (1 column for "home", another column for "rehab ...
1
vote
4answers
12k views

Keyboard shortcut in excel for expanding the pivot table row?

I'm able to expand/collapse the row in the pivot table with mouse by: right clicking on cell in the first column of the row + selecting "Expand/Collapse"/"Expand" from context menu. or double ...
1
vote
3answers
1k views

How can I detect if a range of cells contains only identical values or not in Excel?

I have a range of cells and I want a formula that says if all these contains the same values or not. Usually for two cells I would use if but I want a solution that would work for ranges. I don't ...
2
votes
2answers
3k views

can I make Excel always open a delimited text file with “text” translation? [duplicate]

Opening a tab-delimited data file in Excel to view & manipulate the data is a very common operation around here. However, by default Excel (2003/4 or 2007/8) will read the columns in a "General" ...
3
votes
6answers
33k views

Quick way to convert Excel sheet to XML

How do you easily convert an excel file into a XML file? When trying to save as an XML File, it complains that the file does not have an XML mapping. Clicking help brings up pretty complicated stuff ...
4
votes
3answers
3k views

How do I get excel to close completely after creating a macro in a personal workbook?

I am using Microsoft Excel 2007 and have several macros in my personal.xlsb workbook, which I use often, so it is very convenient that Excel opens them automatically when it starts. What I don't like ...
0
votes
2answers
1k views

Return cell reference as result of if statement with vlookups

I have two sets of data in excel. One contains a set of data which represents the initial step of a process. The other set of data represents the additional steps which take place after the first ...
2
votes
1answer
11k views

embed file in Excel cell

I'm working on a simple "DB" for some people at work and I don't want to do anything fancy. the fields should be something like name , number , notes , File:resume, notes (where file:notes is the ...
3
votes
2answers
5k views

Excel shortcut: switching back and forth between that last active sheet and the current exit sheet

I know the shortcut CRTL + page up/down but I need the similar function that exists eg in internet explorer: Shift + alt I found a macro that is close but does not entirely solve the problem: If ...
0
votes
1answer
274 views

Help to bypass password for sample projects of excel

I am doing project in EXCEL VBA. i am taking refernce of some projects made in excel vba. But when I trid to open that excel sample projects, it ask me for password. so how can bypass password for ...
0
votes
3answers
2k views

Excel - Possible to create a sorted view of a column in one sheet on another sheet?

I'm trying, in Excel 2007, to populate a column in one sheet with the data contained in a column on another sheet, so that I may provide another sorting on the data, related to that sheet only. I've ...
0
votes
1answer
865 views

Excel - Linking multiple source spreadsheets with variable amounts of rows to a destination spreadsheet

I have multiple source spreadsheets, each with a variable number of rows. An example might be one spreadsheet per bank account, with one row for each transaction, with a date and amount. One ...
12
votes
3answers
49k views

Any Excel function that will reverse a string?

Are there any built-in Excel functions that will reverse a string?
3
votes
3answers
14k views

How to sum numbers in a range that only have a particular cell color [Excel]?

I would like to put a formula in Excel that would sum all the numbers of a specific range that have a specific cell background color.
1
vote
0answers
319 views

Sorting in Pivot Table on how data is summarized, not just the value

Often I am creating pivot tables that summarize some count by some category. Let's say I am counting Yes/No responses by some category. I usually add the count field and display it as a "% of row", ...
0
votes
1answer
417 views

Excel showing hidden data?

-- Excel 2003 SP3 -- I have a spreadsheet which I know for certain has a large amount of hidden rows which are correctly hiding on my colleagues machines, and were correctly hiding on mine two days ...
1
vote
1answer
1k views

Excel: VLookUp solves a potential Nested IF problem

I manually enter numbers on one cell according to text values in the cell adjacent to it. Is there a way to use the IF function to help me manage this? The text is automatically generated with a ...
4
votes
1answer
1k views

Conditional formatting using cell styles in Excel 2007?

Excel 2007 has added support for cell styles, which I find very useful. I love it when I can change formatting in one place and the change propagates throughout the spreadsheet. I also love ...
2
votes
2answers
1k views

Excel Table lookup with indexes in multiple columns

I have a Table in Excel the looks about like this: Test1 100 test2 200 Test3 110 test4 210 Test5 120 test6 220 I want a function that, given test 1 as input, will return 100 AND given ...
2
votes
1answer
363 views

XML to Excel file?

What's the easiest way of loading a simple XML file (like the one offered in StackExchange data dumps) into an Excel sheet?
1
vote
1answer
77 views

Weird Excel bar diagram behaviour [duplicate]

Possible Duplicate: Weird Excel bar diagram behaviour I have a very simple question. I want to have a diagram with the following table: Apple 30 40 50 Pears 200 300 400 Bananas 10 ...
1
vote
1answer
1k views

Excel document opens in IE 64, not in IE 32

Whenever I click on a hyperlink to a scrip that outputs an Excel 8 document, I get a prompt from IE to open the file or save-as. If I click open in IE 32 bit, the document opens in Excel (which is ...
1
vote
1answer
1k views

Concatenate cells that change daily automatically?

I use concatenate to pull data together from different cells in my spreadsheet. Since my data changes daily, I want the formula to also change daily without having to manually input the new cell in ...
2
votes
1answer
13k views

Create two axis stacked columns chart in Excel

The data I'd like to chart is like this: 2000 2001 2002 A x x x B x x x C x x x D x ...
3
votes
3answers
10k views

Randomise the order of a column of data in Excel

How can I randomise the order of a column of data in excel? For example, if I have a column of data of 10 rows 0 1 2 3 4 5 6 7 8 9 What functional could randomly re-order them to something like 5 ...
2
votes
2answers
3k views

excel autocomplete combo-box with on-selection event

I have an excel sheet for groceries. One column is the name, another is whether to buy it or not (checkbox) and another is the amount. I'd like to have a widget in the top row so that I start typing ...
0
votes
2answers
1k views

Change SystemDSN to FileDSN for 100's of MS Access and Excel Files

A particular team in my office uses 100's of Microsoft Access Models. They all connect with the database using SystemDSN and, the name of the SystemDSN is hardcoded in the Microsoft Access Models. My ...
2
votes
5answers
397 views

troublesome microsoft excel 2003

Hi I have a problem with my excel .. I want to paste one bunch of rows to another excel document and I can't do it with simple copy/paste .. I can copy then paste to notepad then copy again and ...
4
votes
2answers
18k views

How to copy a single cell into multiple worksheets in Excel?

I have about 30 worksheets, and I need to put my name on the top left corner (cell A1). Is there an easy way to do this without having to write it or paste it into every worksheet?
0
votes
1answer
907 views

How to prevent Excel from saving BLANK columns to Tab Separated File?

I have an excel spreadsheet with some tabular data, when I save it as Tab Separated Values file (.txt) it also saves a few blank columns on the right. I have to go and select the blank columns in ...
0
votes
1answer
824 views

Using Excel VBA to capture standard toolbar use

I am looking for a way to capture a click on a standard toolbar tool in Excel with VBA. The primary need is to capture any Copy, Cut, and Paste request by the user. I can capture the hot keys for ...
3
votes
1answer
3k views

Ubuntu: Convert OpenOffice Calc to Excel workbook using CLI

I need to create an automated report in a spreadsheet format. Unfortunately, There seems to be an easy way to create these reports using OpenOffice Calc, but upper managements wants them in MS Excel ...
1
vote
2answers
2k views

Configure Excel to look by default in Formulas, Values and Comments

I use comments allot in Excel and I need to search for a particular text that is in both values and comments. Is there a way to configure the Find and Replace in Excel to look by default in these ...
2
votes
1answer
79 views

Excel question combing text with numbers

Can someone tell me how I can make the following output? I have a cell with a number for example: 12345 (a1) I have a path to a file: ../dir/dir2/dir3 and a file extension called .jpg I ...
1
vote
3answers
8k views

Extract substrings from a comma separated string using formulas in Excel

I have this string in cell A1: A1 some text, to be, processed I want to create formulas for B1, C1, D1, etc. where: B1 some text C1 to be D1 processed E1 I already found the Text to Columns ...
2
votes
4answers
21k views

Filling cells with sequential numbers in an Excel (2003) macro

I need to fill an excel column with a sequential series, in this case from -500 to 1000. I've got a macro to do it, but it takes a lot of lines for something that seems like it should be a single ...
0
votes
3answers
273 views

Apply a formula to particular cells in Microsoft Excel

Say I have the following sheet: | A | B | C | 1 | 5 | 1 | 5 | 2 | 8 | 8 | 99 | 3 | 77 | 3 | 3 | 4 | 2 | 7 | 4 | 5 | | | | 6 | Base:| 2 | | ...
4
votes
1answer
516 views

Excel 2003 macro doesn't see named cell

I'm trying to clean up an excel macro where I was referencing cells directly ("LoopCount = Range("E4").Value") instead of using named cells ("LoopCount = Cell_LoopCount"). So I named cell E4 ...
2
votes
4answers
1k views

Excel transpose and organize

I have a bunch of rows, that I want to turn into a table. Is there a way in Excel to convert a bunch of entries, each occupying four rows into a table, where each of the four rows is put in one of ...
2
votes
4answers
6k views

How to drag and drop images from windows explorer to Excel?

When I drag and drop nothing happens, can you make it work?
0
votes
2answers
313 views

How to merge many text files data in databse

i have around 100 text files. The files have questions and 3 choices. FIles are like below ab001.txt -- contains question ab001a.txt -- is the first choice ab001b.txt ---is second choice ab001c.txt ...
1
vote
1answer
1k views

Why don't I have a “Web Service References” menu item in excel/VBA?

I'm trying to consume a SOAP web service from excel. Now according to This article (and confirmed by other articles and MSDN) if I do the following: Install the web services toolkit (I've installed ...
3
votes
2answers
1k views

Prevent Excel from updating absolute references when target is moved

I would like to prevent OpenOffice Calc or Microsoft Excel from updating absolute references when their target cells are moved. For example, if cell B2 is "=$A$1" and you cut A1 and paste it in A2, ...