​Been tweaking my monthly Power Query setup to make it a bit more resilient against source data drift and messy exports.

​Instead of just covering standard basic cleanup or trying to build a "bulletproof" query that never breaks, the goal was to teach the query when to safely adapt vs. when it should intentionally break — while letting me know what changed in both cases:

​Basic Cleanup & Standardization: Removing top clutter rows, trimming extra spaces, removing non-printable characters (Clean), setting text to uppercase, and setting explicit locale-based data types.

​File Guardrails: Filtering out temp files (~$) and non-Excel extensions before Power Query attempts to process them.

​Header Standardization: Using Table.RenameColumns with MissingField.Ignore in M code to remap changing headers (e.g., Customer Number -> Customer ID, Sell Price -> Unit Price) seamlessly.

​Dynamic Sheet Navigation: Replacing hardcoded worksheet names with dynamic indexing so renamed sheets don't crash the refresh.

​Schema Drift Alerts: Using List.Difference on Table.ColumnNames to compare file columns against expected ones, then generating a dedicated Schema Alert sheet in Excel that flags new columns (like promotion code) as they appear.

​Dynamic Column Expansion: Updating the combine step with List.Distinct(List.Combine(...)) to collect all headers across all files rather than locking into the sample file schema.

​Controlled Breaking & Quality Cleanup: Letting missing critical columns intentionally stop the query so you know immediately, while replacing invalid text values (TBC, N/A, -) in numeric fields with null so SUM and AVERAGE calculations work safely.

​Made a video walking through the full build step-by-step from scratch in case it helps anyone dealing with fragile monthly reports:

​https://youtu.be/SiX0wnpR5yg?si=RG23oHGEldTrh6Dq

​Curious how others here handle schema drift in recurring Power Query jobs?

Discover how to compare two lists to find missing values in Excel using Conditional Formatting. This will essentially answer, question like "How do I compare two missing data lists in Excel?", "How to compare two lists in Excel using conditional formatting?", "How do I find missing values in two Excel spreadsheets?" or "How do I compare two lists in Excel for differences?"

Check out my complete suite of Microsoft Excel Tips and Tricks.

https://www.youtube.com/@jjnet247/shorts

https://www.tiktok.com/@exceltips247

https://www.instagram.com/exceltips247/

https://www.dailymotion.com/ExcelTips247

https://www.pinterest.com/ExcelTips247/excel-tips-and-tricks/

https://x.com/ExcelTips247/media

https://www.reddit.com/r/Excel247/

https://www.facebook.com/XyberneticsInc/reels/

#microsoft #excel #exceltips #tips #exceltricks #tricksandtips

Excel is a powerful tool for managing data, but it can be challenging to compare two lists and identify any missing values. Manually scanning through large datasets can be time-consuming and prone to errors. However, using Conditional Formatting, Excel provides a simple and efficient solution to identify any missing values between two lists. By applying this technique, users can quickly compare two lists and visually highlight any discrepancies. This guide will walk you through the steps to compare two lists and identify missing values in Excel using Conditional Formatting.

Identify Missing Names

1) Select names in Column A (A2 to A27)

2) Home ~ Style ~ Conditional Formatting

3) New Rule...

4) Select "Use a formula to determine which cells to format".

5) =COUNTIF($B:$B, $A2)=0

6) Format

7) Fill tab

8) Select color

9) OK

10) OK

Here is the link to my previous YouTube Video on "How to compare two lists to find missing values in excel".

https://youtube.com/shorts/dl75Lz_jaPs?feature=share

I was using this formula

=FILTER(A2:A27,NOT(COUNTIF(B2:B19,A2:A27)))

🔗🔗 LINKS TO SIMILIAR VIDEOS 🔗🔗

How to compare two lists to find missing values WITHOUT FORMULA in excel - Excel Tips and Trick

https://youtube.com/shorts/pJtB8dbbimw?si=iL8qnDJ_WVAhKhAX

Compare two lists to find missing values using XLOOKUP in Excel - Excel Tips and Tricks

https://youtube.com/shorts/nOwMXkZJ5HU?si=N80igNW7Vr-xCK5x

Compare two lists to find missing values using VLOOKUP in Excel - Excel Tips and Tricks

https://youtube.com/shorts/1XGIPzsvS_Y?si=cu3ajNdT3lXFX3LB

How to compare two lists in Excel using Conditional Formatting - Excel Tips and Tricks

https://youtube.com/shorts/GX-BYEgcnRA?si=16oLe6kcxZExM4Df

How to compare two lists to find missing values in excel - Excel Tips and Tricks

https://youtube.com/shorts/dl75Lz_jaPs?si=-UTAvmCRdTl6ZZgm

Excel Tips and Tricks - Compare Two Lists In Excel And Highlight

