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.
0
votes
1answer
14 views
How to count based upon multiple conditions
I am trying to create the following formula
=SUM(IF(AND(K3:K138="Tekken",L3:L138="American Football"),1))
However, it always seems to result in zero, even where a single row would have both values ...
2
votes
1answer
38 views
How to automate transforming a table into a matrix
I've been looking for a way to automate transforming data in an Excel table into a matrix.
For example, from the left table into the matrix on the right, as shown in the following image:
Apparently ...
0
votes
0answers
17 views
VLOOKUP to search multiple values in a Cell
I have a VLOOKUP that looks up an Account # and Returns a Rep. The Account # field contains historical data (every account including inactive ones). How can I setup a VLOOKUP to take to see pull the ...
1
vote
1answer
35 views
Select activation method from dropdown instead of writing it yourself
Answer at bottom of post
I got the code below to work but a question remains. When I copy and paste the code into my project it won't work. But inserting the outline of the call from the dropdown ...
3
votes
0answers
36 views
How can i grep data in excel?
I have an excel file that contains data in two columns like :
Operator ID MXS1268
Name ramesh
I need to check for duplicates in multiple excel files which have the ...
0
votes
1answer
28 views
How to get rid of text overlapping with the arrow buttons when filtered in Excel?
When filtering, my header columns are overlapped by the arrow buttons as in the below snapshot.
How can I get rid of this?
1
vote
1answer
28 views
Excel conditional formatting based on a formula including a range containing the current cell
I want to change the background of a cell as long as the running total in that column from start to that cell is less than a limit specified in a different cell.
Conceptually, the formula should look ...
0
votes
0answers
19 views
Paste html into Excel 2010 without Excel parsing the html
I'm running a query on SQL. Typically I can easily copy and paste the results from the query into Excel and it preserves the columns, rows etc.
However, in this case I'm selecting a bunch of emails ...
0
votes
1answer
33 views
How can I update this macro to only copy columns B thru F onto the new sheet?
I have multiple search strings such as: SearchForString 503, TH00000, XYZ if ColE, ColF and ColC are met I only want to copy columns b,c,d,e & f in that row
How can I update this macro to only ...
-1
votes
0answers
22 views
seriessum() and zero to the zero power, or how to submit an improvement request to MSO support [closed]
The excel function POWER(0,0) returns #num! because mathematically “zero to the zero power” is ill defined.
The most convenient interpretation for “zero to the zero power” is that it results in one. ...
0
votes
0answers
17 views
Why would an Excel VB Auto filter macro work in Excel 2010 but not in Excel 2013
On my laptop (Excel 2010) I have macro that Autofilters a table (looking for one name - variable). when I put the file on my desktop (office 2013) the macro accepts the name into the filter but does ...
0
votes
0answers
29 views
Why does my Excel macro fail when run from a hotkey and work when run from the macros ribbon?
I am really confused by what is going on with a macro I created to parse out space delimited files. The macro runs successfully as long as I select the macros ribbon and the macro from the dialog box. ...
1
vote
1answer
50 views
Any way to have Excel 2010 treat a quoted value “08540” as text not number when opening a CSV
I have a CSV with data like this:
"08540",1,"PRINCETON","NJ"
I know that it is possible to specify "Text" when using the Import Wizard and defining columns. But is there a way to tell Excel to ...
-2
votes
0answers
19 views
Copy Sheet and rename based on two columns in a sheet [closed]
I need a macro for the below mentioned problem. Say for example: I have a column A and B in a sheet and in column A say I have 1,2,3,4,5, and in column B say I have A.b.c.d.e against each. Macro ...
0
votes
1answer
31 views
Format time in 24 hour format instead of AM/PM
When I format a cell in Excel to HH:MM:SS on selecting that cell it's show in 12:50:45 AM.
I need it to display 00:50:45.
1
vote
2answers
27 views
Excel: if duplicate cell value found in another column then highlight green
Can someone help me, I'm not sure what formula to use.
I have highlighted the cell in the picture to show an example of what I mean.
What I want to do is highlight the cell in column A where the ...
0
votes
2answers
60 views
How do I separate three different phone numbers concatenated in one cell into three new rows?
I am doing work for a client and need to know how to separate these phone numbers.
I have
0121 396 0501 / 0121 123 434* / 0797 978 8265
in one cell and I want to end up with the same row ...
0
votes
1answer
30 views
Unintelligible result opening csv in excel
When I open a .csv file in excel this is what is showing up:
Maxtor Encryption ver 2.0.1.1.0 ³§o rkDV’)cÿA®T7B‡LôŠì£\% i¼r1 ØÕë—„×½é¿wjd ´Íâ¿…Öö?HÐ)vM!W« Va<žÒ LåRpµI©ô ...
1
vote
1answer
9 views
MS Excel: custom money format
Need to represent decimal number as "## hrn. ## kop.".
Example:
1.2 => 1 hrn. 20 kop.
3 => 3 hrn. 00 kop.
I've created custom cell format:
0," hrn. "#0" kop."
Result is:
1.2 => 1, hrn. ...
0
votes
1answer
13 views
How can you evaluate this excel expression?
I am trying evaluate the following expression in Excel:
IF(A2:C6="apple",ROW(A2:C6)-ROW(A2)+1,ROW(C6)+1),2)
If apple is contained within the array, the if condition would return
...
2
votes
1answer
31 views
Excel 2010 removes data from clipboard, how can I stop it?
The order of my actions is:
Copy data from pivot table from second sheet.
Paste data as value into first sheet.
Notice that something is wrong in the data.
Go back to the second sheet and change ...
0
votes
2answers
19 views
Line Graph with Date on X axis and occurrence on X axis - Excel
I am trying to produce a Line graph that shows the number of access requests over a given time period to my server.
The log data is like so:
10/05/2013
10/05/2013
10/05/2013
11/05/2013
11/05/2013
...
0
votes
3answers
25 views
Nested IF statement
I'm having a problem with this code for nested =IF statement. The range is basically:
<=16 to get 5 pts
16-17 to get 4 pts
17-18 to get 3 pts
18-19 to get 2 pts
above 19 to get 1 pt
and my code ...
0
votes
1answer
15 views
Excel sorting Server Access logs using dd/month/yyyy
I am collating my server access logs to get a cumulative count of HTTP requests made to the server over a period of a few months.
Access is logged as a date time stamp like so:
...
2
votes
0answers
35 views
Background colors in Excel 2010
I have an Excel 2010 spreadsheet. Right now, the entire background is at 40% gray, and need to be white/transparent.
There's no conditional formatting set.
The worksheet is not locked.
The ...
2
votes
0answers
24 views
How to stop Excel following hyperlinks when I click on them
In Word I can use Ctrl+Click on the rare occasion that I want to follow a link rather than, you know, do the editing that Word is actually for.
Is there some way to get similar behaviour in Excel?
...
0
votes
0answers
23 views
Excel formula to copy text from another sheet1 to sheet 2
I am trying everything but nothing works. Please help me.
In sheet 1, when column 3 contains a specific word (like PTA), it must copy the whole row where that PTA word is in, to sheet 2. And the same ...
0
votes
0answers
25 views
Using Hyperlink in Access to link to excel spreadsheet
I am trying to link a cell in a table in Access to some specific spreadsheets in an Excel workbook.
I used the hyperlink and put the address like this
"My%20workbook.xlsx#Sheet12!a1" ,
it work ...
1
vote
1answer
42 views
Hidden formula in Excel?
I have an Excel file that was created a long time ago, and one of the columns uses a VLOOKUP formula that references a file that does not exist on any computer. Formula:
...
1
vote
2answers
43 views
excel how to replace using wildcard character
in the excel, I have data like
Series_post
Series_get
I want to find and replace it to
<Series_post>
<Series_get>
I tried
find: Series*
replace: <Series*>
But it doesn't ...
0
votes
1answer
41 views
How can I split row data into alphabetical worksheets?
I have an Excel file with one source sheet containing many columns. Column 1 is text and column 2 onwards can be any format.
What I want is 27 more sheets in that same file named #, A, B, ... Y, Z.
...
0
votes
0answers
16 views
Get rid of the warning box when save an Excel macro-enabled file? [duplicate]
When save such .xlsm file, I got the below snapshot warning which is quite annoying every time I hit Ctrl-S to save.
How can I get rid of it?
1
vote
1answer
83 views
How can I copy Excel cell's value & text format via “=” operator?
Given a sample plain Excel file here, how can we have the target cell to have both the value & text format of the source cell rather than the plain text value only?
0
votes
1answer
33 views
Compare two date columns and highlight the newest
I have two date columns in the same sheet. Column B receives its data from a vlookup function.
COLUMN A | COLUMN B
2013-07-05 | 2012-01-01
What I want to do, is to compare the two date columns ...
0
votes
2answers
31 views
CTRL+D in Excel 2010 fills values and formatting, but I want it to only fill values/formulas
When you press CTRL+D in Excel 2007 or 2010 (and probably older versions), it makes an exact copy of the cell above it (modifying cell references, of course). In doing so, it copies everything else ...
0
votes
1answer
45 views
Merge multiple tables by comparing multiple rows
I have multiple three column tables that share two rows in common:
DailyCosts: DailyPrices
CodeRole CodeRegion DailyCost CodeRole CodeRegion DailyPrice
1 1 ...
-3
votes
0answers
19 views
UPC / Excel price comparison tools [closed]
I am looking for a tool (specifically a website or Excel script) that will offer bulk price comparison by UPC code. For example, I have a XLS/CSV with products and UPCs from Amazon and I would like to ...
0
votes
1answer
42 views
excel custom number formatting - only show decimals when there are not whole numbers
Is it possible to format numbers (or currency) in Excel so that there will only be a period when there is not a whole number? For example:
12.43 will appear as 12.43
12 will appear as 12 and not ...
-1
votes
0answers
36 views
Using VBA to automatically assign gender in excel spreadsheet [closed]
I have to do an analysis on an online membership database within excel, but for some reason, some of the cells under the Gender column remain unfilled. I have found moderate success by downloading an ...
2
votes
1answer
31 views
Excel visual basic function across workbooks?
This seems like it should be a common issue, but I couldn't find anything on it, maybe I'm just searching for the wrong thing.
Basically I get sent a lot of workbooks that need specific modifications ...
-4
votes
2answers
42 views
file extension not valid . when it is
I can't open a excel file on my home computer. Every other computer I use opens the file apart from this one I use at home .
I get a error message -
financial.xlsx format is not valid.
This is ...
0
votes
2answers
25 views
Combining two Excel sheets
I ship a lot of items to a lot of stores and I have two Excel sheets. I have one sheet showing how many items I have shipped to each store and another sheet showing how many I have sold in each store. ...
0
votes
1answer
42 views
Sort by selection..spreadsheet software
I am so frustrated... I use ArcMap a lot and therefore I use the 'show selected features' a lot when I am in the attribute table. Now I need to manipulate a csv file in excel with 150,000+ records ...
1
vote
1answer
47 views
Excel copy downwards [duplicate]
In MS Excel, if you select two cells containing text like "x1", "x2" and drag the bottom-right corner of the selection, it continues the pattern "x3", "x4", "x5" etc.
Is there a way to do this using ...
0
votes
0answers
63 views
How do I fix a corrupted XLS file?
I have an excel file that has recently become corrupted. It was created back in 2006 and I was wondering if anyone knows of a quick and easy way to extract the data.
The exact error message is:
...
0
votes
1answer
21 views
Excel 2010 Formula Bar - autofit?
In Excel 2003, the formula bar would autofit the height of the active cell. In Excel 2010, this doesn't happen. Is there a way to revert the behavior to the Excel 2003 functionality without trying ...
1
vote
2answers
25 views
Excel : Grouping based on column values
I have 2 columns in excel
eg:
Component Size
A1 12
A2 5
A1 6
A3 10
A2 4
I need to get the output as:
A1 18 (sum of the values for A1)
...
0
votes
1answer
32 views
Optimally positioning pie chart data labels in Excel with VBA
I have a worksheet where I create a pie chart from data from a database. The chart changes based on the data it is displaying so the slices often change size.
I want to display the data labels ...
-2
votes
0answers
28 views
Rename the sheet name as User_User [closed]
Steps to Reproduce:
Open a Worksheet
Try to rename the Sheet1 as User_User (Success)
Try to Rename the User_User
Start with Spaces for User_User
Actual: _ Symbol is carried with User_User, It ...
0
votes
1answer
43 views
Can I use an excel formula to extract the link location of a hyperlink in a cell?
I have a spreadsheet with a large number of cells containing hyperlinks with display text different than the hyperlink location
ie:
cell location: A1
display text = "Site Info"
hyperlink location ...
