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)

3
votes
1answer
165 views

EXCEL Formula economies of scale calculation

I need a formula to calculate a subscription charge that reduces based upon the number of subscriptions purchased, but as additional different subscriptions are added they will be at a different ...
3
votes
5answers
24k views

Apply conditional formatting to multiple rows

When using the Conditional Formatting in Excel 2013, I can't seem to apply one rule to multiple rows. I use data which is not related in multiple rows. For example, row A contains prices, row B ...
3
votes
2answers
7k views

Split Text into Columns Function in Excel

I have an excel spreadsheet with rows of data as shown below. There is a code with the product name and then a number. QA32 Product One (Large) 23 AZ63 Product Two 96 PR65 Product Three 149 How do ...
3
votes
2answers
9k views

How to copy a cell's formatting using a formula?

For example, cell A1 contains the text "Hello World" which is in bold. In cell A2, I use the formula =A1. Therefore cell A2 now also contains "Hello World", but it is not in bold. How can I modify ...
3
votes
3answers
469 views

How make this graph in Excel?

I'm trying to do this kind of graph in Excel, but nothing so far. I don't find anything like it, but it has to be this way, not bars. starter 22,58 20,76 7,1 37,93 68,42 ...
3
votes
2answers
105k views

Plotting multiple series in a line graph in excel with different time values?

I have the following data: Time v 0 5.4 5 4.5 10 5.3 15 6 20 5.1 25 7.4 30 6.7 40 6.1 50 6.9 60 5.9 70 6.1 80 6.2 Time v 0 4.7 5 4.8 10 5.5 15 6.1 20 6.8 25 7.5 30 7.9 ...
3
votes
1answer
5k views

Is there a way to copy an Excel worksheet without copying all of the cell styles in the source workbook to the target workbook?

The title pretty much says it. I have an Excel document with tons of randomly named styles that I didn't create but that I want to copy a worksheet from. When I do, it drags all of those styles into ...
3
votes
5answers
53k views

How to get excel row numbers to skip the header

This is really a cosmetic question. Excel by default starts counting from line one which is the header. Is there some way to have it start counting from the real line one (the line after the header). ...
3
votes
2answers
3k views

Excel conditional formatting based on a formula

I would like to use Excel's conditinal formatting of a particular column based on values in two other columns. I have values in columns A, B, C and D. column A has text values (any value user enters)...
3
votes
4answers
21k views

How to find broken links in Excel that can't be broken with 'Break Links'?

I have an Excel workbook that complains about broken links every time I open it: I've tried using 'Find' to search for: [. as suggested in Microsoft's guide. I've also tried searching for ...
3
votes
1answer
39 views

counting a text string in non-consecutive sheets

