# Tagged Questions

The 2010 version of Microsoft's spreadsheet application.

**0**

votes

**2**answers

11 views

### Excel Count number of cells based on three dependent selects

I am looking to count the number of cells based on three dependent select statements. each select corresponds to a column.
For example,
Select nth-Day (Column A)
Select Issue Type (Column B)
...

**0**

votes

**1**answer

23 views

### Why do I get HEX2BIN() error for long (but valid) strings?

In Excel 2010, the formula =hex2bin("111",10) gives the right answer of 0100010001. According to the function help, the arguments should be at most 40 bits (10 hex digits). However, the following ...

**0**

votes

**1**answer

26 views

### Excel SUM function for whole column?

How can I sum the whole column without freeze head?
I have a situation: I need to SUM each column, but the total rows in each column will keep changing. users are keep adding data in each columns, ...

**0**

votes

**4**answers

4 views

### How to refer to cells in other sheets

I have a workbook contains several sheets. And i'm trying to use cell value that present in other sheet . How do i refer or use cell value in other sheet.

**-1**

votes

**1**answer

24 views

### Custom Data Validation in Excel 2010

I'm trying to make error messages appear when someone tries to enter the wrong data type in a certain cell with data validation in Excel 2010. Is there a feature or some sort of formula I can use?
...

**0**

votes

**1**answer

21 views

### Excel date time find max / min and max min timespan

I have data time data in excel in the format "04/18/2014 17:09:09". I would like to use MAX and MIN on a range of such dates to get max, min and time span between min-max.
I tried using custom format ...

**0**

votes

**1**answer

9 views

### Excel formatting not working for some cells when trying to do center alignment

I have an excel file that has been exported from a SQL Server Reporting Services report. The cells in the first column are a list of store numbers and should all be center aligned but for some reason ...

**-1**

votes

**1**answer

27 views

### how to copy data from on cell to diferrent cell using command button

for example the first thing i put in cell b1 has "='Ex-post'!C172"which has value "1" and c1 has "=OFFSET('Ex-post'!$B$4,MATCH($H$1,'Ex-post'!$B$5:$B$542, 0),4)" in which the value is 2300 and i want ...

**1**

vote

**0**answers

21 views

### Excel Need to Check one Sheet for Data if True take data and paste in field

I am doing inventory for my company but need to use last years inventory prices in this years quarterly spreadsheet. There are only 3 Information fields. SKU, Qty, and Cost. In the master it has SKUS ...

**-1**

votes

**1**answer

33 views

### Excel - Concatenate words separated by “_”

I need help with the following problem:
My column in A2 in Excel has the value: TB_PLACES_NAMES
But I need to separate the "_" from the others words in the same column.
I've tried this function:
...

**0**

votes

**1**answer

22 views

### How to replace characters?

