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)

3
votes
3answers
23k 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 ...
3
votes
1answer
14k 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
15k views

How do you force the focus of an Excel Cell to remain the same?

I have set up an excel table where occasionally the data I have in several columns gets shifted down several rows to make room for new data. I also have a cell that references the top most cell in ...
3
votes
2answers
5k views

How to utilize multiple monitors with Microsoft Project 2010 (or other Office 2010 products)?

My question does primarily concern Project 2010, though it would be applicable to other Office products such as Excel, which can tend to span several horizontal screens. Seems like an obvious ...
3
votes
2answers
7k views

Saving a tab separated file in Excel without quotes

I have a tab separated value file that I want to edit in Excel. When Excel saves the file it modifies any column with quotes. It puts quotes around the whole column, and then double any quotes within ...
3
votes
3answers
2k views

Excel trendline accuracy

This is a problem I have every once in a while, and it annoys me tremendously, beacuse I have always to recheck every trendline I get. An example: r L (mm) 30,00 97,0 60,00 103,2 ...
2
votes
5answers
931 views

Is there an efficient way to copy text from a PDF without the line breaks?

I need to get thousands of snippets of text from PDFs to a spreadsheet. They are short, seldom more than 2-3 rows, but each line break creates a new cell, and I have to repair that manually, which ...
2
votes
1answer
982 views

How do I filter on bold, struck through or italic text in Excel 2007?

Is it possible to filter an Excel column based on the format of the text, such as whether it's bold, italic or struck through?
2
votes
2answers
1k views

VBA Excel 2007 help to speed up code to hide rows [closed]

I am trying to write a VBA code that automatically hides rows if a value in the row is equal to "". My range of values is A37:A400. Because my range is so big my previous VbA is to slow and looks one ...
2
votes
1answer
208 views

how to determine which values in a given set of cells will add up to a numerical range

In other words, I'm looking for a sum range that consists of combinations of cells from an array. Given a set of numbers: 1 720 56 17 59 120 153 203 39 1 690 583 582 561 256 310 232 95 108 16 26 ...
2
votes
2answers
855 views

Excel - Sum values if their row meets more than one criteria

I have a spreadsheet of my bank account transaction history in this format: | A | B | C | D 1 | Date | Amount | Balance | Category 2 | 2013-03-10 | $ -10.59 | ...
2
votes
3answers
12k views

Excel ran out of resources while attempting to calculate one or more formulas

