My current formula is =CHOOSECOLS(FILTER(Dex,((Dex[Type1]=D2)+(Dex[Type2]=E2))*(Dex[Used]=F2),""),1,2)

This works on my work computer, but I have 2021 at home. It adds _xlfn I believe because of the version.

Any ideas on swapping the formula to match 2021 formatting?

Everything looks great in the excel, and the first time I make a change I get #NAME? and it stops functioning.

I spent so long getting it to work on my work version, and I am stumped on changing it from here.

I would appreciate any help!

About a month ago a solution was found for the update that disallowed Excel 2016 to copy and paste:

Excel 2016 update bug : r/excel

It looks like Microsoft rolled back that KB as I don't see it anymore in my installed updates. All of my spreadsheets can now copy and paste, except for one. The weird thing is that *some* of the worksheets within that file work, but not all.

I've tried the following and nothing has fixed the copy & paste for those worksheets:

- Restarted Excel

- Restarted my computer

- Created a copy of the file, the copied file doesn't allow copy+paste

- When I try to copy from this file to a new file, it doesn't work

- When I try to use the 'copy worksheet' function to a new file, a new worksheet is created in the new file, but nothing gets copied over

- Renamed the file from .xlsx to .xls

- Renamed the file back from .xls to .xlsx

Thanks.

I have been using Excel on a different (new for me) computer and I noticed that by default, every single cell in every file shows an apostrophe at the beginning of any text filled cell

They only appear on this computer, but they don't show up when i email them to others, or other computer I use. and they show up in every file I open, whether I created it or opened someone elses.

is there anyway to get rid of them.

I'm exploring an idea and want honest feedback before building anything.

The idea is a simple tool for people who regularly work with Excel and CSV files.

It could help with things like:

• Sorting and filtering large spreadsheets
• Removing duplicates and cleaning messy data
• Converting files between formats
• Combining multiple Excel or CSV files
• Rearranging columns and formatting data
• Automating repetitive tasks that you currently do manually

Ideally, you'd upload a file, select what you want to do, preview the result, and download the processed file.

My questions:

1. Do you regularly spend time doing these kinds of tasks?
2. Which task is the most frustrating or time-consuming for you?
3. What tools do you currently use to handle them?
4. Would a simple tool like this be useful, or do existing tools already solve the problem well enough?

I'm not selling anything. I want to understand real problems before deciding whether to build it.

Honest feedback is appreciated!

I would like a formula that will fit in one cell (D) and will do the following:
If cell C is negative, then return the result of this formula: B*.5
If cell C is positive and greater than cell B, then return cell B
If cell C is positive but less than cell B, then run this formula: ((B-C)*.5)+C

I’ve tried using IFS and something just isn’t working. I think the positive/negative distinction is throwing me off. Any help would be appreciated thanks!

Say I'd like cell A3 (see example at the bottom) to equal whichever cell I click on within a range (A5:A20), then when I click outside of that range, the A3 cell has the last active cell chosen in the range.

Ideally, I'd like to create this in multiple columns (each a range) leaving the last active cell selected in that range.

I found the below VBA, which will make the cell equal to any cell I click on - but, is there a way to write it so it changes the destination cell to the active cell only within a specific range, and then keep that destination cell as the last active cell selected within the range?

It's been a minute since I've done anything complex in Excel, and so much of its capabilities has changed in the last few years. Plus my VBA skills are low level.

Additionally, if there is an easier way to achieve, I'm all ears.

I'm trying to develop a formula that automatically sets a semi-monthly billing period (start date one column, end date in another). Our start date will always be a Sunday and an end date will always be a Saturday with 2 billing periods in a month (October for instance starts 09/27/26, semi-monthly ends 10/10/26, month end is 10/31/26). October is a 5 week month so there isn't a consistent 14 days between billing periods like other months. What formula can I use to automatically calculate billing end dates (start dates are just end dates +1 so they are simple). I have tried a combination of EOMONTH with lots of logic statements and I have come close to reaching a solution but when I do a check column with confirmed billing periods it doesn't align. Sample billing periods below to assist in validation (posting on mobile, apologies for not posting a table):

