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)

0
votes
2answers
8k views

Cell to Equal Value of Different Cell based on Match in Array

Need to Column C to equal the text in Column G based on the match the value in Column B. I do have Column C matching the row in which the value is found (based on columns B and F) Column C's Formula ...
1
vote
2answers
1k views

How to match against multiple value possiblities in Excel

I have list of person names in column A. I want to display "1" in column B for names which end with either "e" or "i" or "n". If there would be only one match to test against, I would write ...
0
votes
1answer
549 views

Excel 2010: Giving another cell value to an “x” (check) in a cell

Hopefully I'll be able to explain clearly what I would like: I have a spreadsheet about a car pool. I have to put an "x" (check) in a cell when there are seats available to transport people, but I ...
0
votes
1answer
3k views

Syncing two sheets, while being able to hide different data

I'm pretty new to excel- so please bear with me. I have created a spreadsheet to organize gear by serial numbers and by who has it. This list is getting updated multiple times daily as gear shuffles ...
0
votes
0answers
217 views

Excel 2007 update reference when copying a row to another spreadsheet

How can I update (using VBA) a formula referencing a cell in column X on one spreadsheet when copying that row to another spreadsheet in the same workbook, but moving that particular cell to a ...
0
votes
2answers
400 views

Restricting symbols allowed in cells of Excel spreadsheet

I need to format some Excel cells to allow currency (decimal point) only to be entered. Some employees completing the form enter a colon (:) instead of a decimal point which then results in an ...
4
votes
6answers
45k views

Missing Paste special options in Excel 2010

I have a weird problem with the Excel. Every time I copy and paste only the text is copying and the format is not copying as shown in the below figure. There are no paste special options available ...
0
votes
1answer
993 views

How to make Excel application load some xll files via vbs?

I need to be able to load the xlls into an instance of Excel. It is easy to do so using the GUI. Just use 'open file' dialog box and load the xll file. However, how I can script this behaviour ...
1
vote
2answers
8k views

How to compare data in 2 columns in Excel and then in one cell, determine if there are similar data in both columns

I have 2 columns in Excel: the first contains a list of employee names who perform function A, and the second contains a list of employee names who perform function B. What I want is to identify, in ...
0
votes
2answers
732 views

Programmatically updated a cell's formula in one PC shows #Name in another PC

We have a lot of spreadsheets and there is a need to upgrade an important Addin. The change will involve converting an old formula to a new one. It is not a straightforward change because the name ...
0
votes
1answer
921 views

Is there any difference between the Comma style and the Number format with commas and 2 decimals?

I'm trying to emulate the Comma style in Excel 2010. From what I can tell, this appears to be the same as having the Number format set with "Decimal Places" set to 2 and the "Use Thousands Separator" ...
0
votes
2answers
2k views

Unhiding rows not working in Excel 2007

I noticed a strange behaviour in Excel 2007 files. In an empty file, I select a number of rows and hide them. Then if I select the first and last row where all rows are hidden and unhide them, ...
0
votes
1answer
819 views

Can cells calculate / show date/time lapses?

I would like a 'cell' to show how many days have lapsed since the start of a period which is contained in another cell... ie.. page 1, cell A1 contains a date of '01/03/12' page 1, cell A2 contains ...
2
votes
1answer
4k views

Can I crop an excel chart to remove part of the chart?

I followed some instructions to create a speedometer chart (pie chart with a scatter line chart on top). However, this solution leaves a large blank space below the chart; 50% of the chart's space is ...
0
votes
3answers
6k views

Sum only if any one of six cells have a value

I would like a cell (total) to sum 6 other cells only if one of those 6 cells have a value. If none of the six have a value i do not want anything to appear.
0
votes
1answer
5k views

Excel- How to create a chart with a variable width column?

Note: if serverfault is not the proper forum for excel questions then please forward me to the correct forum in stackExchange group. I need to create a chart whose columns has different width. I ...
14
votes
13answers
65k views

When does Excel decide to overflow text into adjacent cells, or cut them at the boundary?

