If you’re cleaning the same report every month (deleting columns, trimming spaces, filtering rows), Power Query can do it for you. Here’s how I’d get started, plus the habits that stop it getting messy.

Where to find it
Data tab > Get Data. You can connect to a workbook, CSV, folder, web page or database. When the preview appears, click Transform Data to open the Power Query editor.

A simple order that works

1. Remove any junk rows at the top and bottom (titles, totals).
   
2. Promote the first row to headers.
   
3. Set your data types (dates, numbers, text).
   
4. Remove columns you don’t need.
   
5. Filter out rows you don’t want (cancelled orders, blanks).
   
6. Clean text (Trim, then Capitalize Each Word).
   
7. Add any calculated columns.
   
8. Close & Load to a table, then use Refresh All whenever the data changes.

Habits worth building early

* Leave the raw data alone. Power Query reads your source file but never changes it. Do all your cleaning in the query, so you can always go back.
* Set headers and data types early. Later steps behave better when they’re in place, and dates stored as text cause all sorts of problems.
* One step, one purpose. Don’t cram two jobs into one step. It’s much easier to find and fix things later.
* Name your steps. “Filtered out cancelled orders” beats “Filtered Rows1” when you’re back in six months.
* Aim for a tidy table. One row per record, one column per field. Pivots and formulas get much easier.

If you get a new file every month
Use the folder connector. Put all the files in one folder and Power Query combines them. When you add next month’s file, you just hit refresh. The part that confused me initially was which steps go in the Transform Sample File query and which in the main query. My rule of thumb: steps that make one file look like a proper table (top rows, headers) go in the sample file, and anything that works on all the rows combined (filters, trimming, calculated columns) goes in the main query.

I made a video that walks through all of this on a simple example, in case it’s useful: https://youtu.be/23vd7vAEH2s

Hope people find this useful!

Good news for the many people who have complained about how the Copilot icon overlaps the worksheet area: in the latest desktop Excel version, the Copilot icon is now to the right of the ribbon.

Why Microsoft decided to keep the Copilot icon separate, rather than being on the ribbon, is an interesting design choice. It seems that they only partially listened to the feedback.

My SUMIFS formulas are able to correctly reflect the quantities under each type (based on yellow table) when "Stock In" is a condition, but they all reflect 0 when I change the condition to "Stock Out", even though nothing else is changed. Does anyone know why this is so and how I can change the formula so that the quantities under "Stock Out" can be reflected? Thank you!

Quest 1: The Coneservation

https://everybody.codes/event/2026/quests/1

Past Coding Challenge spiel on etiquette:

https://www.reddit.com/r/excel/comments/1pbgisa/advent_of_code_2025_day_1/

Preface: This is using Excel 2016. I can't do much about that atm.

I'm trying to improve a quoting template so I can get more help maintaining them (will be protecting the worksheets for my sanity).

The concept:

• List of Products/SKUs with a numbered helper column
• Second list for Accessories (if needed) that would have a matching number to the first list
• Output on a scope of work sheet as:
• Item 1
• Item 1 - Accessory #1
• Item 1 - Accessory #2
• Item 2
• Item 3

Is this even possible? Or will I need to add the # reference to the scope sheet and just hope they follow along?

Bonus points if the output also ignores blanks, but I'm not getting my hopes up for that yet.

Edit: Screenshot from Worksheet Edit2: Shoutouts to /u/Competitive-Paint-84, /u/bezuc and /u/MlookSM. All helped me on the right track.

• I used a (now hidden) helper sheet with a column for the original Item# and a COUNTIF() function to create an Accessory# (eg. 1.01, 1.02, etc)
• then the SMALL() function to list those item# in order, ignoring blanks on my output sheets
• One Index/Match to bring in the rest of the info corresponding to the item#.
• I used locked rows (eg. A$1:A$50) to easily copy the Index/Match function through the needed columns, but could have also matched the columns using the header titles.

Overall was pretty quick to implement and is now working perfectly across 2 output sheets. Leaving this extra detail for anyone who stumbles across this thread. Thank you all!

I’m using Abacus Docs to pull fields from vendor invoices, and some vendor IDs have leading zeroes. I need those IDs to stay exactly as printed because they match records in another workbook. Do you set the field up as text in the extraction template, then export, or import the result through Power Query and force that column to text there?

I’m trying to avoid a workflow where Excel silently turns 00127 into 127. If you’ve handled this with another document extraction tool, did the exported file preserve the zeros or did you need to fix the type on import?

Happening every so often when saving a copy in MS365, and the user has to close the application and reopen, and the workbook has lost all progress since the previous save. The thing that is making my head scratch is that apparently, for years before I started here, by boss has worked with her to fix this, thinking that it has been fixed more than once, and it keeps coming back; though not nearly as often as when it started.

Anyone seen this before or have ideas of what could cause this? I'm not very familiar with excel.

My excel used to be doing fine, when i add error bars, it always would be only giving me vertical bars which i need. But these few months it started to give me both horizontal and vertical which is very annoying because its very hard to get rid of manually and considering how much i have the data? Does anyone know how to turn this off? Does this happen to everyone?

hello everyone. i have a simple expenses tracker pivot table i did from ~3years ago but just recently noticed that when i update it, it became in alphabetical order instead of when the data was entered. i don't see a sort option of "when entered" or something along the lines, for the row's sort option.

is there a way for it to remain sorted when the data was entered other than A-Z / Z-A?

for example:

Item A has values in Apr
Item B has values in Jan, Feb, Mar

so in my pivot table, i want it to show as:
Jan Feb Mar Apr
Item B xxx xxx xxx
Item A xxx

but it's now showing like so:
Jan Feb Mar Apr
Item A xxx
Item B xxx xxx xxx

any help will be appreciated, i'm really losing my mind. i even tried to re-do the whole pivot table but it's still in alphabetical order.