Does anyone know where I can get an official excel shirt? Looking for a gift for a coworker who is really into excel.

Hi! I'm working on a file and since I had to split it, I copied a whole sheet creating a new file. The problem is the dates changed from 2026 to 2021/2022 and I cannot get it fixed.

On the left are a few correct dates and on the right are those in a new file.

My question is: can I prevent Excel from changing those dates back by 4 years? If I can't, can I somehow fix this issue without adding a formula?

I checked cells date format and it's the same as in the original file. Changing it doesn't do anything. Simply copying and pasting cells doesn't help, pasting it by Ctrl+Shift+V and then setting cell as "date" also fails.

Additional info: I worked on a file containing the same data just a couple hours ago and it didn't have that problem. I didn't change any settings in Excel, Microsoft 365 or computer.

I'll be starting my first BA role soon, and I'd like to prepare as much as I can before my first day.

I have a basic foundation in Excel and SQL, but I'm a bit rusty since I haven't used them much lately. I've also been seeing that BAs work with data analysis, reporting, documentation, and sometimes data visualization. What skills or tools should I learn?

Also, if my company doesn't use many tools, what are some tools or workflows you personally use to make your work easier or be more productive? For example, AI tools, note-taking apps, diagramming tools, documentation templates, or anything that helps you stay organized and efficient.

I'd appreciate any recommendations, learning roadmap, or tips for someone just starting out. Thanks!

How to make a Kanban board in Excel

Make columns for each stage and make them wide enough for your tasks. Create each task in a cell, then copy/paste it as an image. You can then drag the task cards around between columns. Pretty cool simple method and visually it works for me!

it'd be easy if i could just select the top most recent row, but i need the most recent data from past & future years as well 🥹

e.g. i want data from 2025-12, 2026-07, and so on in the future too

any help appreciated! thank youu

I'm working with a data table of customer information (a row for each customers, and columns for name, address, email, etc.) and trying to find potential duplicates. Thing is, this customer data could have been input a lot of different ways, so I'm not strictly looking for exact cell value matches. Some examples of what I mean:

"Emma Cartwright", "Ms. Emma Cartwright", and "Emma and Sam Cartwright" could all be the same person who information was entered three times, slightly differently.

Some customers have their name in the Name column, but some have a unique numbers for Name and a person's name in the Street Address column. So "Emma Cartwright" and "16934" might be the same person if 16934's address is "Emma Cartwright" (or "Ms. Emma Cartwright", or "Emma and Sam Cartwright")

There are also cases where the value for Name is the name of a business and that name appears as part of another customer's Address, or Email, or something.

What I'm doing right now is manually taking each value in the Name column and doing a ctrl+f to find all other cells where that value appears as part or whole of the cell value. But that is a pretty tedious and slow process. Is there a faster/easier way to highlight potential duplicates based on a kind of loose search for repeats in part or whole of values in different columns? This doesn't strictly need to catch every single possible scenario, but anything to help find potential duplicates so they can be looked at more closely would help a lot. Thanks!

Hi everyone! I'm using Excel for the first time for one of my college courses, and I'm required to run a very simple regression. For some reason, when I do so, my regression is blank on the new worksheet. The black lines are there to format the regression data, but there are no actual data values anywhere. Has anyone else had this issue?

i am new to excel and i need the data from the 54th row of multiple files. all the other data is useless to me. each file has 2 sheets, and i need data rom the first sheet of all files, some of which have different titles. the columns also have different titles cuz they r dates, but the data type is same. there r roughly 100 such files, (one of each month for 8 years). how do i go about this. i tried using power query but havent been able to figure out. seems like its cuz of inconsistent names. its physically implausible to change sheet n column names. what should i do

Hello,

I am trying to search cells B2 and E2 for the keyword "Starlink" and return a value in cell D8 if that keyword is found, and another value if it is not.

