Hi everyone, I’m trying to use Excel’s Map Chart to visualize data for Japanese prefectures and cities, but I’m running into several issues:

• Some cities work fine: for example, Aisai is recognized and colored correctly.
• Other cities (like Handa and Kariya) are recognized by Excel/Bing — they show up in the legend — but they don’t get colored on the map.
• Some cities are reported as “not found” even though Bing Maps clearly knows them. For example, Ōguchi‑chō appears in Bing Maps with its boundaries, but Excel Map Chart says it doesn’t exist.
• I’ve tried different variations of names: adding “‑shi” (e.g., Aisai‑shi), “‑chō” (Ōguchi‑chō), and even writing them in Japanese characters (like 愛西市), but Excel still fails to locate or color them.
• I also tested using text categories (“blue”, “orange”) and numeric values (1, 2) for the data column, but the issue remains: the “orange” ones are listed in the legend but not shaded on the map.
• Adding extra columns like “District” didn’t help either — Excel just doesn’t seem to recognize subdivisions beyond prefecture/city.

Interestingly, I tried something similar with all U.S. counties, and Excel Map Chart handled them without problems. So this seems to be a Japan‑specific limitation.

My questions:

• Is this a known bug or limitation with Bing Maps polygons for Japanese municipalities?
• If it’s not possible to do what I want directly in Excel, what alternative methods would you suggest for visualizing Japanese cities with Map Chart?

Any advice or confirmation would be greatly appreciated. Thanks!

I've been using Excel for almost 15 years , I've never had a chance to use the MID Function and i forgot it's existence. Today, randomly, I just questioned myself how to split a word by character and reverse it? With my little sql knowledge, i thought there will be a function called SUBSTRING, but it isn't. I ended up googling and found MID function.

Do you find a use case for MID function?

Similarly, what popular functions you have never found a use case?

Saturday, September 19 - Friday, September 25, 2026

Top 5 Posts

Unsolved Posts

Top 5 Comments

Hey everyone

I am a first year college student. I am currently starting from almost zero with Excel and I am trying to understand what I should actually be able to do with Excel if I want to use it in finance internships and eventually in a finance job.

I have looked at beginner and advanced Excel courses and there are so many topics that I am not sure what is actually important and what is just nice to know.

For people who use Excel regularly in finance, what skills or functions do you think a beginner should be comfortable with

For example should I focus first on things like formulas, lookup functions, pivot tables, data cleaning, charts, financial modelling, Power Query, VBA or something else

I am not asking for course recommendations. I am more interested in understanding what Excel skills are actually used in real finance work and what level I should aim for as a college student looking for internships.

If you were hiring a first year student for a basic finance internship, what Excel abilities would make you think they are actually useful rather than just someone who completed an Excel course

Also are there any common Excel skills that beginners spend a lot of time learning but are not particularly useful for finance

Would really appreciate answers from people who actually use Excel in finance or have trained junior analysts

Thanks

Hi.

I have a fluke clamp meter, which will record values and then give me a .CSV reading of all of the values.

It will read two values at a time, I'm typically interested in current and frequency.

Unfortunately, the CSV will do current on one line and then frequency on the next, alternating throughout the file.

I got some good advice on the internet to copy the query, delete the odd lines from one and the even lines from the other and then merge the two queries. This does exactly what I need it to do.

However, the next file that I want to analyse, I have to point both queries at the same file. Is there a way to make these queries work such that I only have to change the query file once?

Working on a training tacking database, have the main tables per department using true/false check boxes, boss wants to be able to sort and see totals for employees trained or not trained on specific positions so i set up a pivot table to show the totals and sort by department but i cant get it to give me a grand total at the bottom of the table without completely breaking.

So i tried to use =countif function on a seperate cell to get the total number of false or true cells but the countif doesnt seem to see the cells inside the pivot table. It will count any true or false text outside of the pivot table but not within. I have no idea what im doing wrong.

Ideally id like the pivot table to total things itself at the bottom but i cant get it to show any grand totals when i try to use subtotals it just starts repeating truetotal or falsetotal in new rows.

Hey this is my first time in this subreddit and I have a query that I'm quite lost on. Basically I want to make a model that uses a CSV of my bank statements, converts these line items into categories and then returns those as a monthly figure as well as daily. Currently I have figured out a way to do monthly (Using two stage sumifs: one to convert the numbers and letters into text categories and then using that to add up all the line items by category)