I received the error message "Excel ran out of resources while attempting to calculate one or more formulas" on ONE of my computers (Out of 2). My worksheet contains: (Inside a single worksheet, not ...
2
votes
3answers
15k views

Count unique names in excel column range

I have an excel spread sheet that contains a list of server names in column B. The server names appear multiple times in this column. I would like to count the number of unique server names in a ...
2
votes
1answer
6k views

Compare 2 text cells and show difference in the third cell

If cell A1 = abcd, cell B1 = abcdefg, then what formula to use in order to get the text difference efg?
2
votes
2answers
3k views

How do I prevent Excel from locking files by default?

When I double-click on a CSV file on a network share, the file is opened in Microsoft Excel (which is what I want). However, Excel assumes that I'm going to modify the file, and that everyone else is ...
2
votes
1answer
1k views

Using Excel Linest function with blank cells

I need LINEST function to be applied with blank cells to get the slope parameter as well as the associated standard error. I can get the slope parameter with a normal function that may resolve the ...
2
votes
3answers
6k views

Ctrl + Shift + Arrow stopped working

When I work with a big amount of data, I introduce formulas in Excel by using the shortcut: Ctrl+Shift+arrow. That shortcut allows me to select a whole set of data to the left, right, up or down. ...
2
votes
6answers
27k 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 ...
2
votes
5answers
16k 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 ...
2
votes
2answers
8k views

Export to CSV From Excel

I frequently use Excel to prepare data to load into a table. I create a CSV file from it and use that file for data loading. I generally find myself creating the CSV file multiple times. My current ...
2
votes
1answer
2k 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, ...
2
votes
1answer
245 views

Is Workbook.FileFormat available to the Excel UI (specifically when the file type can't be saved)

As a programmer I eventually located the file type of a .d extension file loaded successfully by Excel 2007 by interrogating Workbook.FileFormat via VBA, but can a normal user determine it is a DBF4 ...
2
votes
2answers
13k views

Excel cells - show text different to the actual value?

Is it possible in Excel to have a cell show text that is different from its value? I would like to have a a cell that has the value 1,2,3 ... or 7 but it will show day of the week as text - but when ...
2
votes
1answer
7k views

Strip out all HTML Tags from an excel file

I have an excel file that has cells with text that includes HTML tags. The data was exported from a database and the HTML tags were included. The HTML is stored as text, but I need it gone. Is ...
2
votes
2answers
5k views

How can I change a cell's background color based on the cell's current value?

I have a column with function values of True or False: =IF(ISNA(A1), false, true) from row 1 to 5000+ rows. I would like to mark all the False as red background color or text as red. Is there any ...
2
votes
4answers
9k 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 ...
1
vote
3answers
101 views

How to split numbers into columns with negative and positive values, without empty cells?

Given positive and negative values in column A of an Excel sheet, I want column B to list the positive values from column A, and column C to show the negative values, in the order in which they appear ...
1
vote
1answer
28 views

can someone help me with my Formula Excel need it show blank

=IF(NOW()-B4>=15,"OK","Warning - Time to Renew") that is the formula, but can it stay Blank until a date is put into cell B4 thanks
1
vote
1answer
55 views

Dropdown, multiple values in dropdown

I'm trying to make something for my hobby, and for that I was wondering on how to do it: These are my input values: And via dropdown you can choose one player and it autofills the other values: ...
1
vote
3answers
484 views

Excel function working like SQL group by + count(distinct *)?

Suppose I have an Excel sheet with below data CODE (COL A) | VALUE (COL B) ============================== A01 | 10 A01 | 20 A01 | 30 A01 | 10 B01 | ...
1
vote
3answers
761 views

Conditional Formatting: 3 color scale showing text relevance in Excel for Mac

Is there a way to use conditional formatting in the following manner in Excel? (For those who are curious, I am using Excel 2010 for Mac) I want Cell B2 to be green when A2 says "John likes green ...
1
vote
1answer
3k views

How to get relative worksheet references when copying formulas in Excel?

I have a workbook containing two types of worksheets. One type is 12 worksheets, each referring to a month (JAN, FEB, MAR, etc.) Then I have another type of worksheets referring to employee's time ...
1
vote
4answers
18k views

Extracting Data Between two Characters in Excel

I have the following format in Excel and I am trying to extract the data between the characters: First Last <flastname@xxxx.com> I need to extract the email address between < and > into ...
1
vote
2answers
11k views

Excel Date Formula - Return the next specified day of the week

I need a formula that will always return the next specified day of the week. For instance IF(A1="A",RETURN NEXT FRIDAY, IF(A1="B",RETURN NEXT TUESDAY.....ETC The reason I need this is because I ...
1
vote
1answer
1k views

Excel array formula for max with multiple/ variable criteria

G'day there, I have a sheet that contains 365 rows, representing dates from a referred Start Date (for the following year). There are about 30 columns each of which has an agency name. The cells in ...
1
vote
2answers
3k views

Excel: Merge two tables based on one column

I have two Excel 2007 sheets A,B with the same columns. B contains less rows, but has a column with updated data (i.e. data in this column is newer than in the same column in A). I want to take every ...
1
vote
2answers
2k views

View linked Excel 'Windows' on separate screens

A colleague had a problem where he couldn't link a cell in his destination workbook with a series of cells in a source workbook. The problem was that in order to link workbooks, he had to open them ...
1
vote
1answer
6k views

How to import (and view correctly) Chinese UTF-8 encoded CSV file with Excel 2011 on Mac OS X

I have a CSV file which I can open and view the column with Chinese letters normally with TextMate. However, when I try to import this file into Excel 2011, the field comes with unreadable fonts. ...
1
vote
2answers
9k views

Text values in a pivot table?

I have a table (in MySQL) with 3 columns: Location Category Supplier A Computers Company X A Printers Company Y B Computers Company X B ...
1
vote
2answers
6k views

COUNTIFS does not seem to count blank cells

I am trying to build a COUNTIF statement in Excel 2010 using a total of 4 sets of criteria. I would ideally like it to look something like this: COUNTIFS(range1,value in a1,range2,value in ...
1
vote
1answer
262 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 ...
1
vote
2answers
778 views

Excel conditional formatting of row

Can someone please tell me how to highlight multiple cells in a row based on some values in any one of a given number of cells? For example, if in Row 2 any of the cells from D2 to L2 have value "A" ...
1
vote
1answer
595 views

Spreadsheet formula to count consecutive values in a column

I have a column with "Y" and "N" values, and need a formula to count the last consecutive occurrences of "Y". So if I the column looks like this: Y Y Y N Y N Y Y the desired result would be 2 Y Y ...
1
vote
1answer
3k views

How can I display only the unique values in one column in Excel?

Is there a way to display the unique values in a column ONLY? Example: AAPL AAPL AAPL AAPL AKAM AMZN AMZN AMZN AMZN AMZN AMZN Expected result: AAPL AKAM AMZN I have already done a sort for ...
1
vote
2answers
264 views

I have a sheet that has 2 cols; in one is the name, in the other there are one or more emails, separed by comma

I have an Excel worksheet that has two columns, in one is the name, in the other there are one or more emails, separated by a comma. Now, I want to get two columns with repeated names but unique ...
0
votes
1answer
31 views

Select text to display based on matching a symbol in another row

How do I write this excel cell formula? In Row 1 is a list of words and they each correspond to an "!" or an "X" in Row 2. Only one word is given an "!". [A] [B] [C] [D] [E] ...
0
votes
1answer
59 views

How to fill down formula so that range references are offset by 5 rows rather than by 1 row [duplicate]

How can I get Excel to increment ranges arguments by more than one row when I fill down a formula? For example, if I want Excel to keep averaging the next five numbers in a column, how do I set the ...
0
votes
2answers
27 views

How to assign rows in multiples of a number (Excel/Google Docs)

For example, I want the first entry in Column B (B1) to show A5, then B2 shows A10, then B3 shows A15. How would I go about doing this?
0
votes
5answers
225 views

Pivot table - Summarising multiple independent data columns

I have a large table containing daily inputs into a system, which has the columns as shown: Day Itemname1 Itemquant1 Itemname2 Itemquant2 ..... The item names and quantities are independent of each ...
0
votes
1answer
71 views

Clever way to connect multiple images to on-click macro - VAB Excel

I have asked this question before, but admittedly in a terrible way, so I am asking this again. So here I am once more. I am making a program in VBA Excel - or better, combining a certain worksheet ...