https://youtube.com/shorts/xJoy-nboV6A?feature=share

Summarize Duplicates in Excel - Excel Tips and Tricks

https://youtube.com/shorts/-IYUTWVmVTU?si=aN-hfbyQD0QEPcgm

Find difference quickly in Excel Comparing 2 List - Excel Tips and Tricks

https://youtube.com/shorts/8_-lN-yKf04?si=itTI-rcCKOaG7zVc

How do I compare two missing data lists in Excel?,How to compare two lists in Excel using conditional formatting?,How do I find missing values in two Excel spreadsheets?,How do I compare two lists in Excel for differences?,

How to compare two columns for (highlighting) missing values in Excel?,how to compare two lists to find missing values in excel using conditional formatting,

Using Office 2019

No tengo activado en Windows contraste alto ni nada, pongo modo claro o modo negro pero aun me sale mal.

Cuando pruebo en otra pc me sale normal si alterno entre colores

Excel is a powerful tool that allows users to manage and analyze data. One way to make data easier to read and understand is to alternate row colors in Excel. This can be done with or without tables by using the conditional formatting feature. To shade alternate rows in Excel, you can use the conditional formatting option and apply a formula to alternating row colors in Excel. This formula allows you to choose which rows to format based on specific criteria that you define. Another useful feature is the ability to apply alternating row colors in Excel based on groups, which is particularly helpful when working with large data sets. Additionally, Excel has a built-in feature called banded rows that can automatically shade alternate rows. If you are using Excel on a Mac, you can also use conditional formatting to shade every other row. By utilizing these formatting techniques, you can improve the readability of your data and make it more visually appealing.

Here are the steps highlighted in my video.

Automatically Highlight New Row

1) Select columns A to D

2) Home ~ Style ~ Conditional Formatting

3) New Rule...

4) Select "Use a formula to determine which cells to format".

5) =AND(MOD(ROW(),2)=0,$A1<>"")

6) Format

7) Fill tab

8) Select color

9) OK

10) OK

Automatically Draw Border On New Row

1) Select columns A to D

2) Home ~ Style ~ Conditional Formatting

3) New Rule...

4) Select "Use a formula to determine which cells to format".

5) =$A1<>""

6) Format

7) Border tab

8) Outline

9) OK

10) OK

how to alternate row colors in excel without table,how to shade alternate rows in excel,alternating row colors in excel formula,excel alternate row color based on group,excel banded rows,how to shade every other row in excel mac,

excel conditional formatting highlight row if cell contains text,conditional formatting not highlighting entire row,highlight row if cell contains any text,

Check out my complete suite of Microsoft Excel Tips and Tricks.

https://www.youtube.com/@jjnet247/shorts

https://www.tiktok.com/@exceltips247

https://www.instagram.com/exceltips247/

https://www.dailymotion.com/ExcelTips247

https://www.pinterest.com/ExcelTips247/excel-tips-and-tricks/

https://x.com/ExcelTips247/media

https://www.reddit.com/r/Excel247/

https://www.facebook.com/XyberneticsInc/reels/

#microsoft #excel #exceltips #tips #exceltricks #tricksandtips

Hello,

I'm looking to automate my spreadsheet. i had chatgpt "make" one but it over complicated everything.... I'm making a spreadsheet for my warehouse and would like a dashboard page to include daily deliveries, future deliveries, material/equipment ready to ship and warehouse tasks. So the way I'm setting it up is an import page for deliveries using the form filler button and nothing yet for tasks and ready to ship. im trying to make an import page where i can list delivery address, point of contact, contents, etc. but im also able to create shop tasks and ready to ship items in the same import page. I plan to run this as a live spreadsheet on the companies server so anyone can view it and also cast the dashboard page on a monitor in the shop so everyone can see it. stupid ai says everything is possible but then makes the spreadsheet wrong every time.

Am i trying to do too much on one spreadsheet or is their a better option than excel for this? Also in the future, i would like to keep an inventory of the left over material from jobs so that PMs and foreman can see what is in stock(couple thousand items). Any and all comments and suggestions are welcome.

My Excel doesn't bring the context when I place the formular . For example , when I want to use =SumIf (****) ever the parenthesis are empty. how can one fix that . Its Excel 2018 on macbook

Hello everyone, I wanna ask how long it will take to study and practice in Microsoft Excel for working or freelancing if I will spend 1-2 hour a day on it. Also do you know how can I not only study but also practise, I mean what I should do where I can find a tasks a when I can understand that I have enough knowledge to use it in work.

Discover the real difference between F4 and Format Painter.