In Excel, 2010 or any other version probably, if I enter in a cell, a long single-line text that is longer than the width of the cell, Excel sometimes render the text across the next adjacent cells; ...
3
votes
2answers
198 views

Swapping rows and columns in Excel?

Is there a quick way to turn the list in the Column to be in the Row? See example below. Note how the row and column are switched.
0
votes
1answer
5k views

Excel: how to insert a true NULL in a formula?

I have a cell with the function =IF(ISBLANK(C10),"",A10) -- if C10 is empty then make this cell empty too, otherwise make this cell equal A10. This works, mostly, but when the C10 is blank it ...
-1
votes
2answers
7k views

Excel scatter chart, with grouped text values on the X axis

Apples 1 Apples 5 Apples 2 Bananas 1 Bananas 4 Bananas 6 I want two bars on the X axis (Apples, Bananas), but with crosses marking the points on the Y axis. Here's a mockup: I'd want 1 ...
1
vote
7answers
16k views

How to copy-paste absolute cell references temporarely as relative references in Excel?

In Excel (2007), when cells that contain absolute references (for example: $A$3) are copied, the absolute reference remains the same. This is by design, and the reason of using absolute references. ...
0
votes
1answer
687 views

How can I convert PDF to XLS on Linux? [closed]

Are there any opensource web applications to convert a given PDF to XLS format?
2
votes
1answer
2k views

How come I can only forecast a trendline 5 periods backwards, while I can forecast it 1000+ forward?

It doesn't make any sense. I can do it in Excel 2010 on Windows, but not Excel 2011 on OS X. Workbook I used: http://cl.ly/Ewfe Screenie:
1
vote
2answers
10k views

Very infrequent Excel crash

Have 1 user who keeps experiencing an Excel crash when sorting data. It is very infrequent and as of yet i've not done any serious troubleshooting, just an Office repair. Event log reports the ...
1
vote
1answer
323 views

How can I reference Excel cells by searching/ matching a cell value on their row?

I've trying to make a simple task list in Microsoft Excel 2012: Task# Parent Task# Task Description Days Due Date 1 Catch the llama 1 2 1 Shave the llama 1 ...
1
vote
3answers
2k views

excel vba - multiline regex not working

In cell A1 of an excel sheet I have the following contents inside the cell: select a.id, b.type, c.name from blah a, blah2 b, blah3 c where a.id = b.key and b.key = c.id ; Either of the below ...
0
votes
2answers
203 views

Finding duplicates in a column where the cells hold a formula

I have a workbook with 3 sheets. Sheet "PS", column A contains a list of player names that stays static. Each week, each person in column A sends me a name from an available list. They are allowed ...
0
votes
1answer
148 views

Excel pivot table to show % which are 0

Here's some pretend data: Apple 4 Apple 0 Apple 1 Banana 4 Carrot 0 Apple 0 Apple 2 I want to show the percent of each food that ...
0
votes
1answer
561 views

Dynamic time in Excel spreadsheet column

I am trying to automate time in Excel. I have two columns that show time advancing in minutes and seconds. I want to be able to automate so that if the time changes in one row, it will automatically ...
0
votes
1answer
2k views

How to scale a value entered in excel?

If I enter 123456, it should be displayed as 1.23456. However, the value stored internally should still be 123456. Is this possible in Excel? Thanks for your time.
1
vote
0answers
426 views

Auto rename files through excel sheet or through folder of these files

I have excel sheets with lot of files names like.. I want to renames all files through excel sheet and renames files itself.. Or 2nd condition is , if i rename files my self and rename in excel sheet ...
9
votes
2answers
9k views

In Excel, can I provide a “default value” in case my formula returns #NA?