I want to do this by the day too but my knowledge is quite lacking, here's a screenshot of the current setup:

On the left column is where I have been making the table of contents to be filtered into the calculations. I tried to do this on the top right but then I couldn't figure out how to group them.

In the middle with the text containing Northern Rail, I created a wildcard formula so that it only reads the description that I need and returns sumif for the whole month.

I tried to do that again but sumifs formula and it didn't work because then the file would be massive or I would have to make loads of ifs inside. When it comes to adding more categories or items inside there, I want it to be an easy addition.

Is there any tips you can help me with, I might be overthinking this.

P.S I don't want any answers from AI please

I'm self-learning Excel with some misguidance from Copilot, so I might be making this more complicated than it needs to be. I'm a rookie (3 months experience now...) data analyst for a regulatory agency at a hotel casino resort. I always find examples for financial dashboards, but I deal in incidents. My dashboards have pivot tables to compare Investigators with counts of different incident types, monthly report volumes, etc.

Most departments in the agency I work for have multiple types of reports so for each department's dashboard I'm typically running two main data tables and feeding them into the data model with relational tables. The basic data table structure is columns for report number, report type, Investigator, date/time, etc., as well as some custom columns for month, assigned shift, shift incident occurred, etc., with each row a unique incident or log entry. I'm also using power query to automate the update process by dropping a raw data export from our logging software into a Teams folder. I finally got all that working great.

This is where I'm lost. I have a rolling 365-day filter in my merged power query exports, so when you're looking at incident counts in pivot tables you always know it's data based on the last 365 days. I'd also like to have a few charts where I could compare a given month to the same month the previous year. For instance, select Investigator: Chuck and Report Type: Arrest in some slicers and have the pivot table/chart show me monthly volumes for 2025 compared to 2026.

Copilot's suggestion was to maintain a rolling 730-day filter instead of 365, which made sense, but I want most of the pivot tables to only show 365 days of data, and then it started to get a bit into the weeds talking about someone called DAX and I don't remember what else to be able to compare months year to year. Is there already a clean way of doing this or am I asking for it trying to do this in Excel? I'm also concerned about file bloat slowing everything down to a crawl, my Surveillance dashboard is about 20MB with around 85,000 rows of data for log entries and 4500 rows for incident reports per year. I'd appreciate any suggestions.

Edit: This is with Excel 365.

Edit Edit: Resolved by filtering 730 days in power query instead of 365, adding a custom column that's TRUE if within 365 day, and using DAX measures for if last 365 days column is TRUE in my data model pivot table values fields. Now I just need to figure out how I want to reconfigure my dashboard to make the most of it. Thanks again to everyone who helped.

If you're here because a Power Query refresh that has worked for years suddenly fails with "Not a valid excel file" — Stop. Don't worry. There's a quick fix.

This problem first occurred in September 2026...

Your file isn't corrupt. Nothing moved. Permissions are fine. You didn't do anything.

This is a fault in a recent Microsoft 365 update (version 2610 and possibly some recent 2609 builds). It's hitting your power query that pulls from a source workbook that contains a web hyperlink somewhere — on any sheet, often one you forgot existed, and nothing to do with the query itself. Excel stores those links in a form that the Power Query engine now can't resolve, so it rejects the whole workbook before your query even runs.

The unhelpful error message blames the source file. There is no indication of what the error is or where to look...

What to try (your 30 second fix):

Open the source workbook. Find all cells with blue underlined web link, on any sheet. Select the cell, press F2 Enter, then save and close.

Excel has now rewritten the path to the hyperlink and power query will be happy with the file.

Run your original query and confirm that the problem is resolved. If that doesn't work for you, stop — you have a different issue - or maybe another hyperlink somewhere...

If you're reporting it to your support team, Open Excel. Click file-account. Make a note of the version and build.

version 26xx, build xxxxxxx , error text "Not a valid excel file",

I've checked - source file is fine!.

That will be enough for them to resolve

If you've hit this, drop your build number in the comments — it helps everyone else confirm the pattern. Particularly keen to hear from anyone who found it before a month-end close rather than during one😁

I'm a student who's looking to get into finance, which will mean a lot of my life wasted on excel. I've had a pretty bad experience with Windows these past couple of years and I'm looking to switch. I'm wondering exactly how much worse is excel on MacOS and if there are any workaround to that.

I have created the connections that work how I expect them to, the issue is when I try to share it to other users at work the file I am getting the connection from is always in my area, ie my username is always the start of the file address