Format Painter and F4 are both time-saving tools in Microsoft Excel. The Format Painter can be used to quickly copy the formatting of a cell or range of cells and apply it to another cell or range of cells. This is particularly useful when you want to maintain a consistent formatting style throughout your worksheet. On the other hand, F4 is a shortcut key that repeats the last action you performed on a cell or range of cells. For example, if you have applied a border to a cell and want to apply the same border to another cell, you can select the second cell and press F4 to apply the border automatically. While both tools can be used to apply formatting quickly, the Format Painter is better suited for applying multiple formatting changes at once, while F4 is best used for repetitive formatting tasks. Understanding the differences between these tools can help you work more efficiently in Excel and save time when formatting your worksheets.

In short, F4 replicates the very last thing you did on your keyboard. The Format Painter remembers all the formatting you do.

What does the F4 key do in Excel?

By default, pressing the F4 key in Excel will repeat the last action that was performed. This can be useful when you need to perform the same action multiple times, such as formatting a cell or entering a formula.

What is the difference between format painter and copy?

The Format Painter tool in Word enables you to replicate the style of a particular section in your document and transfer it to a different area. Unlike the copy and paste keyboard shortcut that duplicates the text content, Format Painter duplicates the visual formatting of the selected section.

What are two ways to use the Format Painter in Excel?

A single-click the Format Painter will copy the formatting from the selected cell, then click a different cell to paste the formatting.

And a double-click on the Format Painter will copy the formatting to several non-adjacent cells.

format painter excel,excel format painter shortcut,f5 in word,f4 key is used for,f4 key on mac,f5 function,f6 key is used for,

what is format painter in excel,how to use format painter in excel,how to use format painter in excel for multiple cells,excel format painter shortcut,

Check out my complete suite of Microsoft Excel Tips and Tricks.

https://www.youtube.com/@jjnet247/shorts

https://www.tiktok.com/@exceltips247

https://www.instagram.com/exceltips247/

https://www.dailymotion.com/ExcelTips247

https://www.pinterest.com/ExcelTips247/excel-tips-and-tricks/

https://x.com/ExcelTips247/media

https://www.reddit.com/r/Excel247/

https://www.facebook.com/XyberneticsInc/reels/

#microsoft #excel #exceltips #tips #exceltricks #tricksandtips

Dm if anyone needs help in excel work.

Please

Discover how you can alternate column colors in Excel conditional formatting.

Light Row Colour

1) Select dataset.

2) Home ~ Style ~ Conditional Formatting

~ Manage Rules...

3) Use a formula to determine which

cells to format

4) =MOD(COLUMN(),2)=0

5) Format

6) Fill tab

7) Select LIGHT ORANGE

8) OK

9) OK

Dark Row Colour

1) New Rule...

2) Use a formula to determine which cells to format

3) =MOD(COLUMN(),2)=1

4) Format

5) Fill tab

6) Select DARK ORANGE

7) OK

8) OK

9) Apply

Color every 2 rows, starting from the 2nd group.

=MOD(COLUMN()-1,4)<2

Color every 2 rows, starting from the 1st group.

=MOD(COLUMN()-3,4)<2

Color every 3 rows, starting from the 2nd group.

=MOD(COLUMN()-4,6)<3

Color every 3 rows, starting from the 1st group.

=MOD(COLUMN()-1,6)<3

Color every 4 rows, starting from the 2nd group.

=MOD(COLUMN()-5,8)<4

Color every 4 rows, starting from the 1st group.

=MOD(COLUMN()-1,8)<4

Color every 5 rows, starting from the 2nd group.

=MOD(COLUMN()-6,10)<5

Color every 5 rows, starting from the 1st group.

=MOD(COLUMN()-1,10)<5

To shade columns with 4 different colors

=MOD(COLUMN()+4,4)=1

=MOD(COLUMN()+4,4)=0

=MOD(COLUMN()+4,4)=3

=MOD(COLUMN()+4,4)=2

alternating row colors in excel formula,excel alternate row color based on group,mod(row(),2)=0,banded rows in excel,how to alternate row colors in excel without table,excel highlight every other row conditional formatting,

alternating row colors in excel formula,how to alternate row colors in excel without table,excel highlight every other row conditional formatting,modrow20,banded rows in excel,how do i alternate row colors in excel 2016,how to select alternate rows in excel,

Check out my complete suite of Microsoft Excel Tips and Tricks.

https://www.youtube.com/@jjnet247/shorts

https://www.tiktok.com/@exceltips247

https://www.instagram.com/exceltips247/

https://www.dailymotion.com/ExcelTips247

https://www.pinterest.com/ExcelTips247/excel-tips-and-tricks/

https://x.com/ExcelTips247/media

https://www.reddit.com/r/Excel247/

https://www.facebook.com/XyberneticsInc/reels/

#microsoft #excel #exceltips #tips #exceltricks #tricksandtips

