MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Inserting Alternating Rows

May 24, 2018 - by Bill Jelen

Inserting Alternating Rows

Today's question from the Atlanta Power Excel seminar.

I have data formatted with every other row in grey. When I insert two new rows in the middle of the data, both of the new rows end up grey instead of following the pattern.

Take a look at the workbook in the image below. The even numbered rows have a grey fill.

Grey fill in every other row.
Grey fill in every other row.

When you select A5:A6 and Insert Rows, you get two new blank rows. But both rows are grey.

Both new rows are grey
Both new rows are grey

I have a solution to this, but it requires a trip back to 3rd grade math.

continue reading »

More Reading

VLOOKUP To Two Tables

VLOOKUP To Two Tables »

May 23, 2018 - by Bill Jelen

Today's question from Flo in Nashville: I need to do a VLOOKUP for a series of item numbers. Each item number is either going to be found in Catalog A or in Catalog B. Can I write a formula that first searches Catalog A. If the item is not found, then move on to Catalog B?

Insert 2 Decimals

Insert 2 Decimals »

May 22, 2018 - by Bill Jelen

A question from my Atlanta live Power Excel seminar: Is there a way to type 12345 and have Excel enter 123.45? I call this Adding Machine mode, because it feels like the 1970's era adding machine with the switch to automatically add two decimal places.

read more news »

Featured Products

VBA & Macros for Microsoft Excel 2016

VBA & Macros for Microsoft Excel 2016 »

Use this guide to automate virtually any routine task: save yourself hours, days, maybe even weeks! Make Excel do things you thought were impossible, discover macro techniques you won’t find anywhere else, and create automated reports that are amazingly powerful.

Excel 2016 in Depth

Excel 2016 in Depth »

Experienced with Excel? Don’t let Excel 2016 make you feel like a beginner again! This new full-color edition of the bestselling book has been completely overhauled. Gone is unnecessary and rarely used content; emphasis is on the most-used and new aspects of Excel 2016.

MrExcel XL =ROMAN(40)

MrExcel XL =ROMAN(40) »

Fun Excel book in full color. Includes Bill's favorite 40 Excel tips, plus Excel jokes, Excel Tweets, and Excel cocktails. Bonus section includes 40 keyboard shortcuts and more.

MrExcel LIVe

MrExcel LIVe »

A book for people who use Excel 40+ hours per week. Illustrated in full color.

M is for (DATA) MONKEY

M is for (DATA) MONKEY »

Despite the moniker "data monkey", we information workers are often more like data magicians. Our data seldom enters our world in a ready-to-consume format; it can take hours of cleaning, filtering, and reshaping to get things ready to go. Power Query will make this process faster the first time and reduce it to a single button click every subsequent time.

Supercharge Power BI

Supercharge Power BI »

If you want to be able to supercharge Power BI, you need to learn to write DAX. Data Analysis Expressions (DAX) is the formula language of Power BI and Power Pivot for Excel. This book prompts the reader to put your new skills to the test. Written to give you hands-on practice using Power BI Desktop and writing DAX.

see more products »