# Questions tagged [microsoft-excel-2010]

The 2010 version of Microsoft's spreadsheet application.

4,703
questions

0
votes

0
answers

16
views

### How to highlight cells based on my defined pattern

Experts, Please help this excel newbie
my sample data
Admin Developer Subscribe
Contributor Product Owner Write
Viewer Stakeholder Create
Test engineer View
...

0
votes

1
answer

21
views

### Use Excel to get only the cycle start date

I have the following data in Excel:
Date
Status of Lab1
Status of Lab2
01-01-2024
On pause
Pausing
02-01-2024
Started
Pausing
03-01-2024
Started
On pause
04-01-2024
Started
On pause
05-01-2024
...

4
votes

1
answer

118
views

### Consolidate Data from Multiple Columns into One Column

I have a dataset that looks something like this:
Customer
Col1
Col2
Col3
Bob
111
2221
Michael
112
Anna
113
2223
Rachel
114
2224
334
Person5
115
I'd like to put all data into one column, ...

0
votes

1
answer

65
views

### Convert single cell into multiple rows in EXCEL

I converted a pdf file to EXCEL for entering data. Problem is, in the pdf there is a single box which contains multiple new lines. Excel puts all of the data into a single cell. I want to make ...

0
votes

1
answer

71
views

### In Excel is there a way to “turn on and off” the =min in a formula?

