# Questions tagged [microsoft-excel-2010]

The 2010 version of Microsoft's spreadsheet application.

**-1**

votes

**0**answers

27 views

### Excel Formula referencing IFERROR with blank gives #VALUE error

I have come across an issue with using IFERROR to return a blank. When I have another cell referencing this blank one to SUM values I get a #VALUE error in that cell.
I assume it is because the "" ...

**0**

votes

**1**answer

21 views

### How to multiply a row of values to a table with the same number of fields?

I have a row of value from A2:H2 and then I have a table of values from B7:E8 (both the table and row have 8 data points).
How do I go about multiplying them? Please see the bellow illustration:
+---...

**0**

votes

**1**answer

20 views

### Excel 2010: Create a column chart that groups items and then charts them by year

I am having no end of angst trying to build what I thought would be a simple column chart. My data are in three columns: Player in Column "A", Year (from 2005-17) in "B", Points for each year in "C". ...

**0**

votes

**1**answer

41 views

### Excel Conditional Format: If cell A10=“Highlight” then highlight all values in Column B that match cell B10

I have a dropdown box in column A. Lets say I'm working row 10. I select "Highlight" in cell A10. When I do that, I would like a conditional format that highlights any cells in column B that match ...

**-1**

votes

**0**answers

12 views

### Importing particular information from multiple word documents into excel

I need to scan hundreds of word documents and turn them into a database. The task is to have each word document into a row with the filename as the first column, then get excel to return a binary ...

**0**

votes

**0**answers

8 views

### 2010 Excel #REF when referencing table in same workbook

Excel - 2010:
I have formulas referencing a table on a sheet in the same workbook. Every time I close the file and reopen it, I get a #NAME? in the cells. Inside the formula I have a #REF error for ...

**0**

votes

**1**answer

26 views

### How does Excel determine default cell formula when a new line is added to a table?

It appears as though a certain column in my table has established a default formula that I can't seem to reset. When I add a new row to the table, the cell in that column always puts this default ...

**1**

vote

**1**answer

25 views

### Turn result based on column with date

I have a column with dates. Those dates I want to assign to new columns "attended" and "upcoming", based on past 5 working days and upcoming 5 working days.
I want to have following results shown:
...

**0**

votes

**2**answers

41 views

### Is there a quick way to do a common date-time format in Excel?

Can someone please help me find, built into Excel (any version) a ready-made format for Date/Time like:
MM/dd/yyyy hh:mm AM/PM
I can't imagine this is not a common format? 10/05/2018 05:03 PM
I like ...

**1**

vote

**0**answers

13 views

### Separate old files with date information stored in excel

