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.
12
votes
11answers
15k views
How to make cells perfect squares in Excel?
In Microsoft Excel,
I want to make the height of first three rows and first four columns (12 cells in the top left corner) such that these cells are squares. How can this be done?
Surprisingly, ...
17
votes
5answers
547 views
How to simultaneously rename a file in MS Office and delete old version?
In Microsoft Office, when wanting to save a file to a different file name without keeping a copy of the previous file name, doing so requires two steps:
First, File -> Save As... and select the new ...
3
votes
3answers
937 views
How to insert empty rows in Excel 2013 when I have copied rows in my clipboard?
This seems like something which should be painfully simple to do but I can't figure it out. So with an empty clipboard I can right click on a row header and say insert row, no problem, it inserts a ...
1
vote
1answer
50 views
Excel: Conditional formatting using icon sets?
I would like to format the Subtotal row such that within each Subtotal cell is the total and an "icon set" icon that will show if that month is greater than or less than the previous month's total.
...
1
vote
1answer
28 views
Excel - How to change code to Workbook and not worksheet
I have the following code:
Private Sub Worksheet_Change(ByVal Target As Range)
Set KeyCells = Range("C9")
Set isect = Application.Intersect(KeyCells, Range(Target.Address))
If Not isect ...
1
vote
0answers
26 views
Hidden comments when scroll down with fixed header row
I commented some of the abbreviations of a header in excel. When I fix the header row and scroll down I can still see the red corner. During mouse contact I see the upper part of the comment, but only ...
0
votes
0answers
30 views
Creating a matrix from two others of unequal length [duplicate]
I have two tables, each of different lengths. Each Table has a column called ID NAME and another with its corresponding values. Some ID NAMES are the same for both tables but some are missing from one ...
0
votes
1answer
29 views
Close Full Screen window does not go away in MS Excel 2003
I have a problem or bug in MS Excel 2003 where the close full screen mini window does not move or go away. No matter what I do, I cannot move the window or even click any of the options.
I'm ...
1
vote
2answers
54 views
Look up the next highest value in another column with Excel
I have two columns A and B.
A B
20 100
40 200
60 300
80 400
140 500
190 600
240 700
User inputs a value in a cell. I need to check ...
0
votes
3answers
869 views
Copying a hyperlink so that it works with a series
OK, this should be a no-brainer, but I cannot find anyone to help me with this:
Example
A5-K5 On worksheet 1 contains info relating to one specific item.
I want to have a hyperlink in L5 taking me to ...
13
votes
4answers
70k views
How do you auto resize cells in Excel?
I have copied some data into Excel 2010. I have found that some of the cells need to be widened to allow the data to fit. How do I automatically adjust the height of the cells to fit the content?
0
votes
2answers
79 views
Why are Excel macro dangerous?
Can someone explain me why Microsoft Excel 2012 is telling me that accepting automatically every macros is dangerous, I mean how can they be dangerous, for my worksheets, for my entire computer ?
0
votes
1answer
36 views
Add rows to insert dates from a “from” and “to” column
I want to breakdown an Excel sheet by having it in a specific format.
Right now, the sheet is like this
FROM TO TOTAL
01/27/12 01/27/12 1
03/09/12 03/09/12 1
04/11/12 ...
0
votes
1answer
1k views
Copy/move values matching a pattern to another column
I have a worksheet where we need to move all the values matching a pattern to different columns.
For example, we have a B column with values that are one or more letters followed by one or more ...
2
votes
2answers
34 views
Calculate average score for by category in Excel
Assume I have the following 2 sheets in an Excel workbook:
Sheet companies:
A B C
1 COMPANY COUNTRY SCORE
2 Foo Greece 10
3 Bar Greece 2
4 Baz Greece 3
5 ...
2
votes
1answer
32 views
Microsoft Excel 2012 add a date when I open the document
I was wondering if it was possible to add a date every time I open my excel sheet.
I know the function is =TODAY(), how can I proceed to do this for example let's say :
A
1 02/05/2013 (Today's ...
1
vote
0answers
14 views
Auto-linking an outlook template to an excel chart
I need to send out data chart from excel on a daily basis. I would like to create a template that automatically updates the chart when opened. I am thinking there has to be a way to create a macro ...
-2
votes
2answers
120 views
Nested IF formula
I want a formula that will capture the following three scenarios below. In column S, I have Orange in cell S2, Mango in cell S3, Grape in cell S4, Pawpaw in cell S5, Onion in cell S6, Apple in cell ...
0
votes
3answers
2k views
Dates not recognized as dates in pivot table pulling directly from SQL Server
My pivot pulls from an external data source with a date column. Excel doesn't see this column as a date and the 'Format Cells' option panel doesn't change how the dates are displayed. The cell data is ...
12
votes
4answers
34k views
Need two excel windows side by side, on different monitors, in the same instance
I have to do work between two spreadsheets and I need to view them side by side.
I can't just open the workbooks in separate instances because I need to make use of features like paste formatting, and ...
1
vote
3answers
7k views
Google Chrome opening Excel spreadsheet annoyance
When I go online with Google Chrome and select to "open" an Excel spreadsheet from a specific web site, it opens the file in Excel 2003. The annoyance is that even though I did not choose to "save" ...
6
votes
5answers
41k views
Excel: how to count number of distinct values in a range?
I've got a large table that is already organized using filters etc. I'd like to add a summary underneath certain columns that contain the number of distinct values in that column.
There's no ...
-1
votes
0answers
107 views
Cartesian product from two sets of values
I've two sets of values and want to produce their Cartesian product (in here - a matrix with rows and columns specified by the two sets of values) in MS Excel using no macros. I'd like the values to ...
4
votes
6answers
8k views
How can I prevent Word/Excel from trying to reach the printer?
Whenever I perform certain operations in Word or Excel (2007), these applications are trying to reach the printer. I work on a laptop at work and at home. When I'm at home (not connected to my work's ...
9
votes
11answers
62k 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. ...
0
votes
2answers
363 views
Data validation - require nearby cell not be blank
I'm trying to create a "comments" field in a worksheet for error-checkers that can only be used when certain errors are flagged as present. (by writing a whole number in the relevant cell)
I've tried ...
1
vote
2answers
67 views
Excel: how to convert a table with daily dates to weeks
Have an excel file that tracks daily dates against a daily metric.
What I'd like to do is convert the table into weekly metrics. Is there an easy way to do this, perhaps with pivot tables?
0
votes
2answers
63 views
Conditional count
I have 500 numbers stored in column A.
Which formula should I use to count the number of those that satisfy the condition 6<X<8? (I'm new to Excel so please give me instructions in detail.)
1
vote
2answers
56 views
IF command query
I'm trying to calculate different percentages for the same cell. Let's say for arguments sake it's cell A1:
If A1 < 50000 then multiply by 10%
If A1 > 50000 then multiply by 12.5%
If A1 ...
0
votes
2answers
67 views
How to count number of rows excluding rows with duplicate data
I have a table in which the column "Doc. No." has duplicate info as below; please advise on which formula IÂ should use to count the number of unique Doc. that I have (as reference to the below table ...
0
votes
1answer
33 views
Issue with SUMPRODUCT Function
I am Having a issue with the SUMPRODUCT Function in Excel 2007. Here is my string and issue:
=SUMPRODUCT(($B$2:$B$800>DATE(2013,4,1))*($B$2:$B$800< DATE(2013,4,5)))
A B
1 4/1/2013
2 ...
1
vote
2answers
96 views
Calculation of number of users in a slab
I have a excel sheet where i need to calculate based on numbers in a slab, for instance:
1-10 users @ $1
11-20 users @ $0.8
21-200 users @ $0.5
201 users or more @ $0.3
I have a feeling this has ...
0
votes
1answer
78 views
In Excel I need to find data matches between two arrays - one horizontal and one vertical
In Excel 2010 (or 2007 - I have both, although my OS is only Win7 32 bit as a limitation for some legacy applications we run), I need to find how I can find and return the matching value from two data ...
0
votes
1answer
30 views
How to customize Paste Value Excel Short Cut
I was reading in this other post about how to set up a macro to past values in Excel. I understand how to create he macro but I would like to set the keyboard shortcut to Ctrl d but excel pops up an ...
0
votes
2answers
97 views
How do I load external images into Excel? [duplicate]
I have a table with a column of links (URLs) to images. I want to load the linked images into the next cell over.
For example:
A1 contains
http://example.com/image.jpg
I want to load the image ...
2
votes
1answer
45 views
Calculating mean of value across random trials/refreshes in Excel?
Say I have a 1000 cells filled with random values and a particular cell that computes a final answer based on those 1000 values. Now I wish to track the value of the answer over 1000 trials i.e., I ...
1
vote
1answer
36 views
Importing images into cells
I have designed a PC program using Profound UI. PUI presents IBM iSeries software to the user in a Windows format. One program is for recording inventory of items that are moving from one location ...
0
votes
2answers
551 views
Excel Data Organization: Array Formulas? Tables? Named Range?
I'm trying to make a huge Excel sheet reasonably maintainable, but it's huge in the "hundred-table-db" direction, rather than the "hundred-thousand-row-table" direction.
I want to have a baseline ...
5
votes
2answers
41 views
Split Excel column with XML inside
Is there a way to break an Excel column containing XML into several
columns like this:
BEFORE
| Apples | Pears | XML |
| ------ | ----- | ...
13
votes
8answers
33k views
Excel displays ### for long text - what's wrong?
We've got an excel sheet with a pretty long text in one cell. Instead of line breaks (as set in the cell format), we get a long line of ####s. We can go back and forth by deleting and adding chars at ...
15
votes
3answers
29k views
How to remove the print preview lines in Excel 2010?
Somehow or other I have managed to switch on the print margin lines in Excel 2010. I cannot seem to turn them off. I went to Page Layout->Page Setup->Print Area->Clear Print Area but the lines remain ...
0
votes
1answer
977 views
Finding Data Tables in Excel
I have inherited a spreadsheet which contains several data tables. I know this because when I recalculate the spreadsheet takes time to recalculate the tables, and shows this in the recalculation ...
4
votes
3answers
64 views
Check if Excel cell text is numeric using formula only
I have to find out if my cells text is a numeric value and wanted to use an elegant non VBA method that doesn't impede on its current state or value.
What I've found is that the ISNUMBER() function ...
0
votes
1answer
26 views
Individual Columns Password Protected
Can individual columns be password protected for separate people, so that others can view information, but only change data in their column?
0
votes
1answer
30 views
Excel: Select all that are not in a set of values (and delete)
I have a list of zip codes, but only a handful of them are needed.
In Excel, how do you select all rows which do not meet (or inversely meet) a set of criteria?
0
votes
0answers
21 views
Is it possible to use alt-XYZ shortcuts on Excel on OS X?
I don't want to know the equivalents -- I want to use the same commands. I'm switching between windows at work and a mac at home.
0
votes
1answer
20 views
Custom Chart Axis
I have an Excel chart whose X-axis values are 5000, 10000, 15000 and so-on but I want them to be like 1,2,3,... i.e. divide them by 5000.
Is there any way I can accomplish this?
16
votes
11answers
39k views
In Excel format number with optional decimal places
Simple: I have numbers in cells in excel. I want the numbers formatted so that if they have decimal places they show to a maximum of 2DP and if they have no decimal places it doesn't show any.
For ...
-2
votes
0answers
55 views
Summarising Excel data
I have two rows of data, one in column A containing amounts and column B containing a text description obtained from a drop down list using the Data Validation -List Function, ie Text1, Text2, Text 3 ...
0
votes
1answer
45 views
Formula to search array and return values
I have a Quotation Book and an Order Book each with a column called Q#. In the Quotation Book the quote numbers are generated and in the Order Book quote numbers are entered without being in order.
...