I have numeric data such as 8,1, 9,2 etc. and I need to use this data on Weka Data Mining program. So I need to replace commas with dots (8,1 --> 8.1). I have selected data and replaced with dots(Ctrl ...

**0**

votes

**1**answer

13 views

### Calculating the sum for a chosen date

I have a range of dates & a drop down in cell B1
What you need to do is write a formula / macro / list of formulas to arrive at the total LIH target till the date chosen (B1)
Example if I chose ...

**0**

votes

**2**answers

30 views

### Excel: Applying Condittional formatting to every other row

I would like to apply a specific conditional format to every second row in my spreadsheet. In my specific case I have regression results, where the first row indicates the beta coefficient and the ...

**0**

votes

**1**answer

17 views

### Excel chart with a single x-axis but two different ranges (combining horizontal clustered bar and horizontal stacked bar), possible?

I am new here. I have a data set of density values (phytoplankton community) according to depth and the composition of it in percentage. I want to ask if it is possible to construct the density values ...

**1**

vote

**1**answer

14 views

### Microsoft Excel lookup match without sorting data

Have a value list and need to insert points by looking up the data table. Data table has value for points in 1000 increments. Actual value that I lookup will not be rounded to 1000 so I tried using ...

**2**

votes

**1**answer

33 views

### Excel 2010 Pivot Formatting - Can't indent and wrap fields?

When working with pivot tables in Excel 2010, I want to show my pivot in compact form, with each field being indented as you move through the data, like so:
However, some of my data strings are ...

**0**

votes

**2**answers

27 views

### Find last instance of a character in string and keep anything that comes before

I have the following data and I need to get rid of the numbers. This means I will have to find that last occurence of the character " " and keep everything that comes before however I dont know how to ...

**0**

votes

**2**answers

25 views

### Excel 2010 - how to prepend a “0” to make an 8 digit number 9 digits

I'm running Excel 2010 using a output file from our database software that exports a list of our affiliate stores. The affiliate stores have a 9 digit code and a number of these codes start with "0".
...

**0**

votes

**1**answer

17 views

### Excel - Quickly Charting Frequency of Event To IP Address Over Time

Warning: Keywords and tricky phrases may not be correctly being used here. I'm not extremely knowlegable about the terms used with Microsoft Excel so I'm using my best guess.
I'm failing to find a ...

**0**

votes

**1**answer

19 views

### Linking files on a network drive

have two excels files on a network drive. Linked file path: Z:\A\ABC\a.xlsx Source file: Z:A\b.xlsx The link from source to linked cell is by clicking the appropriate reference cell in source. For ...

**1**

vote

**1**answer

30 views

### How do I create a dynamically-growing graph which ignores empty tail cells?

I have a table on an Excel 2010 Worksheet 'Consolidated Data' where:
Column A is filled with arbitrary dates sorted by increasing age
Column F has numeric data relevant to this question
Row 1 is the ...

**0**

votes

**1**answer

14 views

### Extracting multiple numerical values from mixed characters in excel worksheet

I have a column containing cells with two numerical values, separated by an asterix...
e.g. 12.5*3.4
How can i extract the smaller number of the two into one new column, and the larger of the two ...

**0**

votes

**1**answer

22 views

### Control CSV import in excel 2010 [duplicate]

I have a csv with values like this:
"a very long value, with a comma","387621937291732193"
The number, despite being enclosed in quotes gets turned into a number in excel and shows up in scientific ...

**0**

votes

**1**answer

20 views

### COUNTIFS using AND / OR

I need to count the number of records where any one of several conditions could be met in column G but a specific condition must be met in column I.
For example, column G could be either "Toyota" or ...

**0**

votes

**1**answer

20 views

### Finding the right formula

I have a spreadsheet set up and used by one of my contractors, there is certain data that I need pulled from it, ie: the number of people per craft. Said spreadsheet is used for contractors payroll ...

**0**

votes

**2**answers

29 views

### Graphing patient data, multiple dates, multiple patients

In Excel, is there a way to graph multiple series from one column of data? I have lab values for patients. Column A is date, column B is patient identifier, and column C contains all the lab values. ...

**0**

votes

**2**answers

30 views

### Cannot delete created excel table

I have created an excel table. However, I now want to delete or change it because my range changed.
However I can't delete it in the excel Name Manager:
Any suggestion why I cannot delete it?
...

**1**

vote

**2**answers

21 views

### How can I stop Excel From Updating Links When Copy and Pasting Cells Linked to Other Workbooks?

Summary of the problem: I would like excel NOT to link to the spreadsheets links I'm copy and pasting until I have the entire spreadsheet setup.
Overview: I have a worksheet that acts to aggregate a ...

**1**

vote

**1**answer

35 views

### When pasting, how can I ignore hidden columns in the destination range?

I have two worksheets s1 and s2.
Data on s1 looks like:
+---+-----------+--------+------+
| | A | B | C |
+---+-----------+--------+------+
| 1 | scarecrow | tinman | lion |
...

**0**

votes

**2**answers

26 views

### Excel: DD/MM/YYYY stored as text…need to convert to Date format

I have a slight problem. It would be easier to post an image but I can't because I don't have 10 reputation. Anyway I have a column with the heading "Birth Date", and dates entered in DD/MM/YYYY ...

**2**

votes

**0**answers

40 views

### Automatic scatter plot in Excel 2010

I have two quite large data sets of which I want to plot every line in one plot.
The pattern is the following:
x-axis: Set one (Colonne 1-6)
y-axis: Set two (Colonne 1-6)
Now I want such a plot for ...

**0**

votes

**1**answer

20 views

### How to load above 2 million data from SQL Server to CSV/Excel using SSIS 2008R2 package?

How to load above 2 million data from SQL Server to CSV/Excel using SSIS 2008R2 package?
The Package needs to create new excel/csv file automatically once the first file is filled with maximum number ...

**1**

vote

**2**answers

55 views

### Excel Lookup adds double quotes to string

I'm using this formula to get value form a table
=LOOKUP(C11;Alias[A];Alias[B])
where C11 = 12354567
But when I use
=LOOKUP(LEFT(C11,7);Alias[A];Alias[B])
where C11 = 12345678, the formula does ...

**1**

vote

**3**answers

31 views

### Replace Alphabet with Number

Is it possible I write A and excel count it as 5,and on the same cell I write B and excel count it 4, all the way through to F (where F = 0).
Actually at the end I want to add the numeric values.
...

**0**

votes

**1**answer

40 views

### Compare two spreadsheets in a workbook, find the differences and record the differences on the first spreadsheet

I need a way to compare two spreadsheets in a workbook, find the differences, then write those differences on the spreadsheet in sequential order in the same column below the existing numbers.
Sheet1 ...

**1**

vote

**2**answers

48 views

### Using Excel to make a grid in millimeters

Trying to figure out a way to make a grid in Excel that will print out in with spacing of mm's. Haven't figured out a way to do this yet though.
I'm doing some work with Nanoblocks (which a single ...

**1**

vote

**0**answers

31 views

### How to add custom columns to Pivot Table (similar to Grand Total)?

I have data which I pivot as Row Labels and Column Labels which gives me something like this:
And what I would like to do is add columns like Grand Total (i.e. which applies to all columns as a ...

**0**

votes

**1**answer

37 views

### How does Excel handle the h:mm:ss format?

I have the number 0.00082788917980126 which once converted to h:mm:ss format gives me 0:01:12, and I need to understand how as I need to perform similar formatting outside Excel, hence my question:
...

**0**

votes

**1**answer

32 views

### Defined Name to give text or date value instead of numeric value

I had been trying out Define Name in Excel. I defined a name for a cell in which I would put a text or date value or I may leave it blank.
But when I use this defined name in another cell, the second ...

**0**

votes

**1**answer

15 views

### Nesting multiple IFERRORS with Index and Match

I feel like a dummy. I basically need a column that says - If column a2=0 then index and match column C, if that returns an error index and match column e, else index and match column a
Current ...

**1**

vote

**2**answers

42 views

### Align Secondary Axis Origin with Primary

I am interested in aligning my secondary axis such that it lines up exactly with my primary axis.
To be more clear, I want the origins to both line up at 0 at the same place, each preserving their ...

**1**

vote

**2**answers

27 views

### Simple, Excel, =LOOKUP(xxxxx), not returning the correct value

OK this is infuriating as I know this should work...
I have certain traits in column A. Each trait has a corresponding value in column B and a numeric indicator of that value in column C. Like ...

**0**

votes

**1**answer

25 views

+50

### Format Changing when copy into email Excel + Outlook

Okay this is starting to confuse me a lot, when I copy my excel spreadsheet into an email in outlook, (not attaching just selecting all cells with values) the cells in the side table change to some ...

**0**

votes

**1**answer

21 views

### Excel, how to subtract time to span before midnight

I am trying to subtract hours to a time that is close to midnight, but excel is outputting all hash (#) symbols complaining of the operation.
Adding time is easy and possible, as described by this ...

**2**

votes

**1**answer

46 views

### How to Make a PivotTable out of “hierarchical” Excel spreadsheet data

I would like to create a PivotTable out of "hierarchical" data contained in an Excel 2010 worksheet. The data is hierarchical in the sense of a parent/child relationship in a database where, ...

**0**

votes

**1**answer

44 views

### Excel: How to clear all renamed PivotTable labels

When using PivotTables in Excel, it's often useful to overtype certain PivotTable labels with new names.
If you have a long list of items you've relabelled, it can be easy to lose track of what the ...

**2**

votes

**2**answers

36 views

### Referencing a blank cell - should act like a blank cell

I have a spreadsheet that referencing a blank cell on another tab. I use the iif(isblank(thatcell),"",thatcell). This works fine, until I use that cell to calculate. When I include that cell to ...

**0**

votes

**0**answers

20 views

### Convert OO4 ODS to XLSX using Excel 2010 VBA

I am trying to bulk convert OpenOffice documents to Microsoft Office. Using built-in VBA, I use the following code (ODS to XLSX is shown, but same code is being used for ODT and ODP with some ...

**1**

vote

**1**answer

33 views

### Find the last data entered in rows in Microsoft Excel 2010

I have a spreadsheet of clients in column A and each week along the top row. When a client attends I put a corresponding "1" in the week they attended.
I would like to insert a column that finds the ...

**0**

votes

**1**answer

27 views

### Nesting the IF Function in Excel when there may be #Value! errors in the target cell

I'm trying to get some help to change a formula:
=IF(S4>8000,3,IF(S4>6000,2,IF(S4>4000,1,IF(S4<4000,0))))
So that it returns a value of 1.5 if the target cell contains an error value. ...