## New answers tagged microsoft-excel

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 ...

- 14.5k

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 ...

- 14.5k

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
...

- 21k

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 = "...

- 203

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)
=...

- 913

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 ...

- 913

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 ...

- 4,612

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 ...

- 14.5k

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

#### Related Tags

microsoft-excel × 24506worksheet-function × 6795

microsoft-excel-2010 × 3326

vba × 2653

microsoft-excel-2007 × 1599

charts × 1174

microsoft-excel-2013 × 1107

microsoft-excel-2016 × 1093

macros × 891

pivot-table × 648

conditional-formatting × 611

microsoft-office × 521

csv × 470

spreadsheet × 458

vlookup × 410

microsoft-word × 397

google-spreadsheets × 272

windows × 238

sorting × 231

office365 × 222

date-time × 215

formatting × 214

date × 191

microsoft-excel-2003 × 174

power-query × 168