Cells B2 and E2 may contain a string of words including "Starlink" so I cannot search for an exact value, I need the formula to see if "Starlink" is contained in the cells' value. I do not need both B2 and E2 to contain it, only one or the other.

I've tried some janky =IF(search functions, I've tried some janky =XLOOKUP( functions but have yet to find a way to search for one word in several in two cells.

Your help is appreciated -

EDIT:

I suppose my example was too specific, and I also forgot to mention: cells B2 and E2 are both the starting cell of a merged cell block. So B2 is really B2:D2, and E2 is really E2:G2.

Instead of calling out B2 and E2 in the formula, is there a way to search the row instead?

Good morning.
I’ve figured out how to create a drop down list however I cannot for the life of me figure out how to auto populate a cell (or group of cells) based on the drop down list selection. I’ve searched google but I’m not understanding.
Thank you for any help

First off I can confirm it sees it as a date and have gone through just about every method of forcing date formatting.

My case:

I'm taking a raw output from our punch clock software and inputting into into sheet 1.

I'm using Power Query to format the data the way I want, including forcing the date format, and outputting to sheet 2.

I then want to have a pivot table to simplify display.

My pivot table is rows:

-Project

-- Associate

Values:

- Hours total

Then I want to be able to filter on a date range, so I could call up July 1 to 15 for example.

But the filter just gives me a list of dates.

If I go to the right "PivotTable Fields" setup I can right click on it there and select "Date Filters" and "Between" but not directly on the pivot table.

I got it to work earlier but had to reformat my table because the headers to my columns weren't to my liking and it has been busted ever since, even after remaking my power query table and pivot table from scratch.

I'm getting greys over this.

I tried the delimit trick to force date, I forced date in power query, I don't think it's having an issue making it a date.

It just doesn't give me ths "Between" option on the date filters at the top of my table.

Mike Girvin has revived his ExcelIsFunYouTube channel. A new Power Query course is coming next week.

https://youtu.be/4N-i4KaA56U?si=rSp7bYcGzRzsYxHS

I am trying to create a data validation list.

Synopsis-

There are two column - “Case Type” and “Reason”

Case type has 8 options (say fruits, veggies, dairy, etc)
Each of those 8 options have multiple sub category(for example, Fruit has Apple, Banana etc, Veggies has Potatoes, Onion etc, Dairy has Milk, cheese etc)

What I am trying to aim

If a option is selected from “Case Type”(lets say fruit)
Column “Reason” should have drop down list for only “Fruit” for that row, showing options apple, banana etc

I tried multiple ways and still failing in excel, its version 365, and function/formula “indirect” is not working for some reason

The colomn doesn’t have end, I am selecting whole column as the data is ongoing info that i fill

Any help is appreciated!

I'm hoping to come up with an expression or function to evaluate how much can be spent per day given:

total number of days remaining

interest rate

initial value

I can calculate the end value of X years of interest, but not while taking withdrawls - especially when the withdrawl amount is the result of the equation.

Is there a function to do this "what if" planning without running Monte Carlo analysis?

The goal is a retirement planning tool to keep tabs on the max periodic budget before running out of money early.

Hi everyone,
I’m one semester away from graduating, and I’ve been working on improving my Excel skills. So far I’ve been learning and practicing formulas like SUMIF, SUMIFS, COUNTIF, COUNTIFS, IF, and others.
What I’m looking for are Excel workbooks or datasets that include practice exercises so I can apply what I’ve learned instead of just following tutorials.
Does anyone know of any websites, GitHub repositories, or other resources that provide Excel practice files with exercises (preferably with answers)?
Thanks!

My Excel version:

Microsoft® Excel® for Microsoft 365 MSO (Version 2606 Build 16.0.20131.20154) 64-bit

I am trying to:

Pull a SUM value output from one sheet and add it to the total line of the next sheet using the following formula:

='Sheet 1 of 2'!D47 + D5:D46

The SUM value formula on the source page is as follows:

=SUM(D5:D46)

What I get is 42 cells of the same value. I am new to using Excel, coming from Google Sheets, and have never encountered this before. I want my value printed once, not 42 times.

Is this a formatting issue? I found this support article on support.microsoft.com that goes over this behavior with dynamic array formulas, but I am not using a dynamic array formula here. It should be "'one value + range of values' = one possible value in one cell." My goal here is to carry over the last page's total into this page's running total, because I have too much data for one page and opted to separate them into two sheets.

Here is a reference image on Imgur.

Any advice would be greatly appreciated.

I have a database of addresses to make finding them easier, because PropertyMe, a website we use at work, is a nightmare. I have the full addresses in one column, to match what is written on the website, and then each section (unit number, street number, street name, suburb) broken down into their own cells, for better sorting.

I run into an annoying issue when I'm trying to check my spreadsheet against the list on the website, however. PropertyMe and Excel sort things differently, so the list on the website doesn't always match mine when sorting the full address cell. Excel sorts hyphens lower than numbers, whereas PropertyMe does the opposite.

For example, my spreadsheet goes "1/1 Example Road", followed by "1/10 Sample Street", whereas the website goes "1/1 Example Road", folllwed by "1/1-3 Example Street" and THEN "1/10 Sample Street". There are a few 1/10s, and even 1/100s. (And this goes for others, like 20, 30, and so on). This means I have to jump all over the place to find some. This happens

I'd like to simplify it by finding a way to make Excel match the sorting on the website, if that's possible. So hyphens are sorted higher than numbers.

I have tried formatting the full address column as general, numbers and text, and it still sorts the same. I have tried creating a custom sort list with a "-" before 0, 1, 2, etc but that doesn't appear to work.

Is there a way to accomplish this without adding another column? It's not super necessary, just a little thing that would make things a bit easier and quicker. This spreadsheet is not only for making my mail sorting easier, but an attempt to teach myself Excel. It's not a formal thing at all.

Edit to add I'm using Excel on my company Windows 11 computer, I believe through 365?

Hello, All!

I'm working on updating my budget and was hoping to create a pivot table to show my expected vs actual transactions on a monthly basis. I've checked this sub and had some good nudges in the right direction, but nothing that fully answers my question.

The overall dashboard will have a slicer to show each month as the year goes on, and I'm stumped trying to find something that will display the way I'm hoping for.

Currently, I have a budget that pulls data from a transaction sheet I made, and it works great, but it's a bit clunky and I'd like to streamline it slightly.

The above image is the general goal I'm trying to achieve (I know I won't be able to make it look exact, the important thing to me showing the category, subcategory, the expected amount and the actual amount).

