I saw my boss do these 10 things in Excel!
123 Steps
106 followers
6 Video Views·Oct 18, 2022
So we have a bunch of names and he wanted to have another row added for email.
So this is what he was doing. Insert, insert, insert. So I showed him a better approach.
Instead of doing it like that, hold down the control and select all these people.
Once these are selected, right click on any row and insert. Boom! You have blank rows added
But then again, I saw him adding the tracking numbers like this.
One, two, three, four, five, like that. We needed 100 of these. So I showed him
this technique instead. Go to the first cell and then simply type sequence
and then say 100 and we get all the 100 numbers in one go. But my boss being the boss,
he immediately said we don't need just the numbers. We need this to say tn01, tn02, like that.
So again, I showed him this formula tn ampersand and then text of sequence 100
because we wanted three zeros like that. So we get tn001, tn002, like that all the way up to tn100.
But again, my boss wanted to make a list of dates in October. So this is what he was doing.
1 October 2022 and he was dragging it like this. So I stopped him halfway through and I showed
you this formula instead. Sequence and we wanted 31 days in October. So 31 and then start date is
1 October 2022. So we get all the dates. Of course, the formatting needs to happen.
Once these are done, select everything. And then I told him to press control shift three
so that the dates can be formatted into day, month, year. At Awesome Chocolates, we're doing
an office party and my boss was looking at who's coming to the office party. Of course, these are
the names, but there are quite a few duplicates like Barfani gave his name twice. So is Carolyn
and a few others. So this is what my boss was doing, manually scanning the list to identify
which names are repeated. What is going on here? So I showed him this trick instead.
Select all the names, go to home, conditional formatting and then highlight duplicates.
Boom. All the names that are duplicated are highlighted. The other day I spotted him looking
at two of our customer records, customers who bought Almond Choco and customers who bought
Eclairs. And he was trying to understand who bought both of these. So he's kind of going like
who F Bleemire is here and like that. So I showed him a better approach. Select the first list,
hold down control and select the second list. Then go to home, conditional formatting, highlight,
duplicate values. Okay. Quickly, Excel will spot all the names that appeared in both the lists.
So these are the customers that like both Almond Choco and Eclairs. And then I saw him do something
unbelievable. It is true. I, Michael Scott. He was enjoying a YouTube video, but not giving it a like.
Don't be like him. Like this video. The other day, my boss wanted to make a monthly tracker.
So he set up individual worksheets for each month and he was doing this. He was going to the row
number one, setting this as a header, going here and then typing the title as monthly tracker and
then setting this as bold and adjusting alignments and all of that. And he was going to go and repeat
this process for all the six sheets. So I showed him this technique instead. Select the first sheet,
hold shift and select the last sheet. This will select all the worksheets. Now you can set
everything in one go. And when you're done, just click on the second sheet and you will see that
all the sheets are formatted exactly same with same column width, same content, same colors and
everything. Once the monthly trackers are set up, then I spotted him doing something even more
sillier. He was going through these amounts and anytime a value is more than 400, he's filling
that up with yellow color like this. That was incredible to watch. So I showed him this technique
instead. Select all these amounts, go to conditional formatting, highlight, greater than, and then type
the starting value. So 400 and then go to custom format, fill, yellow color. Okay. And okay. So now
automatically all the 400 above amounts are highlighted in yellow color. He wanted to see
all of these in a list format. So then I told him apply filters, click on the filter icon, filter by
color, and then select the yellow. So then he can see which customers have bought more than $400
worth of chocolates from us. For an upcoming presentation, my boss wanted to make country-wise
pivot tables. So this is what he was doing. He went to the data, insert pivot table, add a pivot
table, and then go and add country as a filter and select Australia here to see the Australia pivot,
and then repeat the whole process for New Zealand, India, et cetera. I showed him this technique
instead. Go to pivot, analyze options, and then show report filter pages. This is going to take
the field that we added, the country, and then create individual country-level pivots in one go.
So as soon as I click on, okay, I get my Canada, India, New Zealand, UK, USA tabs
readily done for me. I also have a nice monthly sales report and my boss prepares this, but then
he was trying to make sure that all these charts are sized same. He's kind of going and moving
these around and trying to approximately adjust them, but not getting them perfectly. So then I
showed him this technique. Select the first chart, now hold down control and select the other charts
as well. Go to shape format and from the size area of the format, type a height and width that you
want. Now all the charts have same size. What about the alignment? You can hold down the alt key when
you're moving these charts so that they always snap to the cell grid. This way the alignment
is perfect. Remember the monthly tracker? He was going to go and manually copy paste all these
data into one big file for the further analysis. I then told him, watch this video instead and you
will know how to combine all these six tabs into one big table. Hi there, this is Chandu and I'm
the boss. We hope you liked this video. Let us know in the comments if you want to see more of
boss versus me videos. Also, if you have got a boss who is not so awesome in Excel,
then do share this video with them. I'll catch you somewhere else. Bye.