For example, Say the value of a cell is: IF(ISNA(VLOOKUP(A3,somesheet!G:J,3,FALSE) * VLOOKUP(A3,someothersheet!A:D,4,FALSE)), 0, VLOOKUP(A3,somesheet!G:J,3,FALSE) * ...
1
vote
1answer
4k views

Combine cells with binary values in Excel

Imagine I have a couple of eight cells which all contain a binary value; so it shall be something like Cell A1 has value 0; Cell A2 has value 1; Cell A3 has value 0; etc. What I would like to have is ...
2
votes
3answers
7k views

How do I transpose data in columns to rows?

How do I transpose data in columns to rows? If the data is: Joe some city some state Bob no city no state and I use Paste Special » Transpose, I get Joe some city some state Bob ...
-1
votes
2answers
1k views

Microsoft Excel - Generating random time invtervals

I have a list of employees around 30. I need to create a simple table that will assign them a specific time that they are to be on the phone and 24 hours later excel will automatically(randomly) ...
1
vote
0answers
811 views

Corrupt Excel files have smile-faces and unusually large workbooks

I have several files which are massive in size, but should not be. The files are 20+MB and should be only around 1 MB. When opening with 7-Zip as an archive the file which is unusually large is: ...
1
vote
0answers
177 views

Application Wrapper for Excel Spreadsheet

I work with Excel pretty much all day every day. There are a few spreadsheets I have that I use almost like stand-alone applications. My problem is that I often have 10 or 20 other spreadsheets open ...
2
votes
2answers
5k views

Plotting discontinuous graph in Excel - Mac 2008

have some data that has gaps in it and Im not able to plot it in excel. I know I want to uncheck the "Leave Gaps" item in the Tools / Chart / Options panel. Plenty of googling has showed that that's ...
0
votes
1answer
833 views

How Format Vertical Data Label in Excel 2010

I have created a chart and the Vertical Data Labels. They are 0.5, 1, 1.5, 2, 2.5 etc. How do I remove 0.5, 1.5 and 2.5? Thanks in advance!
0
votes
1answer
154 views

Excel macros making all text superscript

I have several different macros that create a sheet in Excel 2003 and populate it with information. These macros are all written in VBA and some are linked to a toolbar in Excel and some are in MS ...
1
vote
2answers
313 views

Is there anyway to display spreadsheet as a tiny window without toolbar?

My screens are full of stuffs, still need to add a small spreadsheet, maybe one inch high and less than one inch wide. Is there any solution for this? I don't do complicated calculations, just some ...
1
vote
1answer
1k views

Excel: hiding detail of a group of columns then showing detail resets hidden columns to unhidden

I have a neatly formatted data table for printing (it runs across A1:ET35). Within this range are many hidden columns. I want to set it up so that a user can easily toggle visibility of columns C:DR, ...
12
votes
5answers
26k views

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 ...
15
votes
16answers
86k 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 ...
0
votes
0answers
243 views

Sharing parameters between queries in Excel 2010

I have a workbook with two sheets. Each sheet has a table created using a SQL parameterized query. I am trying to just enter the parameter values on the first sheet and have the second sheet use the ...
10
votes
3answers
100k views

Quick replace #N/A by 0 when vlookup

I use vlookup a lot in excel. The problem is with #N/A value when the seek value is not found. In that case, we often replace it by 0 using if(isna(vlookup(what,range,column,false));0; ...
6
votes
5answers
42k views

Copy/Pasting data from SQL Server to Excel splits up text into multiple columns?

I've got a problem pasting data from the result grid of SQL Server 2005 to an excel 2007 spreadsheet. I have a query in SQL Server that returns 2 columns (a number column and a text column) On one ...
4
votes
10answers
47k views

Excel freezes when copying / cutting to paste elsewhere

When cutting/copying some cells to paste them into another sheet/page, sometimes Excel freezes/locks up and fades out. At the top toolbar it says in brackets "not responding". Eventually, I must ...
1
vote
2answers
1k views

Count negative values within date range (month)

I have values in a table like this Date Values 01/01 5:35 02/01 -7:08 02/01 3:45 15/01 -4:05 02/02 3:25 13/02 4:25 19/02 6:20 I would like to count by month how many 'Values' ...
1
vote
2answers
15k views

Excel get cell value which is NOT Match in a row

I would like to search C2:I2 which is the cell value NOT equal to "=No=", which is "ABC", and the formula I would like to store in A2. I was able to look for some formula which works with MATCH(), ...