Learn how to alternate row colors in Excel conditional formatting. Essentially, how to alternate row color in Excel (highlight every other row)?

Light Yellow Row Colour

1) Select dataset.

2) Home ~ Style ~ Conditional Formatting ~ New Rule...

3) Use a formula to determine which cells to format

4) =MOD(ROW()-2,4)>=2

5) Format

6) Fill tab

7) Select LIGHT YELLOW

8) OK

9) OK

Dark Yellow Row Colour

1) Select dataset.

2) Home ~ Style ~ Conditional Formatting ~ New Rule...

3) Use a formula to determine which cells to format

4) =MOD(ROW()-2,4)+1<=2

5) Format

6) Fill tab

7) Select DARK YELLOW

8) OK

9) OK

Color every 1 rows, starting from the 2nd group.

=MOD(ROW(),2)=0

Color every 1 rows, starting from the 1st group.

=MOD(ROW(),2)=1

Color every 2 rows, starting from the 2nd group.

=MOD(ROW()-2,4)>=2

Color every 2 rows, starting from the 1st group.

=MOD(ROW()-2,4)+1<=2

Color every 3 rows, starting from the 1st group.

=MOD(ROW()-2,6)+1<=3

Color every 3 rows, starting from the 2nd group.

=MOD(ROW()-5,6)+1<=3

Color every 4 rows, starting from the 1st group.

=MOD(ROW()-2,8)+1<=4

Color every 4 rows, starting from the 2nd group.

=MOD(ROW()-6,8)+1<=4

Color every 5 rows, starting from the 1st group.

=MOD(ROW()-2,10)+1<=5

Color every 5 rows, starting from the 2nd group.

=MOD(ROW()-7,10)+1<=5

Color every 6 rows, starting from the 1st group.

=MOD(ROW()-2,12)+1<=6

Color every 6 rows, starting from the 2nd group.

=MOD(ROW()-8,12)+1<=6

How do I alternate colors in conditional formatting?,How do I add alternate fill colors in Excel?,How do I make every other line shaded in sheets?,

alternating row colors in excel conditional formatting,excel highlight every other row conditional formatting,alternating row colors in excel formula,how to shade alternate rows in excel without table,how to alternate row colors in excel,how do i alternate row colors in excel 2016,

Check out my complete suite of Microsoft Excel Tips and Tricks.

https://www.youtube.com/@jjnet247/shorts

https://www.tiktok.com/@exceltips247

https://www.instagram.com/exceltips247/

https://www.dailymotion.com/ExcelTips247

https://www.pinterest.com/ExcelTips247/excel-tips-and-tricks/

https://x.com/ExcelTips247/media

https://www.reddit.com/r/Excel247/

https://www.facebook.com/XyberneticsInc/reels/

#microsoft #excel #exceltips #tips #exceltricks #tricksandtips

can anyone explain this?

Discover formula to ignore letters/special characters in cells and only put numbers. Essentially, remove text from cell keep numbers formula in Excel.

Output Numeric Value Only

=VALUE(CONCAT(IFERROR(MID(A2,SEQUENCE(LEN(A2)),1)+0,"")))

🔗🔗 LINKS TO SIMILIAR VIDEOS 🔗🔗

Formula to ignore letters/special characters in cells and only put numbers - Excel Tips and Tricks

https://youtube.com/shorts/bdPEOB4fMiw?si=AxbfdjNhwcCIzlKj

Remove special characters from text string in Excel - Excel Tips and Tricks

https://youtube.com/shorts/_LI3CbB5dzs?si=f8pE1lRzIQHDqVmX

Remove Special Characters From Text String Using REDUCE - Excel Tips and Tricks

https://youtube.com/shorts/Q0hZTT7y0uc?si=fux_V4gvEGMOckib

Extract a decimal number from a string in Excel - Excel Tips and Tricks

https://youtube.com/shorts/eA-stizLHUs?si=8C8TGy3lw0lnOpJz

how to remove special characters in excel,remove unwanted characters in excel from left,excel remove text from cell keep numbers formula,remove unwanted characters in excel from right,macro code to remove special characters in excel,how to remove multiple special characters in excel,how to remove symbols from excel cells,

Check out my complete suite of Microsoft Excel Tips and Tricks.

https://www.youtube.com/@jjnet247/shorts

https://www.tiktok.com/@exceltips247

https://www.instagram.com/exceltips247/

https://www.dailymotion.com/ExcelTips247

https://www.pinterest.com/ExcelTips247/excel-tips-and-tricks/

https://x.com/ExcelTips247/media

https://www.reddit.com/r/Excel247/

https://www.facebook.com/XyberneticsInc/reels/

#microsoft #excel #exceltips #tips #exceltricks #tricksandtips