I saw my boss do these 10 things in Excel!

in on my boss and guess what he was doing 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 Rose added everywhere 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 TN 0 1 tn02 like that so again I showed him this formula PN 10 percent and then off sequence 100 because we wanted three zeros like that so we get T and 0 0 1 0 0 2 like that all the way up to dn100 but again my boss wanted to make a list of dates in October so this is what he was doing one 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 so once these are done select everything and then I told him to press Ctrl shift 3 so that the dates can be formatted into day month year at awesome chocolates we are doing your 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 barf warning gave his name twice so is Carolyn and few others so this is what my boss was doing manually scanning the list to identify which names are repeated huh 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 charcoal and customers who bought eclairs and he was trying to understand who bought both of these so he's kind of going like oh f blemeyer 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 Whole 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 armor 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 roll 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 gonna 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 tracks 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 is 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 dollars worth of chocolates from us yes For an upcoming presentation my boss wanted to make country wise paper tables so this is what he was doing he went to the data insert pivot table at a paper 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 Etc I showed him this technique instead go to Pivot analyze options and then show report filter pages this is gonna 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 OK 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 the 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 gonna go and manually copy paste all this 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 [Music]

Channel: Chandoo

Views: 2,604,863

Keywords: chandoo, chandoo.org, Excel, spreadsheets, microsoft excel, excel tips and tricks, Excel tips, I saw my boss do this, I saw my boss do this in Excel, ISMBDTIE

Id: HgfC3etOgsU

Length: 7min 36sec (456 seconds)

Published: Tue Oct 04 2022