New answers tagged

0 votes

Saving an Excel file with filters and conditionals applied

If you have Microsoft 365 Apps, please try to save your workbooks on OneDrive or SharePoint. Then you can go to View > Create a new view, apply filters and cinditionals for each book.
  • 3,035
0 votes

Excel: Custom format of date that excel cannot notice

It seems like you want to convert this into a date: Dec 8, 2022, 8:35:04 AM If that's the case and supposing the above is in cell A1, then this will work if you have the TEXTBEFORE function: =...
  • 6,045
0 votes

MS Excel: how to use a (random) number as row number in a formula

Try this formula: =INDIRECT(LEFT(B$1,FIND(".",B$1)-1)&"!"&RIGHT(B$1,LEN(B$1)-FIND(" ",B$1))&$A2)
  • 2,790
0 votes
Accepted

Is there a way with Excel Formulas to Sum up a column with the values above the referenced column?

Put this in C1 and drag down: =SUM($A$1:A1)-SUM($B$1:B1) It locks the top reference and the bottom reference is dynamic. Or you can use SCAN if you have it: =SCAN(0,A1:A3-B1:B3,LAMBDA(a,b,a+b)) ...
  • 21.7k
2 votes

In my spreadsheet and start entering into a cell, if a cell above contains a similar entry, the cell auto fills, how can I change the properties?

As explained here: Microsoft Office Excel completes text entries that you start to type in a column of data — if the first few letters that you type match an existing entry in that column. If you ...
0 votes

How does Excel's INDEX know to use REFERENCE form?

In cases like this where an array and reference look identical (A:A), how does it know when to return the value and when to return the cell reference? As stated here: It's important to note that the ...
0 votes

Gray Triangle in Top Right Corner of Excel Cell

I had the same issue and I just found out what caused it in my case. I got these by copying and pasting from an old website into excel, probably the HTML code contained "something" which ...
  • 1
0 votes

Use Cell references within SUM function (Excel)

You're not declaring where your cells within the formula are. I haven't looked at all your code as there's a lot of chaff in there - comments and irrelevant lines of code. This procedure hopefully ...
1 vote

MS Excel: how to use a (random) number as row number in a formula

You can use XLOOKUP to find the row in Sheet1. Put this in under the Sheet1.ColA header in the first row in Sheet2: =XLOOKUP(A2,SEQUENCE(816),Sheet1!$A$2:$C$817) Then drag it down to the last row in ...
  • 6,045
0 votes

How to allow user to enter data in the fields in a Table but keep data validation rules on that field hidden?

I've tried your scenario and can't reproduce your issue (on Excel 365). When I protect the sheet enabling only selection of locked and unlocked cells: content of unlocked cells can be changed ...
0 votes

Excel - The most spending occurred around this date

Based on your description the result should be 4/18/2022. If my understanding is correct, try this formula: =INDEX($A$2:$A$8,MATCH(MAX($B$2:$B$8),$B$2:$B$8,0))
  • 2,790
0 votes

How can I copy and paste a string of characters into libreoffice calc with one character per cell?

First I copy, pasted the figure from the paper into Jupyter Notebook and added quotation marks, commas etc to make data structures in Python: colNames = "...
0 votes

How to create dynamic sheet references with cell text in Excel

INDIRECT =VLOOKUP($B3,INDIRECT(R3&"!$B$2:$G$475"),6,FALSE) R S T 2 100 93.8 93.5 3 03.22 E 04.22 NE 04.22 AL 4 =VLOOKUP($B3,INDIRECT(R3&"!$B$2:$G$475"),6,FALSE) =...
0 votes

How to bulk edit File property details of a file in windows?

ExifTool by Phil Harvey The ExifTool will allow you to replace the value of the Comment tag in a group of files using a CSV file as the source. CSV file format mp4 files for each batch need to be in ...
0 votes
Accepted

Create scatter plot using two arrays of data in Excel

You can use Power Query to unpivot and then merge the tables. I used "Get data from picture" to get your "accuracy" table into my workbook. I've duplicated it for the timeToKill ...
  • 6,045
1 vote

Excel: Shortcut key to move selection to specific row

