## New answers tagged microsoft-excel

1
vote

### Bad Request - Request Too Long when trying to insert image in excel comments

Most likely, you're trying to insert a URL, a link to an image that is online. To avoid this, when copying an image from the web,
Paste the image into an image editor, such as IrfanView or MS Paint.
...

0
votes

### Recommended ways to automatically update several word documents with specific info

Mail Merge
Mail Merge Using an Excel Spreadsheet
I do this on a daily basis.
It sort of stands the idea of a mass mailing on its head.
A row in your spreadsheet is your data record.
You can apply it ...

1
vote

### Excel: Pivot Table with "% of Parent Row Total"

Unfortunately there is not a way to do this natively with a pivot table. When I need something like this I will just make the extra column outside of the pivot table using a formula like the following ...

1
vote

### Excel formula to seek if the last value falls within a certain period

If I am not mistaken, and understood correctly the last non zero value for a period can be achieved by the following formula as well:
=IFERROR(TAKE(TOCOL(A2:A18/(A2:A18<>0),2),-1),0)
Or use ...

1
vote

Accepted

### IF formula for conditional formatting

Create a formula rule.
In a conditional formatting formula rule, if the formula evaluates to TRUE, the formatting will be applied.
In your case, you create the rule with C17 selected and enter a ...

0
votes

### Excel, update cells when clicking on another cell

Just add this code to yours sheet's code area:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A43:A54")) Is Nothing And Target.Cells.count = 1 ...

0
votes

Accepted

### Calculating total sales with multiple criterias across two tables

If I understand correctly, then SUMIFS() isn't the right function that will work for the desired output, instead use the following as shown in the screenshot:
• Formula used in cell C2 of Sheet Named ...

0
votes

### For x=3 in Excel, why does = - x^2 + x result in 12 instead of -6?

In math, the - sign binds tighter (higher priority) than the power function (ie ^2). So you have (-3) squared which is 9 + 3 = 12

0
votes

### Added trendlines appear in legend but not in chart

Change x-data to time only (without date) - data from column C.
Remove the chart and recreate it. There are too many trendlines in one chart.
Add one (or a few) trendline and format it according to ...

1
vote

### Excel problems with axes data labels in dual clustered column chart

Custom format for axes
Unfortunately, it is not possible to format the largest value separately (unless using VBA code), you can only format values above a certain limit separately. In this case, you ...

0
votes

### Display Blank when Referencing Blank Cell in Excel 2010

I'm surprised no one has mentioned this method:
=IF(ISBLANK(A1),TEXT(A1,"#"),A1)
Essentially the same trick as changing the data format, but this method is formulaec and thus you can ...

1
vote

### Display Blank when Referencing Blank Cell in Excel 2010