I am creating a budget spreadsheet in Excel and in some instances I will need the formulas in the spreadsheet to include the =MIN and in some instances I won’t. For example, the formula is:
=MIN(...

-2
votes

2
answers

82
views

### Excel function for filtering a list of substrings to include & exclude

Excel function where if any row values in column A include a keyword from the list in column D but does not include a keyword from the list in column E, write True, otherwise, write nothing/keep it ...

0
votes

1
answer

82
views

### Join/Mix dynamic and static values on Array = alternative to HSTACK that works in older MS Excel version (No VBA, please)

I need to join some value(s) from some cell(s) with a "static" bunch of other values into an Excel array... BUT I'd like to avoid using VBA for it and I need something that works also with ...

0
votes

0
answers

13
views

### How to select random cell in Excel with condition?

I want to select randomly cell in B row with number "1" or "2" in its left cell and put it in C row. What formula should I put in?

0
votes

2
answers

62
views

### Copy paste the values of cells from Excel

I want to copy paste the cell values what we see post pressing F9 in a cell into word. Is there any short and quick way to do this?
For example - if I have a formula:
=AVERAGE(E12/H12,E11/H11,E10/H10)
...

1
vote

1
answer

100
views

### IN VBA, How to sum hours and minutes exceeding 24 in memory (not in ranges of excel)

I'm getting some data in the format of text(like for example "12:47" that means 12 hours and 47 minutes) from a URL document and calculating some parameters like overtime work, shortages and ...

0
votes

1
answer

68
views

### How to compare two columns in Excel with a conditional IF statement?

I have three columns that have some expense figures: Actual (column A), Budget (column B), Variance (column C):
For these expenses, if Actual is greater than Budget, I want the variance column result/...

0
votes

1
answer

96
views

### Formula for Matching Header to Value

This is a question that I may have difficulty explaining, so bear with me.
On one sheet I have a column which has several cells, some of which are blank, some that have a single value, and some that ...

0
votes

0
answers

19
views

### max if functions in Excel to return text from a different row matching the max if conditions

I have an Excel spreadsheet with a summary table that contains credit card charges information from different sheets. The sheets have columns A to F with 'Date', 'Cardholder Name', 'Amount', 'Result (...

2
votes

1
answer

94
views

### Find uniques and show corresponding values as comma separated values in Excel?

I have a table with large data containing family name and family households. And I am trying to get them in a unique list followed by the household names as comma separated values.
The table below ...

0
votes

2
answers

54
views

### Compare data across rows in range

I am trying to solve the following problem in MS Excel:
There are two columns of data, one for types of food and one for how many of them are (in an inventory, say). I want a SINGLE formula that will ...

1
vote

2
answers

61
views

### SUM select ranges of varying lengths in a single column using null/non-null values in second column to decide SUM indexes in an Excel formula

Example of format for the issue, I need to generate the third row that is a sum of each color block:
I have an odd set of columns in a very large dataset, and I am not great at Excel. I could be way ...

0
votes

0
answers

39
views

### How can I easily make a chart showing what weeks a task was completed or not?

I'm trying to make a chart that shows who completed an inspection for each week. At my work, we run Sunday to Saturday and within that time period, we expect our guys to submit a truck inspection.
I ...

0
votes

1
answer

62
views

### How to count the number of cells that contain certain text while excluding cells that start the same but end different

In our company, the truck numbers in our database all start with "TK" A regular truck would be "TK123" for example. We also have stipend trucks and these all start with "TKPB&...

0
votes

1
answer

151
views

### Sum per quarter with extra bits

I have a table that looks like this
Date
Transaction
01/05/2011
£100,000
07/06/2011
£200,000
10/07/2011
£123,123
And so on, the data continues from 2011 to 2024 and is about 200 entries,
I'm ...

0
votes

1
answer

118
views

### Add Link Reference in Excel using OneDrive

I have two workbooks, and I am trying to link the two files in Excel O365 in OneDrive.
I have had limited success doing the following command on my PC or directly on OneDrive. Placing this command in ...

0
votes

0
answers

54
views

### Unable to edit the required text and update auto-date

I Should be able to edit any text updated except , "Posted", "Closed" in J Column. Every time I edit the text the date should be auto updated in next column cell. For example, I ...

0
votes

1
answer

206
views

### How to Autoincrement the Column index number by dragging VLOOKUP formula down

I need to drag a Vlookup down and have the Column index number in to increase by 1 every row I drag it down.
So for example:
Row 1:
IF(VLOOKUP(A2,'Jan 2024'!$D:$BP,**35**,0)<=0,"IGNORE",...

0
votes

0
answers

14
views

### Automating and filtering data from one sheet in Excel to four different sheets

I have two separate sheets in Excel that shows data in the form of (O) and (T). They will be further separated in to four sheets named A, B, N, S.
How do I go about automating data keyed in the first ...

1
vote

2
answers

152
views

### Converting Hours and Minutes to Minutes Excel

Goal: calculate pay for elapsed time.
Short question is how can I convert the time value of hours and minutes to just minutes in Excel to calculate payment for hours worked? For example, I have a time ...

0
votes

2
answers

59
views

### How to properly count multiple criteria from ranges in a separate sheet?

I have a master data table in a worksheet (worksheet name 'Data'; table range called 'MasterData'). This includes data about participants in a conference (name, nationality, etc.) and whether each ...

0
votes

0
answers

55
views

### Using Excel: how can I change to colour of a cell based on whether 3 cells in the same row contain ANY text?

sorry for the novice question. I have a spreadsheet looking for sign off from colleagues after reading a document. I would like cells in column 'O'(sign-off complete) to turn green if there has been ...

0
votes

1
answer

99
views

### How to create a sorted table summary of the count of a categorical column in Excel?

I have a dataset like:
Animal fruit
cat apple
dog grape
cat cherry
cat pear
dog cherry
horse melon
cat lemon
I would like to create a summary table counting each occurance ...

0
votes

0
answers

119
views

### How to create Excel histogram from frequency table?

I have an Excel table with two rows.
One header row with positive integers representing different event properties and another row with the frequency of events with said property number.
Here's a ...

0
votes

1
answer

52
views

### Excel - AverageIfs with multiple valid criteria in different columns

I'm trying to get excel to give me the average of multiple objects if they meet a criteria.
I want to calculate the average of the last column (SLA in days) for the lines that contain Country=ES/PL, ...

0
votes

2
answers

958
views

### My Excel files show some blue dot lines between rows

For some reason when I open a certain file in Excel, it shows some ugly dotted blue lines between rows.
I kind of eliminate them using the menu [View] -> Workbook Views and changing from Page Break ...

1
vote

1
answer

88
views

### How to repeat rows with specific times and modify some variables at the same time?

The sample dataset is as follows:
name
Number of days of Annual leave
Date from dd/mm to dd/mm
Amy
3
01/10-03/10
Iris
2
04/10-05/10
The dataset that I want:
name
Number of days of Annual leave
...

0
votes

2
answers

82
views

### Counting the number of times a value appears in a range of columns

Consider the following table which shows a generalised, abstract version of my specific real-world problem (which I will also illustrate).
Each "1" or "0" is part of a column ...

0
votes

1
answer

226
views

### How to get the value from a cell given a name box in Excel

I have a situation that I need to do a concatenation to add a single quote in the beginning and end of whatever is at column Q, and repeat the value twice by row in column I:
So far I was doing ...

0
votes

1
answer

183
views

### EXCEL show total count from sheets when using multiple drop down lists

I have a workbook with multiple sheets. On each sheet you can select a user from a drop-down list and then select an item from a different drop-down list. There can be multiple instances of user and ...

0
votes

1
answer

81
views

### Is there a formula for retrieving data from multiple cells on a different spreadsheet and adding to one single cell on another spreadsheet?

I am working on a project in which I need to retrieve the Parent Items for a list of scrap material. The problem is that most of this material is used in multiple Items. When I run a separate report ...

1
vote

0
answers

49
views

### Using countifs with more than one condition using references not values

Am trying to apply the countifs function to two columns of my data sheet with two different conditions to meet (one for each column) using cell references in the criteria. The following syntax has ...

2
votes

1
answer

418
views

### How to create a stacked column chart where each category has unrelated subcategories

I have the following set of data
I want to create a column chart with 3 stacked columns(one for each category). The sum of amounts for each category equals 100. The issue I'm having is that the ...

0
votes

1
answer

147
views

### Is it possible to restrict a VBA macro command to just one workbook?

I have macros added as commands to a specific workbook. But a new workbook will show the commands, and if I click it, it just opens the previous workbook. I would rather them not be there in new ...

0
votes

1
answer

54
views

### Formula to use to get desired result

I have 2 tables. Table 1 has raw data, Table 2 is what I'm using as a report. Can anyone suggest a formula, to be used in column D in Table 2, to pull all the negative amounts from Table 1, based on ...

0
votes

2
answers

182
views

### How to make Excel subtract and add (not sum nor subtotal) only the values of the cells shown after using a filter

As shown in the image below, when I use the filter in column A to show only apples, the balance column shows wrong numbers because it takes into account the hidden cells which belongs to oranges.
I ...

0
votes

1
answer

3k
views

### References to blank cells become 0's

I have a spreadsheet in excel where I need to make some references to an area in another spreadsheet. I simply use ='EARTH'!B9:N323, which is an area mixed with some columns of text and others of ...

0
votes

0
answers

20
views

### How to automatically enter a table value into another calculation sheet, and get the returning outcome value back to the table?

Not sure if I formulated that in an understandable way. Basically, I have sheet1 with a table filled with characters with their theoretical DPS where they just attack forever. For a more realistic DPS,...

0
votes

1
answer

70
views

### How to stop numbers from automatically opening hyper links in Excel?

So I have ID numbers that I hyper link to a website. It's automatically hyperlinking the whole cell, preventing me from editing the text by double clicking the cell.
For example, let's say I have ...

1
vote

1
answer

310
views

### Divide a value based on another cell value and split it eventually by the divisor from where an another cell is matched with another cell value

I have a formula to divide a value by an another cell value and split the value eventually. This works fine but it splits from the starting month.
My current formula:
=IF(COLUMN()-COLUMN($F5)<$I5, ...

0
votes

1
answer

32
views

### Dependent dropdown in excel

I have a market, submarket, store no. Columns, I want to use a drop-down to select the market submarket and store no. to display the details of the shop in Excel.
When I choose a market-only submarket ...

0
votes

0
answers

52
views

### Formula to Round Down Number in cell

I need to round down the highlighted numbers in Column Q of the attached screenshot. I currently have formula of P/F starting on Cell Q5 through Q46. I am diving the quantity on hand for each part ...

0
votes

0
answers

294
views

### Excel Solver Error: The objective cell values do not converge

I've been consistently getting the error above when trying to get the parameters for my logistic regression Does anyone have any ideas why this may be happening?
I see that the solver is making the ...

0
votes

1
answer

412
views

### Excel Help - If two cells meet a certain criteria input a value into another cell

I'm a bit of an excel beginner so apologies in advance.
I need my worksheet to automatically input a price into a specific cell.
I have a table in one sheet which has tables for each product brand and ...

0
votes

2
answers

164
views

### Round each instance in Sumproduct before sum

I am trying SUMPRODUCT Formula on lets say Column A and Column B. Column A has Patient referrals and Column B has percentage referred for each hospital. I am using this syntax:
ROUND(SUMPRODUCT(A1:A10,...

-1
votes

1
answer

66
views

### How to generate a table of names based on complicated criteria in Excel

Screenshot:
Hi experts,
I am trying to make a table in Excel that can generate a "Doc #" for each day of the month (for Monday to Friday only) based on availability. I've included a ...