Use CTRL + G and give the cell number you need E.g., press CTRL + G then enter A450 (cursor will move to that cell)
1 vote

Excel macro date parser cannot handle single-digit days

For accepting both 2 and 4 digits year, (where 2 digits is less than 1000) I used one line to check all about date, that's: If IsDate(date) And Len(Trim(Year(date))) = 2 Or Len(Trim(Year(date))) = 4 ...
  • 11
-1 votes

Bordering tables with merged cells

There are several ways to improve quality-of-life in this scenario. Learn the shortcut keys to access the cell formatting dialogue window more quickly. You can invoke the window using Ctrl+1, and ...
  • 760
0 votes

Power Query Load Selected Data from a List

Sure, a simple solution suggests itself. If PQ takes a long time to load, check to see if opening as a spreadsheet takes noticeably less time. If so, in a second spreadsheet, use FILTER on the data ...
  • 16
0 votes
Accepted

Search for cells in a single column containing a string and output all matching cells to new column

Using the test in the following is much more straightforward and therefore easier to understand a year from now when maintaining the spreadsheet: =FILTER(A1:A8,LEFT(A1:A8,4)="Task") It is ...
  • 16
0 votes

Get "most recent" start date, and "most recent" end date in row

If I understand correctly, you have data being input 9 columns a day. Each entry has 3 sets of data in it and each set of data includes Start Date, End Date, and Progress. Not all those fields will be ...
1 vote

What is the underlying Math/Equation of Excel RATE() function

Please see IRR The internal rate of return (IRR) is a metric used in financial analysis to estimate the profitability of potential investments. IRR is a discount rate that makes the net present value ...
  • 40k
1 vote
Accepted

How do I use a cell reference as a column in a table reference?

Either of these should work: This searches the header row of the Data table for the heading you've entered in the Target column of your Table table. It then uses the result of that to return the ...
0 votes

Search for cells in a single column containing a string and output all matching cells to new column

Select the Task List range- go to Data- From Table/Range: Open Power Query editor- Select the Text Filters- select Contains... Enter the Task and click OK: Change the column name to Output- Close &...
  • 2,790
1 vote

Copying cells for a larger dataset in Excel

You describe the problem in a way that suggests you want physical action ("drag down and copy") but also in a way that suggests you want a formula instead. I think you want a formula, so I'...
  • 11
0 votes

Vertical axis overlapping title in excel

Select the plot area, then resize by dragging the left side rightwards.
  • 6,045
0 votes

Search for cells in a single column containing a string and output all matching cells to new column