If you have a newer version of Excel that supports lambdas, you can write one as follows:
Define a new name in the Name Manager, say, Ditto.
In the Refers to: field, type =LAMBDA(x, IF(x <> &...

3
votes

Accepted

### Trying to find Date Differences in Excel between multiple enteries

There are quite a few ways to accomplish this, I will try to post some ways, as far I know using Excel Formulas. There is also one flexible option and that is the use of POWER QUERY.
Using Excel ...

1
vote

### Excel, update cells when clicking on another cell

You can combine formulas with VBA.
In Options:
Options > Formulas > Enable Iterative Calculations
Formulas:
B38 : =IF(AND(CELL("row")>=43,CELL("row")<=54,CELL("col&...

0
votes

### Excel conditional formatting per row

Here are some ideas for if you follow the above advice (which is good advice) but there is still a problem.
Sometimes, it is because a $ is very important.
Sometimes, the '=' sign is forgotten when ...

1
vote

Accepted

### Check if cell formatted as text contains a number

As commented above and since it has worked for user, posting it as an answer.
Using Double Unary or VALUE() function one can test whether the cell reference consists a true number or not.
• Formula ...

1
vote

### How do I return the max value from multiple values in a single cell?

I have not tried the answer provided by @MayukhBhattacharya, but I do appreciate the information provided, so I gave the answer to that user.
I determined that the answers needed to be changed to a ...

2
votes

Accepted

### How do I return the max value from multiple values in a single cell?

If I have understood correctly, then the following formula should work:
Formula used in cell B1
=MAX(--TEXTSPLIT(A1,"; "))
Or Using BYROW() to get all the output as once, without fill ...

0
votes

### Group data by date

There are many ways of doing this, here are few possible ways:
Using UNIQUE(), TOROW() & FILTER() function:
• Formula used in cell D2
=UNIQUE(A2:A21)
• Formula used in cell E2
=TOROW(FILTER(B$2:...

0
votes

### Notepad++ Regex merged 2 sections into one line delimited

Ctrl+H
Find what: ^([\s\S]+?\R----\R)(.+)\R----\R?
Replace with: $2; $1
TICK Wrap around
SELECT Regular expression
UNTICK . matches newline
Replace all
Explanation:
^ # beginning of ...

1
vote

Accepted

### Compare lists of data options besides EXCEL

Try using FILTER() & COUNTIFS() function:
=FILTER(F2:F6,COUNTIF(E2:E4,F2:F6)=0)
The above formula is related with the OP, and the one related with the Excel File will be as follows:
=FILTER(C2:...

0
votes

### Excel duplicate ever 10 rows of data and then reverse order

Another way could be
=LET(a,SEQUENCE(10),
x,VSTACK(a,SORT(a,,-1)),
VSTACK(x,x+10))
You could also combine it with index
=LET(x,SEQUENCE(10),y,VSTACK(x,SORT(x,,-1)),INDEX(B1:B20,VSTACK(y,y+10)))

0
votes

Accepted

### Expanding results from a dynamic array in Excel 2

Here are two ways outlined using Excel Formulas :
Using LAMBDA() helper function called REDUCE()
• Formula used in cell D1
=REDUCE(A1:B1,SEQUENCE(ROWS(A2:B4)),LAMBDA(φ,δ,
VSTACK(φ,IF({1,0},INDEX(A2:...

0
votes

### Excel duplicate ever 10 rows of data and then reverse order

There are quite a few ways to work on this, however in terms of readability, one can use the following. The following formula shown below doesn't require recursive LAMBDA() helper functions, as well ...

0
votes

### How to paste Excel rows into Word as paragraph lines, not as a table?

(partial answer) There is an option hidden on the Layout tab that does not always appear, called "Data", and in that there is "Convert to Text". I accidentally got it to appear ...

0
votes

### Excel duplicate ever 10 rows of data and then reverse order

This creates an array of row numbers then the INDEX returns the value at that row:
=LET(
z,A1:A20,
x,SEQUENCE(10),
y,VSTACK(x,10-x+1),
g,DROP(REDUCE(0,SEQUENCE(ROWS(z)/10,,0),LAMBDA(r,...

2
votes

### Automatic Cashflow Generation in Excel?

With Office 365 Excel we can use VSTACK, HSTACK, SEQUENCE and some math to make it dynamic:
=VSTACK(SEQUENCE(,B3+1,0),HSTACK(-B4,B5*(1+B2)^(SEQUENCE(,B3,0))))
SEQUENCE is what makes the arrays ...

0
votes

### How can I apply a formula to specify which cells I want to use?

Use INDEX:
INDEX(B:B,{1,2,3}*myValueCell)
So your full formula would be:
=AVERAGE(INDEX(B:B,{1,2,3}*myValueCell))
If you are using an older version of Excel than 365 then we need to do the INDEX ...

0
votes

### In Microsoft Word, is there a way to conditionally format the cell properties of a MS Word Table (like in Microsoft Excel)?

Word does not have a simple method for Conditional Formatting.
You will need to use VBA for that.
See the post
Conditional Formatting in a table on MS Word
which shows a Coronavirus risk-assessment ...

0
votes

### Expanding results from a dynamic array in Excel 2

Although the formula is complicated, the process is fairly simple in Power Query with just a few steps.
PQ is available in Windows Excel 2010+ and Microsoft 365 (Windows or Mac)
To use Power Query
...

0
votes

### How to paste Excel rows into Word as paragraph lines, not as a table?

In the Home pane, click the Paste down-drop and select "Paste Special...".
From the dialog, select "Unformatted Unicode Text" (for me that's the
only option).
You will get the row ...

0
votes

### Replace with wildcards in Excel or batch edit some wrapping formula

Not using VBA but using Excel4.0 Macro Functions with Modern Excel Functions - MS365 is one way of doing this. Although the workbook needs to be saved as either .xlsm or .xlsb
• Formula used in cell ...

1
vote

Accepted

### Excel count if sum of other values <0.5

There are quite some ways to work on this, but in terms of readability maybe use GROUPBY() or PIVOTBY()
=GROUPBY(B2:B10,N(D2:D10<0.5),SUM,,0)
Or, If one don't have access to the above mentioned ...

1
vote

Accepted

### Trying to set up a exchanger formula in MS Excel

Turn your pattern into a usable format string for the TEXT function:
=TEXT(B2,SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C2,"1","0"),",","\,"),"/","\/"...

0
votes

### Mouse Scroll Wheel does not scroll in *some* Excel worksheets

TLDR: try selecting all, unfreeze panes.
Hi,
this happened to me recently. Symptoms were an Excel spreadsheet, in which scrolling suddenly became disabled on the document and reopening it would not ...

0
votes

### Excel keeps adding hyphens within words when pasting

To get rid of imbedded soft hyphens in Excel:
In the Replace dialog box type Alt - [num pad]0173 in the 'Find what' field and leave 'Replace with' field blank, then click 'Replace all'.

0
votes

### Create a formula to sum all values number between two ranges

Please check whether the formula is helpful.
=SUM(INDIRECT(CHAR(65+MATCH(INDIRECT("E33"),A19:Q19,0)-1)&40):INDIRECT(CHAR(65+MATCH(INDIRECT("E34"),A19:Q19,0)-1)&40)).

0
votes

### Compare data across rows in range

Assuming all numbers are zero or more (no negatives), you could count all the steaks, then take away all the steak = 0, leaving the number of steak = [>0], then check if the result is >0. For ...

0
votes

### Excel column widths changing upon data refresh, DESPITE "adjust column widths" being unchcked

I had the same problem and was scratching my head for weeks before realizing that upon switching to a new computer that it had not installed all the updates available for Excel and that this was a bug ...

0
votes

### Is there a way to list the names of excel sheets that contain a value?

Warning: This is a slow solution and not recommended for large datasets. It is slow. Turning off automatic formula updates will help.
This is a very slow and clunky way to do it but it does work so ...

1
vote

### In Excel 365, how to remove the green "sharing" check indicator from the bottom-right of a workbook

I found the answer on the Google forums:
https://support.google.com/drive/thread/124802423/how-do-i-hide-the-green-checkmark-icon-on-all-my-sync-d-files?hl=en&sjid=6469741410673054718-NC
To save ...

1
vote

### Excel - Can you use both Relative R1C1 Reference Notation and A1 Reference Notation in the Same Excel File?

If you use the A1 style in your sheet, you can only use the R1C1 style in 4 places at a time (at least I don't know more):
In formulas with the INDIRECT function (as text) with the second argument ...

3
votes

### Excel - Get R1C1 Reference Style Option in Quick Access Toolbar (QAT Bar)?

You can access that option with this shortcut (not really short, but still):
Alt, F, T, down, Alt+R, Enter.
If you want it slightly shorter, add the Options: Formulas command to the QAT:
Depending on ...

1
vote

### Excel - Can you use both Relative R1C1 Reference Notation and A1 Reference Notation in the Same Excel File?

You can use them in the same file.
But you must switch between one and another. You can't type an A1 formula, then type an R1C1 formula without first switching the reference style.
When you switch, ...

1
vote

### How do I know if I'm using Excel 2021 or Excel 365?

Your version and build number indicates what you're using.
You are using Version 2401 which is the Current Channel release Microsoft 365 Apps (including Excel) of January 30 2024.
Read more here:
...

1
vote

### Excel formulas for autofil

If I have understood correctly from your post, you are trying to use Conditional Formatting in Excel based on Start Date and it will highlight the correlating columns.
To achieve the above, follow the ...

0
votes

Accepted

### Returning value within two column when it match 3 criteria

If I have understood correctly, you need to use the following formula in order to accomplish the desired output:
=IFERROR(INDEX($C$7:$E$19,MATCH(1,(G2>=A7:A19)*(G2<=B7:B19),0),MATCH(I2,C6:E6,0))...

0
votes

### Paste in Excel without splitting on newlines

This might be a bit roundabout, but if you want some semi-automation to avoid cell-by-cell manual operations, then here's a work-around:
Add a helper column, and use a formula to concatenates the ...

0
votes

### Macro for using Goal Seek over a range of cells

You should use GoalSeek for individual cells, not for a whole range at once. Accuracy of calculations you can control by changing Application.MaxChange property in relation to your data. The default ...

3
votes

### Unraveling a nested IF statement to make it more user friendly

If you put your list of neutralisers with pH ranges somewhere in your workbook and give it a name List (name refers to data without header 48 rows x 3 columns), you can use the formula:
=IF(Sheet1!A5=&...

Top 50 recent answers are included

#### Related Tags

microsoft-excel × 25801worksheet-function × 7216

microsoft-excel-2010 × 3374

vba × 2748

microsoft-excel-2007 × 1623

charts × 1240

microsoft-excel-2016 × 1132

microsoft-excel-2013 × 1114

macros × 932

pivot-table × 696

conditional-formatting × 660

microsoft-office × 545

csv × 486

spreadsheet × 480

vlookup × 434

microsoft-word × 417

google-spreadsheets × 294

office365 × 249

windows × 246

sorting × 241

formatting × 235

date-time × 221

power-query × 212

date × 202

microsoft-excel-365 × 186