I have approximately 2000 files, of which I need to separate any files before a specific date (say, 2015).
These are currently separated into subfolders for each categories of file (Say, projecttype1,...

**0**

votes

**1**answer

31 views

### Excel Autofill with defined steps

Disclaimer: I think similar questions have been asked before, but the answers were all quite complicated, involving special cases or index functions etc.
Is it possible to autofill a formula were the ...

**0**

votes

**3**answers

33 views

### Excel IF Formula to search rows less than a specific date

I have a very large spreadsheet with many dates in each row with the DD-MMM-YYYY format. I would like to return a 'YES' for all rows with dates less than a certain date (which will change) and return ...

**0**

votes

**0**answers

30 views

### Excel 2010: Pivot table: insert calculated fields with sum or average columns

I have a problem with calculating in an pivot table (insert calculated fields).
I would like to calculate with two value columns. One is an average column and the other one a sum. These two columns ...

**0**

votes

**0**answers

14 views

### Excel 2010 on Windows 10 does not save changes

I got reports from Excel 2010 and 2013 users that they open some Excel document, make changes (add, delete or update), save the document and then probably send it through Outlook. The problem is when ...

**1**

vote

**1**answer

60 views

### Why not getting proper sorted name list

The Array Formula I've used in H104 is,
{=INDEX($E$104:$E$111, MATCH(SMALL(IF(COUNTIF($F$104:$F$111, $F$104:$F$111)=1, COUNTIF($E$104:$E$111, "<"&$E$104:$E$111), ""), ROWS($H$103:H103)), IF(...

**1**

vote

**1**answer

30 views

### Speed up Excel Index Match - First Instance

I'm using the below formula to look up the first instance of a name appearing in an email (Column L). I have around 40,000 rows and the formula is getting slower and slower as new rows (emails) come ...

**2**

votes

**1**answer

41 views

### Create many hyperlinked cells with readable text content instead of literal URL

Generally speaking, I'm trying to create an MS Excel spreadsheet with hundreds of rows of related URLs. Column A should contain human-readable text, but the cell text is hyperlinked to an HTTP URL. ...

**0**

votes

**0**answers

16 views

### Reorder spreadsheet based on values in two columns

I have an Excel 2010 spreadsheet with lots of product data across many columns and 20,000 rows. Two of the columns are for parent and child categories.
A product with different sizes or colours will ...

**0**

votes

**0**answers

28 views

### Macros Created in Excel 2016 Not Working in Excel 2010

I create a file with the same macro in Excel 2016 to send out to people in my company. Users on Excel 2010 were able to use the macro last month, but when I sent them a file with the same macro today, ...

**1**

vote

**1**answer

39 views

### Excel bar chart widths displaying incorrectly

Using the following data to create a bar chart displays incorrect bar widths.
Yes No
a 7 0
b 7 0
c 6 1
Shows
As you can see, the widths for c are incorrect. The 'No' column is ...

**1**

vote

**2**answers

39 views

### How to automatically delete blank row/cells when export to other worksheet?

I have a few raw data files which some row/cell does not have data. How do I automatically delete those empty row/cells when I move/export the data to other work sheet. what formula should I use to do ...

**1**

vote

**2**answers

39 views

### Any keyboard shortcut to navigate to the most recent worksheet in Microsoft Excel 2010

I'm working with a workbook which has got 11 worksheets. I have frequently switch between worksheets. Mostly I work between different pairs of worksheets. If I use the mouse, sometimes I have to ...

**-1**

votes

**0**answers

31 views

### Microsoft Excel: Insane slow with thousands of xls-Comments

I'm generating xlsm/xlsx files with Python and writing thousands of xls-Comments to the file and I'm wondering why it's slowing Excel so much down.
There's no problem if I output an Excel-file with ...

**0**

votes

**1**answer

53 views

### Multiple columns into one column without blanks

I'm using Microsoft Excel 2010. I have multiple columns with data in them, and some blank cells too. I'm using this formula:
=INDEX($A$101:$F$200;MOD(ROW()-ROW($A$201)-1;ROWS($A$101:$F$200))+1;INT((...

**0**

votes

**1**answer

64 views

### How to copy color of the conditional formatting cell to another excel file

On one sheet I have a data which has conditional formatting color.
I want to copy and paste that into another file, I was able to paste value, column width etc. but I couldn't paste the color from ...

**0**

votes

**1**answer

44 views

### Excel - Bar chart - years in y-axis and variables in x-axis

I'm trying to chart the following:
I want a bar showing the total fruit
I want the fruit in the Y-axis
The years in the X-axis - so you can visually see the impact per year of each category.
...

**-2**

votes

**1**answer

46 views

### Excel - Show if a cell (in the same column) has a value below it, not a blank

I need some help please, I'm trying to save time as I'm working with 100k+ rows.
I'm trying to find a way of finding out when there is information on 2 consecutive cells, one below the other. I've ...

**0**

votes

**1**answer

53 views

### Excel pivot values column to Text

I have data in Excel pivot and my text data in value column shows number instead of Text . Any one can help me please. In below image of pivot value field red circled:

**-2**

votes

**1**answer

62 views

### HOW TO SEPARATELY VISIBLE A ROW CONTENT BASED ON VALUE-without VB

I have some data from 2A to 9D.
I want a separate box and need to show all details of particular row based on the age I wish.
In the example I want to filter all data of a customer with age 15.(I ...

**0**

votes

**2**answers

69 views

### How to sum numbers in parenthesis within a cell until a certain condition is met? [closed]

Here's the problem, I got a cell, say,
WH,QC-NDE(0.75),CHL150-1(5.05),HMCT12P1(1),B-S(0.2),QC-NDE(0.5),TIGPEC05(17.25),SR,CHL150-1(23),HMCT12P1(42),B-S(1.5),QC,QC-H(0.35),QC-NDE(0.75),INSP_FIN(1)
...

**1**

vote

**3**answers

118 views

### How can I count how much wood a woodchuck chucks using Microsoft Excel?

TL;DR Is there anything like grep in Excel that can help me count whole-word-only instances (e.g. "wood" but not "woodchuck") (and maybe their variations)?
So there's this poem, by mother goose:
...

**18**

votes

**2**answers

2k views

### Excel week number inconsistent results

I am using a spreadsheet that includes the week number for a particular date. After some research, I found a function ISOWEEKNUM() that I could apply to a column of dates and this appeared to work ...

**0**

votes

**1**answer

34 views

### Is there a way to make Microsoft Excel 2010 include the *.csv files in its list of default files in the file open dialogue box

I am working with CSV files a lot and have to frequently navigate to different directories that contain CSV files. Whenever I press Ctrl + O to open a CSV file, Microsoft Excel 2010 will not list any ...

**0**

votes

**1**answer

40 views

### Replacement for 'sum_range' in SUMIFS formula that can match automatically with source data header

Need your help, through SUMIFS formula, I need the result to be like the one in D5 column. From the formula box in picture, we can see that I wanted to know the ad cost between July 6 to July 8 which ...

**0**

votes

**1**answer

22 views

### CSV Not able append text as some rows understood as text and some rows and number

I have a excel with a one column. That is imported from Mangento tool.
One of the row value is
7,81,90
and one of the row value is
71,08,19,11,31,614
First row understood as text however ...

**0**

votes

**2**answers

41 views

### How do I use a conditional array with more than one condition to calculate percentiles?

I have a table in excel with the following data:
+-------------------+----------------------+----------+
| Contribution Type | % Contribution Match | % Salary |
+-------------------+-----------------...

**0**

votes

**0**answers

34 views

### Can a subtotal Sum only unique values in a Pivot table?

I created a Pivot table in Excel. The field setting for the data's values is set to Max.
I would like the pivot table subtotal to Sum those Max values, but when I change the subtotal field setting to ...

**0**

votes

**0**answers

22 views

### Alt Key is not working in Excel 2010

Team,
I using Lenovo L560 model laptop . I use some of the Alt combination short cut keys like "Alt+=, Alt+ Down Arrow, Alt+:" none of these combination works for me. When I hit Alt+Tab its working. ...

**1**

vote

**2**answers

37 views

### Excel Formula to subtract cells from one and other whilst ignoring text

I have an extremely basic understanding of excel so please cater your answers accordingly! I need to subtract one cell from another, and then add one to the result, however the I need the formula to ...

**0**

votes

**1**answer

25 views

### compare 2 values within 2 ranges

I have 2 columns with both positive and negative values. Let the columns be A and B. I need to find a condition that matches both values of A and B.
i.e. if -3.485 < a < +2.1254 value and -4....

**1**

vote

**2**answers

64 views

### Find zero value in one column and get value that is above/below in next column

I have a list of temperatures from a sensor which includes reset values of zero when the sensor is turned off.
A B C
res 0
tmp 20,5
tmp 20,3
res 0
tmp 20,4
tmp 20,1
tmp ...

**1**

vote

**2**answers

84 views

### Filtering Excel Data by Column Value and Saving Columns to Individual Files

Many years back we had to come up with a solution to survey results that we were getting via CSV. Back then we'd get data where the first column was e-mails, and subsequent columns were 1's or null ...

**0**

votes

**0**answers

85 views

### Complex or not? Possible or not? Copying text contents based on an IF statement

I have a very complex multi tab spreadsheet I use to mirror the accounting system my office uses. Its my way of reconciling with their monster software.
A B C D
Todd 1.00
So the ...

**0**

votes

**1**answer

62 views

### Macro to fill cells based on conditions [closed]

New user to Excel Macro's here but need to get this task done.
I was wondering how I would possibly go about filling cells with a set number based on a couple of conditions.
I have a spreadsheet ...

**-1**

votes

**2**answers

97 views

### I want to make Excel 2010 change year in the cells was written Date on the way of hand

I have sheet in Excel, with cells having dates like:
1/1/2018
2/1/2018
3/1/2018
4/1/2018
I want to change just the year by writing in any cell like as
2019
So that Excel changes the cells' date ...

**0**

votes

**0**answers

94 views

### How to split multiple worksheets of data by column value and output to separate files?

I have an Excel 2010 workbook (let's call it 'Master') with two worksheets, Sheet1 and Sheet2.
Each worksheet has data structured as shown below. One of the columns (in this example, Column1_header)...

**1**

vote

**1**answer

98 views

### How to get smaller size of an icon for an embedded file by default or by using a macro in Microsoft Excel

I'm using Microsoft Excel 2010 on Windows 7. I have an assignment that requires me to keep on embedding objects of a compressed file into a cell in a Microsoft Excel worksheet every few minutes.
The ...

**3**

votes

**1**answer

65 views

### Trying to use the dates on the first column and have them separated into month, day and year in another column

Trying to use the dates on the first column and have them separated into Month, Day and Year in another column. The dates on the first column has random blank spaces in between and on the results ...

**1**

vote

**0**answers

82 views

### Removing linked workbooks external links in Excel

I have two Excel with the same content but different sizes. If I check with the Excel Add-in called Analyze a workbook with Inquire Add-in (link below) I can see a number of external links that are ...

**1**

vote

**0**answers

42 views

### Autopopulate cells based on values from other sheets

I'd like to auto-fill a cell using references from another sheet.
So, I've got this PO tracking spreadsheet that looks like this:
I'd like to fill the G1 cell with information that is on another ...