08/30/26 - 09/12/26
09/13/26 - 09/26/26
09/27/26 - 10/10/26
10/11/26 - 10/31/26
11/01/26 - 11/14/26
11/15/26 - 11/28/26
11/29/26 - 12/12/26
12/13/26 - 12/26/26
12/27/26 - 01/09/27
01/10/27 - 01/30/27

Hi,

I know that you are unable to change the comment indicator color from bright red so I have covered the indicators with white right triangles (I have tried both images and the shapes themselves). This works great on my computer but when I share them the triangles that cover the indicators are gone.

This is not a problem with images because the rest of the images in my spreadsheet come up. This is a problem because my spreadsheet is going to be public facing and has a significant amount of conditional formatting to hide aspects until other parts of the workbook are filled out so I do not want the comments to appear until the boxes that the comments go to appear.

Some other things: I am not looking to use MACROs but if they are necessary I could be willing as long as they work on peoples computers that are not public facing. As a company we do use google suites to share things but this tool is created and intended to be used on Excel. This issue also doesn't happen when shared between my personal devices. I am working on a PC and we have tried it on both MACs and PCs and it seems to work on the MACs weirdly enough.

The photo is what it looks like on my own computer.

Thank you so much in advance

This is for a homework assignment. I'm supposed to be able to link the different words in column A to the different discounts in column B of the Lookup Tables sheet by creating a formula and applying it to J2:N101 of the Dataset sheet.

I hope I'm being clear and concise. I tried using XLookup, VLookup, and Match. None of it works for me.

How do I solve this?

Lookup Table sheet:

A1:B5

D1:E5

G1:H5

Dataset Sheet:

Small sample because table has 101 rows

A1:N20

Not sure if I worded that correctly but I am hoping someone can point me in the right direction as I am not new to the basics of Excel but new to using its more advanced functions.

An example list with 3 columns:

Column A - product names

Column B - employee names(but there are several blank spaces without names)

Column C - total cost of the sale

I need to:

1. Get the total sum of column C, but only the rows that are NOT blank on column B.

2. Get the total sum of column C, but only the rows that ARE blank on column B.

3. Get the total sum of column C, both blanks and non-blank.

4. Take those three numbers and place them off to the side in three different cells.

Can Excel do this and how would I go about it?

Edit: Thank you all!

=SUMIF() and =SUMIFS() is what I was looking for. I tried it now and its exactly what I needed.

As has been mentioned by some amazing people below:

1. =SUMIFS(B:B, C:C, "<>") to get the sum of the non-blanks in column B

2. =SUMIFS(B:B, C:C, "") to get the sum of just the blanks in column B

3. =SUM(C:C) to get the sum of the whole list

Thank you all!

I have a sheet tracking the change in a number week to week. So 3 columns: date, number, change from last week.

So logically if x is the row, Cx = Bx-B(x-1) .

Is there a formula in excel I can use to do this automatically so that I can put a universal code for all of the cells in column c, enter the value in cell B and it will automatically fill in C with the change from the previous week?

(Bonus points if there’s a way to not have weird error messages for the empty cells for future weeks)

I'm not great at excel but I wanted to update a resource list for work. Is there a way to make multiple drop-down lists easier? Something that I could include the organization name, the counties it covers, and the services then I can filter it. I'm not sure how to include services that exist in multiple counties within one organization.

I have an excel sheet having formulas linked to sheet 1 ( containing data for January month ) . Now I want to use same file with formulas but using sheet 2 ( containing data for February)
How to do that?

Here is a sample from my spreadsheet:

Column B has the same value in both rows and indicates they are related.

When the "Document Type" is DZ, the value will match in both columns A and B.

Column F is populated from extracting text from column E via the following formula:

