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
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
32 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 ...
-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.
...
0
votes
1answer
18 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?
-1
votes
0answers
103 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 ...
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.
1
vote
1answer
32 views
Excel: Highlight all instances of a phrase like in notepad++
If I select a phrase in notepad++ it will highlight all other instances of that phrase.
Is there a way to do this in Excel as well?
Thanks for your help!
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?
-1
votes
0answers
42 views
Offset and if statement combo
Cell C4 contains the formula,
{=IF(AVERAGE(N(OFFSET($A4,0,LARGE(COLUMN(A:BC)*ISNUMBER(A4:BC4)-1,{1,2}))))>=9.5,"Review 4 Wks","Review 2 Wks")}
The last score is 8.0, in cell AD4. C4 returns ...
2
votes
4answers
66 views
Date values from text
I have two text fields with dates. I want to convert them to excel dates and subract one from the other
eg:
cell 1: Wed 18 Feb 2015 18:00
Cell 2: Mon 16 Feb 2015 07:00
Giving 59 hrs (59:00:00 will ...
1
vote
3answers
60 views
How do I sum columns of data across a spreadsheet into a column on a separate sheet?
Example: On one spreadsheet, I have columns and rows of data (A1 through G10). On a separate spreadsheet, I am trying to get the sum of each of the columns from the first into a column running down ...
2
votes
3answers
53 views
Shifting/Justifying across cells in Excel
I have an excel table with the following formatting:
1| Line 1 | Line 2 | Line 3 | Line 4 |
2|123 Main Street| Apartment 1 | NY, NY 10000 | |
3| ...
0
votes
1answer
34 views
Focusing on particular information within a cell when doing a calculation
When performing a calculation using two cells in Excel, is there a way to focus on specific information within a cell, when entering the formula?
For example, I have two time cells (start time and ...
0
votes
0answers
48 views
How can I copy/move values below the matching pattern to another column?
I have found this script from this site which moves matching cell values to another column.
Sub MoveData()
Dim myrange, cell As Range
Set myrange = ActiveSheet.Range("B:B", ...
1
vote
1answer
33 views
How to group daily data by month
Have a somewhat unusual Excel question:
I have a column of data that only has values of -1, 1, or 0:
| X |
| 1 |
| 0 |
|-1 |
| 1 |
| 0 |
| 1 |
I also have daily dates associated with each row, ...
1
vote
2answers
44 views
Can I use Microsoft Excel to convert a “ranged” table to a full breakdown table?
Suppose I have a spreadsheet with the following rows and columns:
QTY MIN | QTY MAX | 1 Color | 2 Color | 3 Color | 4 Color
1 | 10 | $1.00 | $1.50 | $1.75 | $1.90
11 | 20 ...
2
votes
2answers
36 views
Excel 2010/13 and column grouping : how to have the toggle button on the left?
When you group columns, there is a toggle button on top to fold/unfold the group.
This button by default appears on the right, which means that when you unfold the group, it jumps. So that you can't ...
1
vote
0answers
90 views
Excel 2007: Intermittent 0xc0000005 crashes
Hello there SuperUser Hive Mind.
We are having an issue at the office I work. One of our users who has a workflow which consists mostly of Excel documents with dynamic formatting and macros which she ...
0
votes
2answers
139 views
How to open xlsx files with Excel 2013 by default on windows 8?
My xslx files open systematically with Excel 2010 when I'd like them to use Excel 2013.
Everytime I
right click
select open with
point to C:\Program Files\Microsoft Office ...
-1
votes
1answer
26 views
Automatic borders in Excel 2010
I have a worksheet that automatically colors a row when column A is filled with certain text. What I want is to also have a thick black border appear above each colored cell, and not appear above any ...
0
votes
1answer
14 views
Excel/Calc: Pivot table with (almost) identical row: how to identify them?
I need to examine via pivot a datasource from a sheet. This datasource sheet is something like this:
| Field 1 | Field 1 | Field 3 |
| AAAA | BBBB | CCCC |
| DDDD | EEEE | FFFF |
| GGGG ...
0
votes
2answers
62 views
excel: how to leverage vlookup with count
I have some data in columns, several binary variables:
| A | B | C |
| 0 | 1 | 1 |
| 1 | 0 | 1 |
| 0 | 0 | 1 |
| 0 | 1 | 0 |
| 0 | 0 | 1 |
| 1 | 1 | 0 |
and then another variable with three values:
...
2
votes
2answers
196 views
How to implement MATCH and INDEX functions together in Excel VBA?
I am trying to find the row number of the first cell that is less than zero.
So, by browsing through the net I was able to come up with this formula which works perfectly fine:
...
1
vote
1answer
35 views
Exporting Data into a form type format from Microsoft Access query to Microsoft Excel Worksheet
Thanks for any help you can provide. I would prefer not to use VBA if possible, but will if necessary. I have MS Office 2007. I have a query which holds information for products that need user ...
1
vote
1answer
48 views
Using an array formula to add up several cells in another worksheet (using INDIRECT)
I have an Excel 2007 workbook. On one worksheet, I want to add up several regularly spaced values on another worksheet. I can easily do this with a very long sum formula without an array formula but ...
0
votes
1answer
30 views
Prevent Excel from converting text to columns
When I'm using Excel to parse multiple documents, and I need to control the import (Text, not General, on column import settings being the most notable example), how can I prevent Excel from ...
0
votes
1answer
47 views
Automatically updating a workbook from another workbook
I have a master workbook which I use to store all the client information I have available but I also need to generate other spreadsheets (preferably in separate files) with sub sections of the data in ...
0
votes
0answers
36 views
Frozen text box in excel
I just learned how to add a text box to a cell and enable a scroll down bar as I had too much text in a cell (from this question To much text for an Excel cell).
I created my spreadsheet and added my ...
4
votes
1answer
67 views
Preventing Excel from automatically creating a range when entering + or * in formulas
Often, when I am entering formulas in Excel 2010, as soon as I enter a cell reference, and then a + or * to add or multiply things together, Excel will make some kind of correction that creates a ...
1
vote
2answers
93 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
58 views
Microsoft Excel value lookup, return result in an undefined number of row above
I know how to use the vlookup function to search my worksheet to return the value. However, what if the value that I want is in an undefined number of row above.
Example below, if I were to search ...
0
votes
1answer
52 views
How do I add values in column B if column A contains the same employee surname more than once?
State of Data: I have column A which has names of employes and column B has number of hours. How do I add the hours if column A contains same employee surname more than once?
I have been using what I ...
0
votes
1answer
28 views
Conditional Data Type based on Cell Value
I know that you can conditionally format the visual treatment of a cell using conditional formatting, but can I control the data type of a cell based on the value of another cell?
I'm building an ...
0
votes
3answers
95 views
CountIf Formula that counts cells containing any and all dates without having a date range?
I need to simplify a CountIF formula.
Instead of having a date range I just need my COUNTIF to count cells cointaining dates disregarding date range period.
I have tried for example simplifying it ...
0
votes
1answer
29 views
How to check for specific values in a column
I have a column that I'm working with that has a number of possible text options in it. The only two I care about are "Bad" or "Good."
I'd like to write a function that results in 1 for "Good," -1 ...
0
votes
1answer
27 views
Create a pivot table by joining two tables
I have two tables, each on its own worksheet, that are linked to Sharepoint lists, where one table has a foreign key that maps to the other's primary key. E.g.:
sheet: flavors
...
0
votes
1answer
21 views
How to fix timestamp issue in Excel Graph?
I am working on making a scatter plot in Excel of some temperature data. I have
column A containing the date data as mm/dd/yy.
column B containing the time data as hh:mm:ss AM/PM
and the rest of the ...
2
votes
1answer
42 views
How to make a pivot table with two sets of columns, each filtered differently
Suppose I have the following Excel data:
+------+-------+--------+
| Item | User | Status |
+------+-------+--------+
| MTB | Bob | A |
| QCD | Bob | A |
| MTB | Alice | B |
| ...
0
votes
1answer
63 views
Cannot share an Excel 2010 file on SharePoint Foundation 2013
I have a .xlsx file on a network drive and using the Share Workbook feature without any problem.
But when I try to use this feature with a file stored on SharePoint, it doesn't work. When [User A] ...
0
votes
2answers
20 views
Save data exactly, at opening csv file in excel
I have csv file which contains data 0001,0002,0003
When I open this file in excel, starting nulls are removed
Its possible to save starting nulls at opening file in excel ?
4
votes
2answers
39 views
Highlight row if date is in current week
I'm working on a data table for finances. I want to highlight the row if the date is in the current week:
What it should look like:
The current condition formatting for the yellow fill spans ...
1
vote
1answer
38 views
Excel 2010 save as webpage - Want Japanese characters in HTML file instead of Unicode
For programming purposes (Objective-C) I need to export Japanese characters as a HTML file from Excel so I can parse data into my project. I'm using Excel 2010. For example:
When I open up the ...
0
votes
1answer
86 views
Excel Time Subtraction
I think there is a way around the time subtraction issue I have in Excel. The problem is that when the time in cell F (for example 11:29am) is less than the one in cell A (for example 11:30am), the ...
0
votes
2answers
37 views
Find and Replace to add text to end of cell data
Is is possible to do a find/replace on all cells within a calendar style worksheet that start with a consistent text, then has varying text or digits, to add additional text onto the end of the ...
0
votes
1answer
66 views
Data Source not found in Excel (Data/From other sources/From MS Query) connecting to PostgreSQL
I set up an ODBC connection to a datamart with c:\windows\SysWOW64\odbcad32.exe.
I set up my Data Sources in the System DSN tab using the PostgreSQL ANSI driver.
When I go into Excel bring in the ...
0
votes
1answer
54 views
How to make a button that will send all values from one table to a row in another table
I'm not even sure if this is possible, and Google is turning up nothing for me. I have a table with data in C6:F12,G6:G8. I want to insert a button that will send all of this data into a single NEW ...
0
votes
1answer
59 views
Excel - 2 Drop down lists to output a row-column intersection cell value to a third cell
I'm creating a spreadsheet for calculating costs of crafting in a game to determine profit ratios. There is a section of my spreadsheet where I would like to have a two drop down lists. In the first ...
0
votes
3answers
61 views
How do I apply formulas to subtotal rows?
I have a spreadsheet with many subtotals and a variable number of rows between them. If I show only the subtotal lines, add a formula and drag it down, Excel adds the formula to all lines not just the ...
0
votes
1answer
26 views
Using Excel, is there a way to check two SS for the same element info?
For example what I'm doing is seeing if there are duplicate WORKORDER NUMBERS in two different spreadsheets.
What I'm doing now is copying a WO # from one worksheet and selecting FIND on the other to ...