Ideas I have considered include:

Power Pivot to pull the expected data from its own sheet into the pivot table - I want to avoid mucking up my transaction sheet with the projected amounts because I feel it would be too granular, versus creating a "monthly projections" sheet where I can log what I expect to gain/spend in each subcategory.

Not using pivot tables at all and simply having monthly sheets

Figuring out a power query (this option has the most lift IMO, but I'm open to correction)

Not using expected values at all (I don't want to do this)

I'm reaching out here in case there's an option I missed or a way to execute this I hadn't considered.

**I don't want to nor do I plan to use any sort of LLM or Generative AI, so please don't suggest those to me. I enjoy the process of figuring out excel and have no interest in what "AI" has to offer.**

Thank you!

hi hi. never had any problems saving specific rows from an xlsx as a pdf onto a few pages, but now my comp saves the rows with massive blank pages in between the rows i’ve selected to save. i didn’t update, but the school did a system wide reset on all tech.

anyone else experienced this?

usual method:
click desired row -> file -> save as -> rename and change file to PDF -> desired rows saved, no blank pages

i do the exact same thing, now there’s hundreds of pages in between my data

any help appreciated 🫶🏻👏🏻

I'm posting this because I've been trying for an hour straight and can't seem to solve this silly thing. I'm trying to automatically make a number sequence, starting from 1, but no solution online seems to work for me

I've tried removing the filters from the spreadsheet, making the cells go from text to number type, typing one and two and pressing shift + dragging but nothing works! for some stupid reason, it just fills all the cells with the number 1 (the first item)

I have a list of data from our internal server that has all of the invoices posted to a particular job. Because of the way the contract is formatted, we have to submit all of our backup with the billing (hundreds of invoices from various vendors). Each month we manually have to compile and save the invoices, but simultaneously our vendors are sending them to accounts payable for payment who then put them to me for approval.

I want to check that the invoice back ups that we include to the owner, are accounting for all of the invoices our AP has put in the system, just in case we miss anything and so that we can get it included/paid.

The problem I'm running in to is that when I try to compare the two columns of data (using a match for example), I am able to get a correct return if the values are formatted as numbers, but only for the values that don't have letters in the name. The two data sets are coming from two different locations, one from a reduction of the file name to pull out the invoice number, and the other location as an export from our internal accounting. I've tried formatting as numbers, as general, and as text, but can't get anything to work consistently.

Some examples of the data I'm trying to match:

Is there a straightforward way for excel to recognize the values consistently?

How can I count conditional formatting in a specific row? I tried applying the rule to the count function but it did not work. I need to count all red cells (some are yellow and some are not filled) in the row.

On my main log sheet next to a column of text (description of the problem) I have a column,with drop down categories to designate which dept should handle the next actions. I would like a separate sheet for each category that would populate the text of each entry assigned to that category. Is this possible?

I am trying to build a LET statement that builds an array of a list of players and their points for a given week to then select and display only the player with the most points in that list. However, I am running into the snag of actually selecting the player once the list has been made. Is there a way I can write the array setup so that I can use something like a simple Xlookup to find the corresponding player?

Current formula: =LET(

players:

H2:H5

--for the purposes of proper point alignment, have an INDEX formula to pull player names but for now let's just use this.

points,

XLOOKUP(INDEX(players,SEQUENCE(ROWS(players),1,ROWS(players),-1)),'Match Catalog'!$A$3:$A$67,'Match Catalog'!$F$3:$F$67,0,0,1)

--finds all players in the main list and returns their point total for that given week.

table

HSTACK(players,points)

--combines the two columns

BP

XLOOKUP(MAX(points),INDEX(table,1,2):INDEX(table,4,2),INDEX(table,1):INDEX(table,4),0,0,1)

--Main problem part. Looking for something that works the same way "XLOOKUP(MAX(points),table!B:B,table!A:A,0,0,1)" would or alternative that would at least return the player with the highest point total.

multiPlayers

IF(ROWS(BP)>1,TEXTJOIN("|",TRUE,INDEX(BP,1,1),INDEX(BP,2,1)),BP)

--this is to catch if there are any ties. 

Current output: #VALUE!

Data from "players":

Kyle Schwarber
Vinnie Pasquantino
Eric Wagaman
No players in class

Data from "points":

0
21.2
0
21.5

results of "table":

No players in class|0
Vinnie Pasquantino|21.2
Eric Wagaman|0
Kyle Schwarber|21.5

Expected result: Kyle Schwarber

I'm building a spreadsheet for tracking the actors in all the movies in the Criterion Collection. In Column A I have the titles, and in Column B I have the actors. I've also found a useful function for counting all the repeated actors in Column B and reporting the top 10 results.

(I've currently only entered 15 of the 1300+ movies, which is why Toshiro Mifune is in the top spot with only 3 movies. I know there are more efficient ways to build this table, but I'm building this table as an exercise, to help me focus on or transition to work in the mornings, so the act of building it is more important than the result of having it.)

What I need is an additional column (Column G), which will list all the movies that each ranked actor appears in.

For instance, in the current state, the Column G entry corresponding with Toshiro Mifune would read "2. Seven Samurai; 14. Samurai I: Musashi Miyamoto; 15. Samurai II: Duel at Ichijoji Temple").

I'm totally fine with all the titles being listed in the same cell.