New answers tagged

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. ...
DrMoishe Pippik's user avatar
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 ...
Charles Kenyon's user avatar
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 ...
Warren Elson's user avatar
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 ...
Mayukh Bhattacharya's user avatar
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 ...
FlexYourData's user avatar
  • 6,820
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 ...
Hamed Ghasemi's user avatar
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 ...
Mayukh Bhattacharya's user avatar
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
Ram's user avatar
  • 1,107
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 ...
MGonet's user avatar
  • 1,807
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 ...
MGonet's user avatar
  • 1,807
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 ...
Arctiic's user avatar
  • 1,063
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 <> &...
Jon's user avatar
  • 111
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 ...
Mayukh Bhattacharya's user avatar
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&...
MGonet's user avatar
  • 1,807
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 ...
Michael Curtis's user avatar
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 ...
Mayukh Bhattacharya's user avatar
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 ...
Beer_en_thu_si_asT's user avatar
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 ...
Mayukh Bhattacharya's user avatar
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:...
Mayukh Bhattacharya's user avatar
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 ...
Toto's user avatar
  • 17.7k
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:...
Mayukh Bhattacharya's user avatar
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)))
P.b's user avatar
  • 262
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:...
Mayukh Bhattacharya's user avatar
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 ...
Mayukh Bhattacharya's user avatar
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 ...
Mark Colan's user avatar
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,...
Scott Craner's user avatar
  • 23.1k
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 ...
Scott Craner's user avatar
  • 23.1k
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 ...
Scott Craner's user avatar
  • 23.1k
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 ...
harrymc's user avatar
  • 476k
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 ...
Ron Rosenfeld's user avatar
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 ...
harrymc's user avatar
  • 476k
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 ...
Mayukh Bhattacharya's user avatar
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 ...
Mayukh Bhattacharya's user avatar
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"),",","\,"),"/","\/"...
Ron Rosenfeld's user avatar
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 ...
Starbuck3000's user avatar
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'.
Haig Johnson's user avatar
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)).
Emily's user avatar
  • 3,464
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 ...
AdamV's user avatar
  • 6,268
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 ...
James Beatty's user avatar
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 ...
Engineer Toast's user avatar
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 ...
JustMeToo's user avatar
  • 111
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 ...
MGonet's user avatar
  • 1,807
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 ...
FlexYourData's user avatar
  • 6,820
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, ...
FlexYourData's user avatar
  • 6,820
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: ...
FlexYourData's user avatar
  • 6,820
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 ...
Mayukh Bhattacharya's user avatar
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))...
Mayukh Bhattacharya's user avatar
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 ...
Arctiic's user avatar
  • 1,063
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 ...
MGonet's user avatar
  • 1,807
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=&...
MGonet's user avatar
  • 1,807

Top 50 recent answers are included