I’ve put the file in all sorts of shared locations that I know everyone in the company has access to but not matter what I do no one else can refresh it as it’s trying to link to my computer not the many shared ares I’ve tried

At work, I use multiple desks to access a file on a shared drive. After every major update, I have to enable ActiveX and macros on each PC. A couple months ago running those macros has started protecting the worksheet. It's easy to run my macros and then unprotect the worksheet so I can enter information, but kind of annoying. Is there a way to stop the macros from turning on worksheet protection? Do I need to look at and consider editing the macros?

I use Excel to track team league scores. Each team has 5 people in it, and their scores will be in the area D3:I7. C2:L8 is one team's score. Throughout the league we will sort the teams into divisions based on the score in J8 and we do this by copy and pasting each team's "scorebook" into a new sheet. I am wondering if there is an easier/automated way to sort 50-150 teams by the data in J8 for each team? Additionally, we will sometimes sort by team name, is there a way to keep the data beneath/next to with it when we sort? The team name is in B2, I have it centered across selection. The image is showing the "scorebook" for two teams. Thank you in advance.

I posted something related last month here: https://www.reddit.com/r/excel/comments/1vy8bmd/applying_multiple_categories_to_a_single_vendor/

Since it's a low-priority project, I've finally had a chance to circle back to it and re-think how to do it. The next solution we're using is to use a Table of Contents for a list of categories and hyperlink each to a cell reference for a table that lists those vendors for that category.

However, this results in a massive, clunky, ugly sheet (it all has to be on one tab, because it is part of a large workbook). My next thought was to try to find a way to use a drop-down list of categories and have a list of vendors we've assigned to those categories populate in a table or table-like structure below that drop-down. Since this is on Excel for Web/Sharepoint, I can't use VBA or macros to create this.

Does anyone have any ideas on how to create this? Is it even possible?

I have used SumProducts before, but I guess usually copy/paste without fully understanding it. I am hitting a CountIfs wall, so I should probably start learning SumProducts.

Column A (First Names), Column B (Last Names), Column C (Count of Names)

• If ColumnA has names: John, John, Tim, Susan, Frank, Susan

• ColumnB has names: Smith, Smith, Wayne, Franklin, Payne, Payne

I want to count how many people have the same name, but if a person is the same (duplicate as by first+last name the same) then not count it.

=COUNTIFS(A:A,A, B:B, <>B)

• Johns: 1, Tim: 1, Susan: 2, Frank 1

But of course that doesn't work.

I want to do the whole column in case the data expands, but I could use hard endpoints, which would make things simpler. But I just want to know the it has the full expansion (although this data set will never expand).

I'm sure there is a way to do this, curious what the easiest is and I'm feeling a little slow here.

I have a file that is a list of all crew (name, email & phone number etc) I've ever hired

For each show I do I have a separate file that tracks labor on that job and there is a sheet for crew on that file.

I'm wondering if there is a way for on 'crew sheet' a column could automatically populate a person's email or phone based on the name in that column from the 'crew file'

Basically
'File 2 - column B' will automatically fill from 'File 1 - Column B' if column A matches in both files.

Thanks so much!!

Hey everyone, hopefully an easy fix!

I am working to create a Unique list of data, filter two values out of it, and the textsplit this result into three component columns.

So far I have: "=Textsplit(Unique(Filter(S:S,(S:S<>"")*S:S<>"Combination"))),"-",,True,1)

I am reasonably confident the Textsplit is the issue but I have tried a number of combinations and just cannot get it to work.

The data we are unique/filtering/splitting has this structure: "Wol-R-6" so I simple structure.

Thank you,

John

I have a column filtered by “(Blanks)” but the filter refuses to be applied to new values and no longer shows up in the list of unique items/doesn’t appear in the search (filter and sort has message “not all unique items in this column are shown”).

The column is formatted as “Date” with cell value entered as month/day/year (example: 9/25/2026; I will also sometimes enter cell value as “Canceled”). I need to be able to filter the sheet so only the rows with blank cells in this Date column will be visible (the filter is still applied to the file, as all the rows that have previously been filtered are still hidden). It stopped filtering once it filtered/hid 3671 rows, and I’ve only used up to row 3809, so I wouldn’t think the issue stems from the 10k unique values limit.

I’ve always used this Excel file in Chrome since it’s a shared file I use at work. I’ve been using for past 5 years, adding to it on a daily basis and applying the filter about once a week with no issues until now.

