Microsoft Excel Tutorial - BOM Tool Suite from Main Sheet Solutions
Do you deal with Bills of Material? Also known as BOMs. Any Structured List in Excel can make use of this add-in from Main Sheet Solutions:
Download a trial from MainSheet Solutions | Time saving Excel Add In
Download the two data sets that I used in this video: Excel Add-In To Streamline Bill Of Material BOM Analysis - Episode 2612 Sample Files - MrExcel Publishing
Table of Contents
(0:00) Bills of Material in Excel
(2:02) Data in a typical BOM
(2:21) Ribbon for BOM
(2:29) Adding Grouping
(2:46) Choosing highlight colors
(3:17) Highlight rows
(3:27) Indent BOM
(3:36) BOM Rollup Calculations
(4:30) Analyzing Mike Girvin Book Table of Contents
(6:03) Adding Structure ID and UID
(6:44) Applying colors to group levels
(7:06) Check out the add-in
(7:31) Wrap-up
Do you deal with Bills of Material? Also known as BOMs. Any Structured List in Excel can make use of this add-in from Main Sheet Solutions:
Download a trial from MainSheet Solutions | Time saving Excel Add In
Download the two data sets that I used in this video: Excel Add-In To Streamline Bill Of Material BOM Analysis - Episode 2612 Sample Files - MrExcel Publishing
Table of Contents
(0:00) Bills of Material in Excel
(2:02) Data in a typical BOM
(2:21) Ribbon for BOM
(2:29) Adding Grouping
(2:46) Choosing highlight colors
(3:17) Highlight rows
(3:27) Indent BOM
(3:36) BOM Rollup Calculations
(4:30) Analyzing Mike Girvin Book Table of Contents
(6:03) Adding Structure ID and UID
(6:44) Applying colors to group levels
(7:06) Check out the add-in
(7:31) Wrap-up
Transcript of the video:
It's really for any structured list. But designed primarily for people dealing with Bills of Material.
There's an amazing utility from Main Sheet Solutions.
All right, so a Bill of Material or BOM or structured lists.
Now look, in my 12 years of working at a company.
I did not deal with BOM in the headquarters in Akron. But two of my counterparts down at the plant in Houston, Bob and Patty dealt with Bills of Material all the time.
So when Dan sent me this utility that he created, he's a BOM guy. I immediately thought of Bob and Patty.
And said, "I wonder if this would be useful to them".
And I have to tell you, Bob was one of the most interesting people I ever worked with.
Texas A&M Aggie, former military guy, Texan through and through.
He was always just the most super nice guy to me. First-rate.
But if he noticed something that was BS, he would pull out the most colorful Texas vocabulary.
All kinds of terms that I had never heard before.
And even now, 23 years later, I'll break one out when necessary.
So I've been thinking a lot about Bob and Patty in their work on Bills of Material.
I've learned that Bob has since passed away and I'm sad about that because he was an interesting guy.
All right, so let's just say I'm not dealing with Bills of Material all the time.
But I know enough about it to be dangerous.
This set of BOM Tool Suite for Main Sheet Solutions.
20 time-saving functions, over 250 use cases for Bills of Material. I think it's a cool set of tools.
I think there's some use cases outside of BOM analysis.
But if you know someone who's dealing with Bills of Material, either in engineering or down in the manufacturing plant.
If you have a Bob and Patty, hey, send them to this video because there's a good chance that this will make their life a lot easier.
All right, so Bills of Material generally today created in your ERP, CAD or PDM system.
But even then they're exported to Excel. So here's the sample dataset that came with it.
And we always have a level, right? So this system has subsystem A.
And a level two and you get this kind of data.
How many components, the weight, the cost, all kinds of information. And the BOM Tool Suite is this ribbon out here.
It lets us do some items, just let's add some grouping here.
So find the level column and we choose group rows.
And wow, I mean that is so much faster than coming out to data group and the whole bit, right?
And so now we can collapse the Bill of Material down to any of these levels very quickly. The next tool is highlighting.
And before we get to highlighting. Let's talk about some user preferences.
I really appreciate that when I tried this a month ago and tried to set my own colors, I just did a bad job, right?
So if we come up with presets, and then you can choose your favorite theme, right?
So all of the color themes that are built into Excel, you can choose. So there's Slipstream.
Which is what I use in all my books and may not be the best color for you, but that's just what I'm going to use. Save as preference, right?
So I can control the colors that are used here.
And then when I come here to highlight rows, I just have to say all rows and use my user preferences instead of the default colors.
Click Continue. And you get that nice formatting here.
What if I want to indent things?
So right here I'll select those items and say indent rows, right? It's cool.
Okay, but here's even better.
So let's say that we need to figure out the total mass for each of these levels. So the weight in kilograms times the quantity.
Dan has these great things out here called Roll Up equations.
So the data that I want to roll up is weight.
Click, okay.
And the quantity column, he figured that out automatically. And where am I going to put this?
I want to put it out in column J.
So just use the little arrow there to get out to J and insert equations. Check this out.
So the note there tells us how this was created. But basically tells us the whole system is 235.75 kilograms. And it's building the formula.
This is some cool stuff. All right, so now this is the sample data.
And of course it doesn't mean a lot to me.
It probably means more to the people that are dealing with Bills of Material. But I have this great little file.
Which is the table of contents for Mike Girvin's book, Microsoft 365 Excel: The Only App That Matters. And so just trying some things here.
Let's add Group Rows. All right.
So if I go to the level two, I get a list of each chapter.
If I go to level three, I get what we call the A heads.
So there's two kinds of heads in a chapter.
The main heads are A heads, and then the B heads are the subheads, right? And here, let's add some Indent Rows here.
Notice I have 659 items, so it actually takes a little bit of time.
All right, so that's cool.
And then let's figure out how many pages are in each chapter in the book. So I'll come out here to roll up equations.
And this time I want to count the number of pages. I don't have to multiply by quantity now.
So page count. And we're going to put this in column D.
Insert equations. Show advanced options.
Uncheck used quantity. Insert equations.
All right, there we go. So that's it.
Mike's book is a huge book, 780 pages.
And now I can come back here to level two and see for each chapter how many pages per chapter. This is pretty cool.
And there's some other things out here that I have no idea.
So we have our levels, right? If we ask for SID, it adds the 1.1.2, right?
That's I don't know, probably useful to someone. The UID, I'll use the name, click okay.
All right.
So we get the book title, the chapter title, and the subheads.
NHA. We can add a sequence.
We can sort by SID, sort by NHA, scramble it. There's a lot more here than I've gotten into.
But it's enough to make me say, "Huh, yeah, this is pretty wild and pretty interesting".
Let's change the color preferences here to use presets.
We'll choose another color. Hey, let's use green.
Save as preferences. Click okay.
Click close. Highlight rows.
All rows. User preferences continue.
And applied the formatting very, very quickly. So check this out.
From Main Sheet Solutions, I'll put a link down in the YouTube description below.
If you're someone who's doing Bills of Material.
Or you know someone, you're Bob and Patty at your work, they could check this out. It probably has a lot of great tools.
Dan, the guy that wrote it, he's a Bill of Material guy.
And just made things that would make his life easier.
Check it out. I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
If you like these videos, please down below, Like, Subscribe and Ring the Bell.
Feel free to post any questions or comments down in the comments below.
There's an amazing utility from Main Sheet Solutions.
All right, so a Bill of Material or BOM or structured lists.
Now look, in my 12 years of working at a company.
I did not deal with BOM in the headquarters in Akron. But two of my counterparts down at the plant in Houston, Bob and Patty dealt with Bills of Material all the time.
So when Dan sent me this utility that he created, he's a BOM guy. I immediately thought of Bob and Patty.
And said, "I wonder if this would be useful to them".
And I have to tell you, Bob was one of the most interesting people I ever worked with.
Texas A&M Aggie, former military guy, Texan through and through.
He was always just the most super nice guy to me. First-rate.
But if he noticed something that was BS, he would pull out the most colorful Texas vocabulary.
All kinds of terms that I had never heard before.
And even now, 23 years later, I'll break one out when necessary.
So I've been thinking a lot about Bob and Patty in their work on Bills of Material.
I've learned that Bob has since passed away and I'm sad about that because he was an interesting guy.
All right, so let's just say I'm not dealing with Bills of Material all the time.
But I know enough about it to be dangerous.
This set of BOM Tool Suite for Main Sheet Solutions.
20 time-saving functions, over 250 use cases for Bills of Material. I think it's a cool set of tools.
I think there's some use cases outside of BOM analysis.
But if you know someone who's dealing with Bills of Material, either in engineering or down in the manufacturing plant.
If you have a Bob and Patty, hey, send them to this video because there's a good chance that this will make their life a lot easier.
All right, so Bills of Material generally today created in your ERP, CAD or PDM system.
But even then they're exported to Excel. So here's the sample dataset that came with it.
And we always have a level, right? So this system has subsystem A.
And a level two and you get this kind of data.
How many components, the weight, the cost, all kinds of information. And the BOM Tool Suite is this ribbon out here.
It lets us do some items, just let's add some grouping here.
So find the level column and we choose group rows.
And wow, I mean that is so much faster than coming out to data group and the whole bit, right?
And so now we can collapse the Bill of Material down to any of these levels very quickly. The next tool is highlighting.
And before we get to highlighting. Let's talk about some user preferences.
I really appreciate that when I tried this a month ago and tried to set my own colors, I just did a bad job, right?
So if we come up with presets, and then you can choose your favorite theme, right?
So all of the color themes that are built into Excel, you can choose. So there's Slipstream.
Which is what I use in all my books and may not be the best color for you, but that's just what I'm going to use. Save as preference, right?
So I can control the colors that are used here.
And then when I come here to highlight rows, I just have to say all rows and use my user preferences instead of the default colors.
Click Continue. And you get that nice formatting here.
What if I want to indent things?
So right here I'll select those items and say indent rows, right? It's cool.
Okay, but here's even better.
So let's say that we need to figure out the total mass for each of these levels. So the weight in kilograms times the quantity.
Dan has these great things out here called Roll Up equations.
So the data that I want to roll up is weight.
Click, okay.
And the quantity column, he figured that out automatically. And where am I going to put this?
I want to put it out in column J.
So just use the little arrow there to get out to J and insert equations. Check this out.
So the note there tells us how this was created. But basically tells us the whole system is 235.75 kilograms. And it's building the formula.
This is some cool stuff. All right, so now this is the sample data.
And of course it doesn't mean a lot to me.
It probably means more to the people that are dealing with Bills of Material. But I have this great little file.
Which is the table of contents for Mike Girvin's book, Microsoft 365 Excel: The Only App That Matters. And so just trying some things here.
Let's add Group Rows. All right.
So if I go to the level two, I get a list of each chapter.
If I go to level three, I get what we call the A heads.
So there's two kinds of heads in a chapter.
The main heads are A heads, and then the B heads are the subheads, right? And here, let's add some Indent Rows here.
Notice I have 659 items, so it actually takes a little bit of time.
All right, so that's cool.
And then let's figure out how many pages are in each chapter in the book. So I'll come out here to roll up equations.
And this time I want to count the number of pages. I don't have to multiply by quantity now.
So page count. And we're going to put this in column D.
Insert equations. Show advanced options.
Uncheck used quantity. Insert equations.
All right, there we go. So that's it.
Mike's book is a huge book, 780 pages.
And now I can come back here to level two and see for each chapter how many pages per chapter. This is pretty cool.
And there's some other things out here that I have no idea.
So we have our levels, right? If we ask for SID, it adds the 1.1.2, right?
That's I don't know, probably useful to someone. The UID, I'll use the name, click okay.
All right.
So we get the book title, the chapter title, and the subheads.
NHA. We can add a sequence.
We can sort by SID, sort by NHA, scramble it. There's a lot more here than I've gotten into.
But it's enough to make me say, "Huh, yeah, this is pretty wild and pretty interesting".
Let's change the color preferences here to use presets.
We'll choose another color. Hey, let's use green.
Save as preferences. Click okay.
Click close. Highlight rows.
All rows. User preferences continue.
And applied the formatting very, very quickly. So check this out.
From Main Sheet Solutions, I'll put a link down in the YouTube description below.
If you're someone who's doing Bills of Material.
Or you know someone, you're Bob and Patty at your work, they could check this out. It probably has a lot of great tools.
Dan, the guy that wrote it, he's a Bill of Material guy.
And just made things that would make his life easier.
Check it out. I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
If you like these videos, please down below, Like, Subscribe and Ring the Bell.
Feel free to post any questions or comments down in the comments below.