=IF(OR([@[Document Type]]="RV",[@[Document Type]]="RW"),TRIM(MID([@Text],FIND("#",[@Text]),5)),"")

What I am trying to do is populate column F with a nested formula that, where the above formula does not produce a result as it did for line 3, populates the same value as line 3 based on the row with the matching value under column B that did produce a result (in other words, to get "#200" to appear on row 2 under F as well as row 3 because the value under B for each matches). If it cannot be done within the same column, a separate one is fine because I could then consolidate them via the MAX function.

VLOOKUP is not doing it for me because of the repeating values under column B, and I am not having any success at getting a VLOOKUP or INDEX MATCH combination to work to via multiple criteria of both the "Clearing Document" and "Document Type".

Is there a way this can be done?

I have an excel file I use at work to find parts via description because our system does not have that as an option built in. To get all the part information I make a csv file with every part we have (roughly 175,000 rows and 9 columns wide) and have to periodically get an updated csv file as new products are added to it. The method I use now is New Sheet (sheet 2)>Get Data>Legacy Wizard,From Text> then import the updated csv>create a table with the new sheet>original sheet1 "Sort" is deleted and sheet2 gets renamed to "Sort">re-setup conditional formatting on the new table>re-link the cells from sheet3 referencing cells on "Sort">continue the work i was doing before this whole process.

What I would like to do, and hope is possible, is to keep the csv file as a separate file rather than import the text, delete the old one from the reference folder and move the new csv into the reference folder, and then have the excel table data auto update or at the most re-link the table to the new csv file (which will be the same name as the old one "DBR.csv"). I would even be happy with converting the csv to an excel file first if needed, mostly want to avoid setting up a new table and set up formatting every time. It would normally be a weekly process but been putting it off to monthly or even longer some times with the extra work involved.

Been searching a solution off and on for months so thanks for any help.

Whenever I attach an email by inserting an object, I click "Display as icon" but when I save it, the format changes. And if I try to reduce the email size, the name and icon shrink, and I do not know what to do even after trying several times.

Pls help me I will attach the image in the comment. I'm newbie to this sub as well

Now I know in the power query editor they don’t have a lookup however , there is the option to potentially merge queries to solve that issue.

But I have a bit of a problem, let’s imagine I have 2 columns, 1 with date with the first of each month (1/9/26) as an example , then in column 2 I have possible sessions available for that month (54) as an example.

I have some data missing in column 1 like the date or column 2 like the number of possible sessions. However when I merge queries it comes up with null values and I can’t use xlookup because even if I use it as a function when the query refreshes data the function is removed.

What could I do in this situation?

I have a project that requires data extraction from a .txt file that has a new name every year.

While I can go into the VBA and update the table name each year, I'd prefer if there was a location where users could input the file name on the Start Here tab (in a cell, or I could create a user form if needed), and this name could be used for the query, table name, etc.

So far, I seem to have created a working variable query name (must be split into two parts because queries sometimes use " ", ".", or "_" between the names) by having two cell values (B1 and C1) that create the final name, but the program breaks down when it tries to reference the table by that name. I also haven't figured out how to set the old query name with a cell value (B2).

Sub Data_Transformation_1()

Dim User, oldQry, qryStart, qryEnd, File As String
Dim wb(1 To 2) As Workbook

Set wb(1) = ActiveWorkbook
User = Environ("username")
qryStart = wb(1).Sheets("Start Here").Cells(1, 2).Value
qryEnd = wb(1).Sheets("start here").Cells(1, 3).Value
File = "C:\Users\" & User & "\Desktop\" & qryStart & "." & qryEnd & ".txt"

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.Calculation = xlCalculationManual