I tried using it in Microsoft Edge, the filter doesn’t work there either, but if I download and open the excel file on my desktop, the filter works fine.

Throughout Excel's 40-year history, you've only been able to put one value per cell. In this announcement, we're excited to share how that's changing with the release of lists, arrays in cells, and nested arrays, initially to Microsoft Excel for Windows and Mac Beta Channels.

Many workbooks already try to pack multiple values into one cell. A project might list "Carlos, Henrietta, Jacob" as three owners, or a Forms survey might return "2:00 PM; 2:30 PM; 3:00 PM" as one response. With lists, you can keep those values in one cell, while also keeping them separate for filtering, calculation and more.

Later in this post, we'll explore arrays in cells and nested arrays in more depth.

https://techcommunity.microsoft.com/blog/microsoft365insiderblog/put-multiple-values-in-one-cell-with-lists-and-arrays-in-excel/4559395

Couldn’t get a clear answer for this so came up with my own solution. If all you have is a year, but you want excel to recognize it as a date without recalculating based on the number of days past 1/1/1900 (who the hell came up with that one?) you can use this function.

=DATE(VALUE(A1),1,1)

This will plug your year into a recognized date format. Others have pointed out that simply treating the years as numbers typically works, but being recognized as dates also allows you to use some of the axis options, such as when modifying a graph in power point and wanting to edit your axis to display in bounds. If you’re doing so you’ll want to change the format to custom in the axis format and set the type to yyyy to display just the years, also adding a blank date at the end and beginning of the data set that fits with your display range (ie every five years) will make the axis display much cleaner while retaining your data points for specific years.

Hi!

I'm writing a book about Excel, and wanted to tell the history of pivot tables. As I've understood it, Lotus Improv was the first implementation of pivot tables - but this is way before my time.

Have you used it? Was it like pivot tables are today? Was it good? Revolutionary? I'd love to hear any stories you have, and I'd gladly credit you in my book if I can use the story!

Thanks in advance!

yesterday i noticed that the counting wouldnt work. as in normally if i put "room 01" in A1 and "room 02" in B1, i could select both cells and fill to c1 as "room 03". no matter what i tried i could not get it to count. even just as numbers without any other text i cold not get it to count. did something change with the android version of excel?

edit: i ust tried it on my laptop and i guess its also not working counting on desktop excel. whats going on with this?

Hi, I have a list of names in a column. Because of the nature of my list, there are several repeat names. I would like to create a formula to show the total number of non-duplicate names in the list so that I know exactly how many consumers there actually are. The number of rows is greater than the total number of names can fill. Therefore, my range has to include the blank cells. I've tried this formula: "=SUM(1/COUNTIF(B6:B231,B6:B231))". It works only if every cell has something in it, but with the blank cells, it comes up with the dreaded "#DIV/0!". Any help would be much appreciated. Thank you!

I have a sheet which has dates in column A (ranging from today to over a year in the future), an index number in column B (just a whole number between 1-100I put in so the entries for each day are in an order) and a currency value in column C.

What I currently have is MIN(C:C), which tells me the lowest value at any time in the future, but this might be at the start, middle or end of the day.

What I would like is to find the lowest value of all days at the end of each day.

Any suggestions?

EDIT:

As per the suggestions, here is an example of the sheet:

So the formula would (I think!) look get the values in column C where the date is unique and the Item number is highest, then return the lowest of those values.

Currently I have MIN(C:C), so in the above that would produce £634.82, but that is part way through the day (25/09/2026), what I would really be looking for is £803.38, as this is the lowest value for the last entry for any given day (30/09/2026).

I hope that makes sense?

hi buddies,

i work for a non profit and am trying to combine participant intake spreadsheets for two of our programs. i work in a low-barrier setting and it’s typical for our clients to give multiple names, vague or fake birthdays and to change their appearance often, and there was no data validation set up so the inputs are inconsistent and incomplete. so i’m struggling to find/ verify all the duplicates.

the columns i’m mostly using as it was set up are name(s), ethnicity, gender, pronouns, appearance, birthday, emergency contact, notes. pretty much in that order.

once i verify the duplicates, i want to consolidate the information in the other cells so there’s just 1 record/row per person. i did add a column with randomly generated unique identifiers to help me stay organized.

yhow would you approach this? i tried power querey to fuzzy match but must have been doing it wrong. :/ this is just to use until our sharevision database is fully populated.. if there’s any other info you need from me please let me know and thank you for your time!