The 2010 version of Microsoft's spreadsheet application.

learn more… | top users | synonyms (1)

5
votes
2answers
30k views

How do I split one row into multiple rows with Excel?

I have a product database in Excel with several hundred entries, each of which has from 1 to 3 "tiers" of pricing: Standard, Deluxe, and Premium. Each tier has its own SKU (A, B, or C added on to the ...
74
votes
13answers
172k views

Easiest way to open CSV with commas in Excel

CSV files are automatically associated with Excel but when I open them, all the rows are basically in the first column, like this: It's probably because when Excel thinks "comma-separated values", ...
11
votes
13answers
52k views

Display Blank when Referencing Blank Cell in Excel 2010

I have an Excel 2010 workbook that contains a number of individual worksheets. The cells on one of the sheets are linked to individual cells on two other worksheets in the same workbook. I'm using a ...
5
votes
2answers
14k views

How do I use Excel 2010 Personal Macro Workbook?

I want all of the macros that I've written to always be available when I'm using Microsoft Excel 2010. How can I do this?
4
votes
5answers
34k views

Comparing Similar Text Strings in Excel

I am currently trying to reconcile “Name” fields from two separate data sources. I have a number of names that are not an exact match but are close enough to be considered matched (examples below). ...
51
votes
16answers
346k views

How to make Excel's “Auto Fit Row Height” feature actually auto fit the row height?

For every generation of Excel I can remember (including 2010, which I'm using now), Excel's "Auto Size Row" features sometimes fails to actually auto size a row when the cell contains wrapped text. ...
4
votes
1answer
3k views

Excel 2010 conditional formatting: selectively highlighting duplicates

I'm using Excel 2010 and would like to know if there's any way I can choose conditional formatting to highlight only instances of duplicates when it's found in ALL the columns I chose? For instance, ...
0
votes
2answers
2k views

VLOOKUP with multiple criteria

I have a workbook with 2 sheets: spg and data From the data sheet I wish to do a vlookup in the spg sheet, based on the value of multiple columns in spg. I can do a =VLOOKUP(H5,spg!$D:$I,6,0) but ...
6
votes
1answer
2k views

Conditional formatting to highlight the whole row

I would like to highlight the whole row 5 if J5="T". I used to do it with the old Excel, the 2010 conditional formatting ignores it. So my rule is like this and it does not work. I used the formula ...
13
votes
2answers
32k views

How to control CSV import into Excel 2010

When I open CSV files in Excel 2010 I get entire rows in cells, not distributed according comma separation. How to control this?
14
votes
2answers
8k views

Prevent Excel from clearing copied data for pasting, after certain operations, without Office clipboard

Whenever you copy a cell or a range of cells in Excel (my version is 2010 on Windows 7), and then do something else like insert new sheet row or simply edit another cell, Excel removes the clipboard ...
5
votes
2answers
12k views

Excel-Count number of visible cells containing certain string

I have a sheet in which I first apply a filter to a certain column to show "Unique records only". Now I have a column which contains, for e.g., values like: Applepie Applesauce Crabapple Banana Mango ...
5
votes
1answer
6k views

Why does Office 2010 fail to open an Excel file?

I have a excel file I've put on a file server. When required i access it via a dll, fill it with some data in background only (through ExecuteExcel functionality). It used to work before. Recently I ...
3
votes
5answers
15k views

Generate a comma-separated list of cell contents, excluding blanks

To concatenate a certain four cells, I'd use: =CONCATENATE(A2,",",C2",",D2,",",F2) This would make it so that... A2 = "Matthew" C2 = "Mark" D2 = "Luke" F2 = "John" would result in ...
2
votes
1answer
444 views

Excel VBA code Private Sub Worksheet Change Power function not working

I'm making a worksheet where the wind speed defines the potential energy of the location. This value is the basis of a few sheets in the same Excel file. I'm trying to write a code that where I10 is ...
2
votes
4answers
35k views

Microsoft Excel 2010 How to reference a table to another sheet

I am using Excel 2010. I got a sheet name "March" I would like to duplicate it[This is a table] (with reference / link) to another sheet called "Summary" which included January, Feb, and March. In ...
39
votes
11answers
97k views

Why does Excel treat long numeric strings as scientific notation even after changing cell format to text

I'm trying to process some data in Excel. The data includes numeric account numbers and other long numeric strings (like cell phone MEIDs). I am not doing math operations on these strings, so I want ...
9
votes
2answers
5k views

How do I get excel to not mess around with the formatting of a .csv file

I have two issues with a csv file that occur when I try and edit it in Excel. I have an ISBN column that gets reformatted to scientific format every time I save the file The encoding for the file is ...
5
votes
2answers
8k views

How can I change the default date format?

When I paste a date into Excel 2010 in the format "2012-12-07 00:00:00.000" I'd like it to be displayed by default as "2012-12-07", but it is actually displayed as "00:00.0". I know I can work ...
9
votes
4answers
29k views

Excel Scatter Chart with Labels

I have three columns of data. Column 1 has a short description, column 2 has a benefit number and column 3 has a cost. I can create a cost/benefit scatter chart, but what I want is to be able to ...
2
votes
2answers
5k views

How can I compare two columns in Excel to highlight words that don't match?

(I'm using Microsoft excel 2010) OK, lets say I have a list of phrases in both column A and column B (see screen shot below) What I would like to happen whether it be with a macro, VBA or formula ...
2
votes
2answers
31k views

Conditionally format cells based on match in another sheet

I have an Excel spreadsheet with 2 worksheets. The first is just a header row and a single column of item names. The second is a list of item groups, with a header row and a title in the left most ...
2
votes
3answers
63k views

XYZ 3D-chart in Excel

I have a table of data as follows: X Y Z N1 2 5 6 N2 2 7 8 N3 0 1 2 N4 0 4 5 N5 9 10 10 N6 4 9 9 N7 0 2 4 N8 0 7 8 N9 0 7 8 Now I would like to ...
2
votes
2answers
534 views

Duplicating formula in Excel 2010

I have a bunch of formulas in a column that I wish to duplicate exactly into another column, where I will go through and tweak them so that a different function is run on the same parameters. When I ...
9
votes
8answers
44k views

How to toggle Scroll Lock in Excel 2010 without a Scroll Lock key in the keyboard

I have a notebook with no Scroll Lock key (or any shortcut using the 'Fn' key). Somehow Excel 2010 got the Scroll Lock on. I see it left down in the status bar and now I can't navigate through cells ...
2
votes
2answers
8k views

Excel (2010) HYPERLINK: how to call a file with command line arguments and spaces in the path?

I am working on an Excel-document to make a (project-)folder structure manageable, using HYPERLINKs to important files and folders (Word/Excel, etc.). Since we are also using TFS, I want to add the ...
1
vote
1answer
253 views

Sorting cells in a column is easy. But how to sort cells in a row?

Assume school language learning exercise, based on making right order of words in a scrambled sentence. Then put numbers into the next row, specifying the order the words should be in. Now select ...
0
votes
1answer
11k views

How do you plot time on the X axis in excel?

I need to plot the decay of my phone's battery against time over night. My starting time is 23:17:47 and my ending one is 07:05:49. I don't have any issue in changing the time format in the current ...
10
votes
4answers
44k views

Is there an Excel function to create a hash value?

I'm working with a number of data lists that are keyed by document name. The document names, while very descriptive, are quite cumbersome if I need to view them on (up to 256 bytes is a lot of real ...
8
votes
3answers
16k views

Why is Excel truncating my 16-digit numbers?

If I type in a 16 digit number (format: number, no decimals) it changes the number on me. Example: 1234567812345678 changes the view to 1234567812345670. If I type it in as a general format it ...
5
votes
7answers
53k views

How to sum a range of cells with N/A values?

I have a range and need to sum it using SUM function but the result is N/A if there is any N/A value. How can I make the SUM function to treat the N/A value as 0 value? Please help!
3
votes
3answers
25k views

How do you update an Excel file (Data Refresh and update formulas) WITHOUT opening the file?

I have an Excel file that want to update and save automatically with out having to open it or manually interact with. Manually, I open the file up and hit data refresh which goes and does a SQL query ...
12
votes
3answers
11k views

How to insert a date into an Excel spreadsheet?

I want to insert date and time into a cell of an Excel spreadsheet. NOW() and TODAY() don't fit my needs. These functions insert dynamic date. But I want static: if I insert the date it won't change ...
8
votes
1answer
33k 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?
3
votes
1answer
818 views

Replace cell references with values without F9 using macro

This is an Excel macro question. Given a series of formulas such as the following: = A1 + A2 = B2 + B2 etc., ... I would like to replace each of these cell references with the values without ...
1
vote
3answers
8k views

Excel: how to permanently apply conditional formatting to the whole spreadsheet?

I have the following annoying problem with Excel 2010. I use Conditional formatting to apply bold formatting to each line of the spreadsheet that meets a condition expressed by a formula. In the ...
1
vote
1answer
16k views

Split Excel worksheet into multiple worksheets based on a column with VBA

Question is simple and may be repetitive. I have an Excel workbook which contains around 50 columns I have a criteria column for splitting this workbook into mulitple workbooks The approach is as ...
12
votes
3answers
11k views

show Excel sheet tab in >=2 row

Some days I'm working with extremely large Excel files, they have to many sheet that can't be show in 1 sheet tab. (see image) So, I'm wondering that whether or not we can show Excel sheet on ...
9
votes
3answers
66k views

convert text string to date/time format

I have a column that is text formatted like 20110408_2041. How can I format it to date/time format?
7
votes
5answers
22k views

Why does this Excel file keep asking to save?

A client sent me some Excel .xlsx documents, and every time I open one of them and then close it, Excel asks if I would like to save changes, even if I didn't make any. This doesn't happen with any ...
6
votes
4answers
35k views

Diagnosing slow Excel spreadsheets?

A client contacted me, complaining of Excel spreadsheets that were taking far too long to open. They use Excel for creating invoices, so they have hundreds of Excel spreadsheets with light formatting ...
6
votes
1answer
32k views

Excel 2010: Can I hide columns without excluding the data from a graph?

In Excel 2010, is it possible to hide columns but still have the values show up in a graph? I have a graph of monthly data going back to 2005, with each month's data in a column. If I hide the ...
5
votes
6answers
19k views

Remove duplicate entries, keeping latest only

I have several spreadsheets with information I need to consolidate, each with different (and sometimes overlapping) portions of the final data set I'm hoping to create. Typically, combining ...
4
votes
1answer
29k views

Excel, how to find column number of min value?

I have a table in Excel. Can anyone help me find the column number of the cell where the min value of the row is? Example: 200 is the min value in row1 and has the column number 1. Is there a ...
3
votes
3answers
4k views

Multiplying values in Excel 2010 (Same cell)

Another excel question. I'm using Excel 2010. If for example I have a value in cell A1, lets say 2.59 What I want to do is multiply that cell by 1.333 but in the same cell I don't want to have to ...
3
votes
2answers
40k views

How to disable Excel number autoformat

This problem is simple to correct but is very very annoying what I'm doing is to type a hour range, eg. (0-1),(1-2),(2-3) so on per cell row DATE --- HOUR 1/1/2013 0-1 1/1/2013 1-2 ...
2
votes
2answers
966 views

How can I combine two lists into one long list?

I have two lists in coloumns 1 and 2. I need coloumn 3 to be of the format one dash two. For example: Column C will need to repeat all values of A and B. I would prefer a formula for this is ...
2
votes
1answer
3k views

Excel 2010 macro to separate a large data file into workbooks

I found some code which works perfectly to separate a file based on a column (line 31) into separate tabs within a worksheet. I just need it to separate the files based on a column into a separate ...
1
vote
0answers
2k views

create Macro in VBA with colors if the value of the cell is greater than 100, the macro will change background color of the cell to blue

How can I create a macro in VBA that accomplishes the following: For the active cell, if the value of the cell is greater than a number, the macro will change background color of the cell .
0
votes
1answer
41 views

Excel: Changing and updating a future value based on past occurrence and lookup in a table

I have a big dataset, dataset is in such a way that each time a customer is called a row will be created. So the customer Number is the unique ID here. Based on their current and past response type, ...