'** TROUBLE WITH VARIABLE oldQry **
    On Error Resume Next
    Set oldQry = wb(1).Queries("CBA 4321")
    On Error Resume Next
    oldQry.Delete

    wb(1).Sheets("Sheet1").Show

    wb(1).Queries.Add Name:=qryStart & " " & qryEnd, Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Csv.Document(File.Contents("File"),null,{0, 23},ExtraValues.Ignore,1252)," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(Source,{{""Column1"", type text}, {""Column2"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Changed Type"""

    wb(1).Sheets("Sheet1").Select
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & qryStart & " " & qryEnd & ";Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [" & qryStart & " " & qryEnd & "]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = qryStart & "_" & qryEnd
        .Refresh BackgroundQuery:=False
    End With

'** THIS IS WHERE THE PROBLEM BREAKS DOWN AND THE REMAINING COMMANDS CAN'T BE FOLLOWED BECAUSE OF THE TABLE NAME**
    ActiveSheet.ListObjects("Table_ExternalData_1").Name = qryStart & "_" & qryEnd

    Selection.AutoFilter
    wb(1).ListObjects(qryStart & " " & qryEnd).Range.AutoFilter Field:=1,
        Criteria1:="*C*"

    Range(qryStart & " " & qryEnd).Copy

'**CODE CONTINUES**

End sub

I'm open to other suggestions too. this is just the tool Record Macros originally gave me years ago, and I work with queries like this only once a year. If it's really not worth the trouble, I'll just write down steps for how to make the connection and update the names in VBA.

Your help is much appreciated!

I need help making a formula that I hope exists.

I'm setting up an inventory and am calculating price per item. (A $10 pack with 5 items $2 per item). I made a (=A1/B1) formula and it works great.

However, this formula comes up with an error as some cells have units in them (for example, 10ft or 100 pairs). I have to keep the units in but don't want to manually calculate the cells with letters in them.

Is there a way to make a formula that will ignore letters in the cell so this error doesn't pop up?

I am trying to multiply columns. I know how to do each column individually, but I can't seem to copy the formula to the next column so I don't have to redo the formula each time. When I drag to the next column, it changes the formula. Column C has a set value. The rest of the columns need to be sum product of column C. When I drag it over it changes to sum product d, then e and so on. The formula I am using is =sumproduct(c2:c13, d2:d13).

Hello everyone. I'm fairly new to using Excel. I made a drop down list and a yearly calender. Is there a way to have the option in the drop down list change to a new calendar every time I choose a new option in the drop down list.

I want to calculate how much I have been out of the country for each year i already have the comings and goings in a chart.

(above is when I went in and out)

my problem with doing it in a function is when I wasn't home for the new year so I cant separate the days to the correct year. (like in entry number 3)

The result above I counted manually which I can't really do because I have to this for hundreds of people which go in and out more than me.

I need this graph that the color black fills the graph outside of the two black lines (over the top black line and under the bottom black line). How do I do this?

I'm using Excel 365, in English (despite the graph being in French) and I'm an Intermediate at using Excel

So I need to make a manual trendline that automatically updates when new data points are added.

I have the

(SLOPE($C$2:$C$6,$B$2:$B$6)*B2)+INTERCEPT($C$2:$C$6,$B$2:$B$6) function that works for making the actual trendline and the =CHOOSECOLS(FILTER(B2:C16,((B2:B16<>"")*(C2:C16<>""))),2) for the filter

I want to know how to combine them so I can have everything be in one helper column (for my sanity this chart is already A1:L46 and I do not want it to get any bigger please (if that is the only option I will accept it though)

And using the trendlines in the chart editor do not work for what I am trying to accomplish

Edit: I want Column E to have the trendline go from E2 to E16 even if I have less than 15 data points in Column C

Novice user here:
I’m creating a sheet that will house employee skill assessments. I want to have the stated item as a note on the cel. Then follow ups and discussion via comments on the sane cel. Eg: (Note): Tom can use a chainsaw but has no cert. (Comments): I’ve booked him for the cert next weekend. Etc.

Excel doesn’t allow for both on a single cel. Any tips ir tricks for something to replace the Notes but still able to have text data attached to a single cel?

Appreciates!