This solves the issue: I would like to suggest two formula. If you are using higher version of Excel 2021 or 365 then try this in cell AQ2. =FILTER(AO2:AO8,ISNUMBER(SEARCH("Task *",AO2:AO8))...
  • 8,778
0 votes

How to prevent occurrence of or cause Excel to ignore all circular references in a spreadsheet automatically

If the spreadsheet contains cyclic formulas and you would like to disable this calculation: Open menu File > Options > Formulas In the section "Calculation options", uncheck "...
  • 424k
0 votes

Changing data source of pivot tables with VBA

I would like to split a document by destination (for example) and create automatically various specific files (for each destination) with the relevant pivot chart. How to do to update the data source ...
  • 1
0 votes

Max value for multiple datasets in different orders

I am understanding that the cities have names of a real sort, and you want them sorted A-Z, then the "odd" cities in the list chosen for evaluation of their Salary column entries. Of those, ...
  • 1
1 vote
Accepted

Can no longer update Excel chart links in Word, except with a weird workaround

I followed the instructions for downgrading to an earlier version. I was able to get it working with version 2202, build 14931.20120, which was the first one I tried. The known working version ...
  • 11
1 vote
Accepted

Finding and Displaying Unique Names

If you have the latest version of Excel, you can use a combination of TEXTSPLIT and UNIQUE functions to get the desired result as seen on the image below. In case you want to split a whole range, you ...
2 votes
Accepted

Excel - Combine range to column

If you are using Excel 365, the new function =TOCOL(Range, Ignore) does exactly that. To leave out empty values, use =TOCOL(Range, 1).
  • 321
1 vote
Accepted

Excel: Split a 2 column range (A:B) into multiple smaller columns with a pre-set number of rows

Welcome! The code itself for copying data to other cells is very simple, not much different (by size) from the one you took from the macro eight years ago. As always, when the data for the calculation ...
  • 1,261
2 votes
Accepted

How to increment a cell references in a formula

I can give you a "generic" way... which is a very "low-tech" way of doing it, but assuming you can propagate your formulas to the right so that the correct formulas exist, just not ...
  • 36
0 votes
Accepted

How to filter a table onto new tab in excel?

Apologies in the delay for responding. Used a similar technique as provided by the @Rajesh Sinha below. replaced the Sheet1!$A$2:$C$10 with a defined table using the following steps. In the menu bar ...
  • 121
1 vote

How to increment a cell references in a formula

One of many possible options is the formula =SUM(OFFSET($A$1;0;(COLUMN()-1)*2;1;2))
  • 1,261
0 votes

How do I align two tables based on a unique column with Excel?

There is a direct way to do this with VLOOKUP (and XLOOKUP which wasn't available then), although doing it with Power Query or VBA would be the gold standard. Especially with such a large move. Many ...
  • 1
0 votes

Merge Rows and Sum Single Value in Excel

Power Query is the way to go. Combine that with your output data not showing anything except returning single rows that are not actually combined with anything and I shan't show any cell formula ...
  • 1
0 votes

Merge Rows and Sum Single Value in Excel

You can use Power Query for this. Create a blank query using Data>Get Data>From Other Sources>Blank Query On the Power Query Editor ribbon, use Home>Advanced editor Replace the code that's ...
  • 6,045
1 vote
Accepted

How can I copy a larger number of cells into a smaller number of cells that are already feeding a graph?

try this: Your data set is in A1:B10 and the chart uses A1:B10 insert 10 rows between row 1 and row 10 the chart now uses A1:B20 paste the new, 20-row data into A1:B20
  • 22.2k
0 votes

How do I Preserve the format for a date field as mm/dd/yyyy in Pivot Table

convert the 'date' column into 'text' using TEXT function [TEXT(value,"mm ddd yyyy")]. Use the latter (text) column in pivot instead of the first. Pivot will give you the date in the ...
  • 1
0 votes
Accepted

Excel conditional formatting: contains one string but not another

=AND(LEFT(E2,4)<>"US-D, LEFT(E2,3)="US-") LEFT(x,y) takes the first y characters from x.
  • 6,045
0 votes

VBA code to auto hide rows based upon text in cell A1

Welcome! As far as I was able to read and understand your code, the normal state of rows 35:36 is "hidden". In case of CODE 4 OR CODE 5, row 35 should be displayed, and in case of CODE 1, ...
  • 1,261
0 votes

MS Excel - how to fix a column in the formula when in named table

Or use indirect formula: =sum(indirect("Table1["&"Field1
0 votes

Excel: How To Transpose Every Random amount of N Rows From One Column To Multiple Columns?

Completely agree with the solutions already given. If you still have not completed the task manually, but are waiting for a ready-made solution, then I will offer my own version. Turn on macro ...
  • 1,261
1 vote

Finding aggregate return for multiple entries under same name

Please see if this solution helps. Formula in I3 is =(-SUM(IF($D$3:$D$9=H3,$E$3:$E$9,0))+SUM(IF($D$3:$D$9=H3,$F$3:$F$9,0))) This needs to be converted to Array Formula by pressing CTRL + SHIFT + ...
  • 5,057
1 vote

Combine two related line graphs

Note: The X-axis values aren't really relevant here since they are the versions of each product, which obviously would be different, and aren't related per se. Would you mind using Scatters to show ...
  • 3,035
0 votes

How to disable copy-as-image in Excel?

Make sure that what you are copying doesn't include an object that excel recognizes as an image, such as a macro button.
1 vote

Joining tables with one key and multiple repeating values of the key on the other

You can use the following approach, just edit the range references to suit your actual ranges: (data is in A11:B27, bare ID's are in A1:A5) =SORT( FILTER( A11:B27, MATCH(A11:A27, A1:A5, 0) >= 1 ...
  • 21

Top 50 recent answers are included