In Excel 2003 I`ve a question about counting a text string Man in sheets named 001R through 900R, in the same cell C8 of all the sheets I`ve got the following answer =SUMPRODUCT(COUNTIF(INDIRECT("'"&...
3
votes
3answers
2k views

Importing from a CSV modifies the number format into scientific notation (1,23E+18)

Suppose I have a following string in a .csv file which I am trying to open in Excel 2010: 1073741824;1073741824;1073751824;1073741832;1152921513196781569 However, when I actually open it, the value ...
3
votes
3answers
118 views

remove leading 0 in front of decimal except when a a non-zero number precedes it

I have a column, which has a comma separated values inside each cell that look like this 0.1, 0.2,0.3, 0.4,0.5, 0.8,1.0 1.5, 1.6,2.0, 10.6,10.9, 15.2,30.75 20, 0.25,280.2, 0.29,300.2, 423,530.76 ...
3
votes
1answer
456 views

Excel 2010: Formula for Sum and Return Date

I have a workbook with a Summary sheet and a Data Set sheet with a linked table containing 700k+ records of financial data on investments. (In my particular case, it is real estate properties; but ...
3
votes
1answer
21k views

How can I summarize my data by week?

I have a spreadsheet where I need to run averages of the information from month, week & day. Below is just part of the data I would need to break down. Column A is the Date Assigned Column B is $...
3
votes
1answer
6k 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 ...
3
votes
1answer
48 views

How to calculate an average value based on duplicate groups?

I have a table which contains data recorded at various times. The data should have been recorded at 5-minute intervals but this wasn't always the case. Therefore, to try and clean this up: I set ...
3
votes
2answers
767 views

Modify value of a cell using the old value as part of the new one?

I have a table in which row 2 has tons of columns. Each of those columns in row 2 has a varying value in the range of 1-1000000 represented by X in the example below. How can I add modify the cells ...
3
votes
2answers
18k views

sum of vlookup using array formula

I have such table of payments: account | amount --------+------- 101 | 3 101 | 5 102 | 7 103 | 9 I named the range of this table "payments". Its first column I called "accounts". ...
3
votes
2answers
4k views

How to make Excel retain the contents of its clipboard [duplicate]

I'm making a list in Excel where many rows have the same data in a number of columns, enough so that I'd like to hit Ctrl-C once on this set of common data, and then just hit Ctrl-V every time I make ...
3
votes
4answers
7k views

Excel Smart Find and Replace only specific characters

I want to change INT to INTERNATIONAL and NA to NATIONAL ASSEMBLY in whole excel workbook through an excel Macro or Find and Replace dialogue box. But when I run the macro or change it through Find ...
2
votes
3answers
4k views

How to help Excel parse my CSV automatically

I'm generating CSV files from a script and then want to look at them with Excel. I noticed Excel does a smart job of identifying money (by the $) and dates, and formats those cells accordingly. ...
2
votes
1answer
30k views

In Excel how can I return all the items that are not found in an original list (based on a new list)? [duplicate]

Possible Duplicate: Extract values from a range that are missing from another I have two columns in Excel (Column A and B , going left to right), like so: Adel Adel Adam Bob Ashley ...
2
votes
1answer
5k views

probable cause of excel files getting corrupted

how to find out the probable cause of corruption of excel files...here i m not saying of a single file...files are getting corrupted one after the other...can 1)using windows 8 with excel 2007 and ...
2
votes
5answers
30k views

Protecting the formatting in Excel 2007

I'm creating a number of forms in Excel 2007 for other people to use. They will be accessing the digital versions, and I'm afraid that they'll eventually accidentally break the formatting I've ...
2
votes
1answer
6k views

Copying data from multiple word docs into one excel sheet

I have about 4000 doc and docx files which contain a table. I've managed to import these into one excel sheet using the following script: Sub Macro1() Dim xl As Object Set xl = CreateObject("...
2
votes
1answer
5k views

Find first number greater than x and return another value in that row

I have created an investment spreadsheet. In one column I have the ending balance each year. In another column I have the years. A B 2000 500K 2001 450K 2003 1.2M 2004 1.5M How can I display ...
2
votes
2answers
492 views

How to concatenate values in Excel?

I have a spreadsheet that looks something like this:    A B C D E ... 1| 1 0 0 1 0 2| 0 1 0 0 1 3| 0 0 0 1 0 4| 1 0 0 0 0 I'd like to concatenate row 1 in column F so ...
2
votes
2answers
79 views

Find information about range of Excel data. Is it constantly growing or cyclical?

I am (short-term) monitoring the sizes of several UNIX files and can easily export the numbers to an Excel spreadsheet. The files will eventually be indexed by Splunk and that team wants to know the ...
2
votes
1answer
125 views

Format a number in Excel

When I have this as input 1234567890123 I want it to be displayed as 12 3456789012 3 Is this at all possible? My attempts so far were unsuccesful.
2
votes
1answer
2k views

Excel/Libreoffice returning TRUE for IF() instead of expression

See the following situation: For some Here cells A1:E3 have plain text data (no formulas), and C4:E4 have the same formula (i.e. typed in one cell and then autofilled to another ones). I wanted ...
2
votes
1answer
3k views

Word 2010 - Mail Merge with Superscript Formatting

I have a user with a letter in Word 2010, built for a mail merge. The source document for the addresses is another Word document. The source document includes addresses that have super-scripted ...
2
votes
3answers
249 views

Getting the value in one column from a formula on another column in excel

I have an excel table that looks like this: Date Original Actual 11/10/10 15 12 12/10/10 12 10 ... 24/10/10 2 1 25/10/10 -2 ...
2
votes
1answer
140 views

Easy way to put multiple columns in Excel on their own rows?

I have a sheet that looks like this: A | B | C | D | E | ---------------------------------------- 62| Value1| Value2| | | 345| Value3| Value4| Value5| Value6|...
2
votes
2answers
753 views

I can't write credit card number when I write the last number becomes 0

While trying to fill in credit card numbers into Excel 2010, when I write 4645890379859736 last number becomes 0 Example:4645890379859736 is turned into 4645890379859730 Note: I can't use any - or ...
2
votes
1answer
80 views

Excel formula to take out quotations

Does anyone know a way to get excel to not include quotations in a value? I have the formula: =WEBSERVICE("http://finance.yahoo.com/d/?s="&A3&"&f=p2") so that I can pull the % ...
2
votes
1answer
4k views

How do you format the date filter selection box for Excel Pivot Table

I created a PivotTable in Excel 2013 and used a date value as a filter. When I want to filter by a range I have to select the individual days. The option looks like: 1/1/2013 1/2/2013 1/3/2013 ...
2
votes
2answers
276 views

SUMIF/ COUNTIF (look up names from column 1 then sum numbers from column 2)

I need to look up names from column 1 then sum numbers from column 2. Some names in column one appear more then once. (The spread sheet I am working on has 7400 rows!!!) Example: Column A ...
2
votes
1answer
262 views

How to retrieve non-contiguous cell collections from strings in Excel?

I tried to =SUM(INDIRECT("B4,B6")) but it doesn't work. How to do that?
2
votes
2answers
25k views

How can I check to see if two Excel spreadsheet columns have the same exact data?

I have 2 Excel spreadsheets that I must do a side by side comparison on. I am comparing one employee spreadsheet to another spreadsheet of the same employee and there are columns that need a 'Y' or '...
2
votes
2answers
7k views

Graphing mean and standard deviation

I have measurements for multiple devices, and have their mean and sd values. I would like to produce a chart that would show these values, and I think the best would be if I could have something that ...
2
votes
1answer
235 views

Can I run a VBA function whenever excel calculates, even when I don't click F9?

This is a followup question to this one: Can I make Excel beep when a calculation is finished? The answer relies on me pressing F9. However, even with calculations on manual, sometimes calculations ...
2
votes
3answers
19k views

How to highlight data points in a scatter chart in excel?

I have a scatter chart with a series of numbers in Excel. Most of the time, those numbers are high (>1000). I would like to display the scatter chart with those numbers in the default color, but if ...
2
votes
2answers
111 views

Problems figuring out weekly scheduling and hours past midnight

This is a screenshot of what I have What I'm trying to do is have the hours for each individual person posted as shown or a way that the hours can be posted like this and then have the total amount ...
2
votes
1answer
2k views

In Excel, I pasted in a bunch of URLs, but they aren't live links — how do I bulk convert them to live links?

In Excel, I pasted in a bunch of URL's into a column. However, they aren't live links after pasting. How do I bulk convert them all to live links swiftly?
2
votes
3answers
4k views

Listing non-blank cells in Excel based on adjacent column(s)

Given columns A and B, I want to list the A-values that have a non-blank B-cell in their row, in column C: A B C One Two Two x Four Three Four x ... The best I came up with ...
2
votes
1answer
79 views

How can I execute this function using VBA in excel?

I'm trying to get this function in VBA but I'm new to it. I don't seem to know how I can get this done: What I'm trying to do is, for each cell in column B that's numeric, I want to take the next ...
2
votes
2answers
4k views

How to replace cell formula with its own value via UDF?

Problem Description I have a custom formula that I wrote, but it takes a while to calculate the answer, and since Excel updates function values (When you activate the workbook, or re-open the ...
2
votes
1answer
2k views

In Excel, doing a “Freeze Panes” on the right-most column instead of the left-most?

My understanding of Excel's "Freeze Panes" feature is that you can only freeze the left-most column(s), or top-most row(s). However, I have a table where a very important column needs to be located ...
2
votes
4answers
5k views

Excel formula to get the penultimate value in a row

Imagine I have a row of data in excel, like this: [A] [B] [C] [D] [E] [F] [G] [H] [1] Data 5 8 12 32 15 9 89 I want a formula that will return the ...