Hi Heinrich, just wanted to let you know that your powerpoint tips (especially the formatting shortcuts) have helped me so much with efficiency in my work. Really appreciate this series because it's specific and practical!
Thanks Heinrich, the tip on using a different font colour for calculated cells is going to be really useful. I am now revisiting my excel files and making that change.
Hi Heinrich, a few additions: I would usually add a filter (Ctrl-Shift-F) on the name line. And since clients love to mess with things I would lock the calculated fields. The dark blue for calculated things is new for me, in our firm we use grey backgrounds, but working at an M&A boutique so it's just the difference between Finance and Consulting. Danke für die Videos! Ich leite deinen Kanal immer an alle Praktis und Einsteiger weiter, um ihnen die Angst zu nehmen und zu zeigen, dass alle mal irgendwo anfangen müssen.
Hi, thanks for your comment! Indeed adding a filter is always a great idea as well :) Freut mich sehr, dass die Videos hilfreich sind. Vielen Dank dir fürs Teilen! LG, Heinrich
Good video as usual. Just wanted to add here, whenever you want to change the format of a column, NEVER select the entire column, ONLY select the data from the column header till the end if data in that column. Otherwise you will end up with a huge file in terms of memory size. The same applies to rows. Thanks
Thanks for sharing. If you’re really in a hurry, you can use excel’s format as table function. That will take you less than 10 sec. 1) click the triangle up in the left corner to mark all cells. 2) double click any column border to adjust all columns in one click. 3) with all the cells still selected, click the clear symbol in the tools bar and choose clear formats. 4) click anywhere in the dataset to mark a cell and then hit Cmd+T and then enter. Voila You can choose one of excels formats or right click any table format and create a copy that you can format using your company’s colors and then save in a template for future use.
Blue: Inputs, or any hardcoded data, such as historical values, assumptions, and drivers Black: Calculations and references to the same sheet Green: Calculations and references to other sheets (note that some models skip this step and use black for these cells) Red: References to separate files or external links
Incorporate a first sheet as the document cover with client & consulting firm logos + brief description of the objective of the document and maybe some index bellow that explains the user what is the intention of each Excel page in the document
Thanks Heinrich for this nice coffee break! I’d be interested in having your advice for the creation of a consultant-like project dashboard. Have you already made a video on this subject ? I know there are a few on the internet already but I think yours would be better ;)
Hi, thanks for your comment. Yes, I already created one video on dashboards. You find it on the channel / also in the Excel playlist. Hope this helps! Might do more on this topic in the future - stay tuned! :) Best, Heinrich
@@FirmLearning Thanks Heinrich ! I just watched your video on dashboards and it's exactly what I was looking for :) I will make sure to watch the next videos you'll post on this topic in the future. Best, Armand
Hey Heinrich, great video as always! I noticed that you use key board shortcuts and make the work super fast. Can you maybe tell us about these shortcuts also?
@@FirmLearning I'd do that for PPT as well. There's some super useful ones when presenting, when moving to a specific slide etc. And one thing I always have to dig for when I haven't done it in a while is how to extract one specific slide and save it as pdf.
Agree with that tips. I think if I am a client, I can read the formatting much easy. I think we don't need format as a Table if we show this report to the client that maybe have a little experience with Excel
Hi Heinrich, thanks for your practical tutorials. I am wondering whether I will be able to download the excel table you had worked on in order to practice at my end. Many thanks.
If there is one thing I hate about giving data to consultants it is when they introduce errors, with lack of attention to detail, such as Merchanside instead of Merchandise which was spelled correctly at the start.
Great set of tips! Please allow me to add my humble opinion regarding what I would add there: 1. I would elaborate on the data source to explain a bit more where this data came from (X dashboard, X database... etc.) 2. I would add a key for the year field - the acronyms FW and SS might not be very intuitive if you don't have enough background (I guess that would depend on the target audience, but it might be worth explaining!) 3. Finally, some of these numbers look like they were decimals when you look at the raw data field, so it's also worth checking if you want to include decimals in there or not (This would especially affect the unit prices for some of the rows, and for some business users these discrepancies are very important!)
I usually format as a table anything with unique id columns so that people can run index match formulas, etc in a way which is more readable due to being able to reference the table name and column headers. Is this not always the best approach? I saw you didnt mention formatting as a table in the video.
Hi Brad, personally I do not do this and also do not really like working with names for ranges etc. Might just be a personal preference though, understand what the appeal might be. Best, Heinrich
I agree with Brad. Converting the data to an Excel table would enable both quicker formatting and referencing the data (in formula, PowerQuery, pivot table).
Painful to watch. Format as table man! (Alt, h, t) Then you can use proper data aggregation formulas (sumifs, xlookup, etc) by naming the table (alt J,T,A) without looking at it or using the mouse. Your ppt videos are great though.
Default excel table formatting is not best practice in good looking ppt presentations/ reports. So you would need to do additional steps to "deformat" the table formatting. If you don't need table functionality, then it's not really needed.
Hallo Heinrich, I get it now...So you are the one that owns Heinrich-Heine Allee in Düsseldorf😜 By the way, great content Herr Rusche... I have watched almost 30-40 videos of yours so far and even purchased one of your Udemy courses...Vielen Dank!!! Hope to meet you one day in Düsseldorf as I too live in the same city.
0:20 many people do struggle working properly in excel. 0:55 why important? 1:10 first, it communicates well. bring the message across: the look and feel of the deliverable. 1:25 second, importance in professional services: end product quality. the data sent to clients would be expected to have a higher quality. than they hold themselves accountable for. 3:05 the proper file name! 3:15 name it a proper book name. 3:55 a nice and clean look 4:30 main title and subtitle: if bold 4:55 view >> remove gridline 5:50 in quantity, it's good to have a separator. 6:10 accounting format with euro 6:40 general 2 tips: first, put it aside. this is a standard way of modeling 8:00 second, if you're working with a more complex files/models 8:55 the hard copy/input one and the calculated one/output. 8:20 format painter !!!
Implement a table on your data Ctrl + T the column width will auto. adjust, you can choose your layout for a more cohorent feel accross all your Excel sheets and you have a dynamical range for your pivottables or formulas.
Hallo Heinrich, kurze Frage: Findest Du die Microsoft Excel Expert-Zertifizierung sinnvoll? Und würde mir das Vorteile gegenüber anderen Bewerben geben?
Hi! Gute Excel-Skills sind natürlich wichtig. Grade für deine Bewerbung bei großen Beratungen denke ich aber nicht, dass solche Zertifikate einen signifikanten Unterschied machen. LG! Heinrich
Hi Heinrich! Você precisa traduzir este conteúdo para o português! Sem dúvidas vai ser um sucesso! Sempre compartilho o seu material com colegas de trabalho.
Hi Juan, thanks for sharing the videos in your organization :) Might indeed think about translating it to more languages (or with captions) in the future. Stay tuned! :) Best, Heinrich
Hi Heinrich, I’m starting my journey with McKinsey in September! I just wanted to ask you, what should I do to be best prepared ? Maybe an excel advanced course? Thank you 😊
Hi Greta, this will not be absolutely required, but if you already have intermediate skills in PowerPoint and Excel when you join, this will for sure help you to add real value to the team early on! Lots of success to you and congrats for getting the job :) Best! Heinrich
A sneaky consulting trick is to title every Ppt or Excel as at least ver3 or ver4 as a minimum. This video focuses on making the worksheet perfectly clear. Always consider the option of including an instruction or comment sheet as sheet 01.
🎯 Key Takeaways for quick navigation: 00:00 🤖 *Introduction to Excel Formatting Importance* - Introduction to the importance of proper Excel formatting in consulting and professional roles. - Emphasis on the struggle many people face with Excel formatting. - Objective of the video: to demonstrate professional Excel sheet formatting. 00:57 💡 *Reasons for Necessary Formatting* - The necessity of formatting for clarity and communication of key messages. - Importance of high-quality formatting in professional services and client expectations. 02:24 🛠️ *Practical Tips for Excel Formatting* - Demonstrating practical formatting tips: renaming files, adjusting fonts, and layout modifications. - Focus on aesthetics and clarity in formatting for client presentations. 05:20 📊 *Advanced Formatting Techniques* - Advanced techniques: freezing panes, number formatting, and incorporating currency symbols. - Comparing before and after formatting to highlight improvements. 06:17 📈 *Building Complex Layouts* - Tips for building complex Excel layouts, with emphasis on vertical table arrangement. - Recommendations for dashboard creation and managing multiple tables. 08:12 🎨 *Formatting Conventions for Complex Models* - Importance of color-coding to distinguish between input and calculated fields. - Example of applying formatting to enhance model comprehensibility. 09:12 📢 *Conclusion and Engagement* - Summary and invitation for viewer engagement. - Call to action for likes, comments, and subscriptions. - Promotion of social media platforms and membership perks. Made with HARPA AI
When dividing numbers you should always use the excel formula +Iferror(number 1/number2;"0") so if it divides a number that is zero it will always return the value to zero instead of "#Div/0!"
There's always something I notice, but never understood: Why do people always make Row 1 and Column A empty? I'm not sure about its purpose, and it's an additional step you have to do for every sheet. Makes doing VBA reference slightly more complicated as well.
@@FirmLearning Hi Heinrich, I need to present some charts periodically to client. Some times I recreate it, sometimes I re-refresh it, some times I miss a chart (one table can have several charts and there are so many tabs or sheets). What is the tips to easily manage excel charts to make it easier to present it in periodic basis? Is it by creating a standard chart in the same excel tab with its raw data or table? Is it by using pivot chart (as long as one not forgetting to refresh it), is it by putting it in one special tab with many charts in it? Is it one chart one tab? Or Is it by recreating chart every time it will be presented? (Thus the created chart is always the latest one, not the old one) Need to know how is the best way to manage charts to make life easier. Thanks you very much Heinrich (always look forward to get your new insight)
Why do people still put the date in the title? I think it’s a Windows XP artefact, nowadays we can sort files by date (the creation date will stay the same even after sending it to a collegue a month later) with one click. It’s looks confusing and not clear. Hard to find the right document.
Hi Julia, one reason would be to mark more substantial changes. You might open a file and change just a minor detail on a format of a cell and the automatic change date indication would go up, though this is not always desired. Best, Heinrich
Eine leere Spalte A links von der Tabelle ist ein absolutes NoGo in Excel. Wie von mehreren bereits kommentiert, ist Ctrl+T hier m.E. geboten. Es ist ja offensichtlich, dass weitere Analysen in Pivottabellen o.Ä. erfolgen.
Check out the channel of Leila Gharani here on UA-cam, she‘s an Excel pro! I learned so much. I can recommend the mini series where she designs the annual report. It teaches a lot about how design delivers information.
Hi, check out the video on shortcuts that I released shortly after, it contains a version of this file that you can also use for this video! Best, Heinrich
I would say, learn a lot of excel and tools alike that you use, then learn to do the same things but with python and automatize everything and then they cant fire you cos noone would know how to do your job as good and quick as you c:
As a future investment banker I had to cringe when you used the mouse in Excel and colored hard coded cells in black and formulas in black - it should be the other way around. BIG game changer: don’t use your mouse and learn shortcuts. For example removing gridlines is Alt + W + VG, renaming sheet is Alt + H + O + R, etc.
You can tell this is true consultancy because it looks great but there's a fundamental spelling error - "Merchanside". Shine over substance. (Just joking, these are great tips, thanks!)
I'm seeing no reason not to format the data as a table - if there is any use beyond printing (read: actual analysis), then there is no reason not to format the data as a table.
Interested to learn to create Consulting-style Slide Presentations? This is my complete Slide Writing course:
link.firmlearning.com/slides
Hi Heinrich, just wanted to let you know that your powerpoint tips (especially the formatting shortcuts) have helped me so much with efficiency in my work. Really appreciate this series because it's specific and practical!
Great to hear the videos are helpful - thank you!! best, Heinrich
Thanks Heinrich, the tip on using a different font colour for calculated cells is going to be really useful. I am now revisiting my excel files and making that change.
Great to hear it is helpful! Best, Heinrich
Hey Heinrich, would love to see how you would approach making PPTs for clients. Great and simple video btw !
I recommend his course on Udemy, about how create PPT like a consultant!!! It's great!
Thanks for the shoutout Henry :) Best! Heinrich
Fantastic Heinrich - I am at the maturity of my career but still I get to learn and improve so much in my business. God bless you.
I am also an Consultant in my first year and this is a great tip, thank you very much.
Great to hear that - thanks! Best, Heinrich
Random video that exceeded my expectations, cheers! Thanks for the effort
Happy to hear that - thank you Mauricio! Best, Heinrich
Hi Heinrich, a few additions: I would usually add a filter (Ctrl-Shift-F) on the name line. And since clients love to mess with things I would lock the calculated fields. The dark blue for calculated things is new for me, in our firm we use grey backgrounds, but working at an M&A boutique so it's just the difference between Finance and Consulting.
Danke für die Videos! Ich leite deinen Kanal immer an alle Praktis und Einsteiger weiter, um ihnen die Angst zu nehmen und zu zeigen, dass alle mal irgendwo anfangen müssen.
Hi, thanks for your comment! Indeed adding a filter is always a great idea as well :) Freut mich sehr, dass die Videos hilfreich sind. Vielen Dank dir fürs Teilen! LG, Heinrich
Hast du da überhaupt noch Zeit für Beatboxing?
Good video as usual. Just wanted to add here, whenever you want to change the format of a column, NEVER select the entire column, ONLY select the data from the column header till the end if data in that column. Otherwise you will end up with a huge file in terms of memory size.
The same applies to rows.
Thanks
Thanks for watching and sharing this tip Salman! Best, Heinrich
That’s a good tip!
Yes. That is a good tip that I’ve realised.
This helps me a lot (make my life much easier). Thanks for sharing, Heinrich 👌🏼👌🏼👌🏼
Hi Fadira, super happy to hear that, thank you for watching!! Best, Heinrich
Thank you for the video! Distinguishing between hard coded values and formulas is a good call.
Thanks for sharing. If you’re really in a hurry, you can use excel’s format as table function. That will take you less than 10 sec.
1) click the triangle up in the left corner to mark all cells. 2) double click any column border to adjust all columns in one click. 3) with all the cells still selected, click the clear symbol in the tools bar and choose clear formats. 4) click anywhere in the dataset to mark a cell and then hit Cmd+T and then enter. Voila
You can choose one of excels formats or right click any table format and create a copy that you can format using your company’s colors and then save in a template for future use.
Agree, thanks for sharing Gustav! Best, Heinrich
Excelent!! Super useful, thanks a lot Heinrich
Great to hear that - thanks Paola! Best, Heinrich
Blue: Inputs, or any hardcoded data, such as historical values, assumptions, and drivers
Black: Calculations and references to the same sheet
Green: Calculations and references to other sheets (note that some models skip this step and use black for these cells)
Red: References to separate files or external links
Exactly , this is the convention I use . Messes with my brain when people use the 0/0/255 blue for formulas and black for input !
Fair enough! ;) Best! Heinrich
Incorporate a first sheet as the document cover with client & consulting firm logos + brief description of the objective of the document and maybe some index bellow that explains the user what is the intention of each Excel page in the document
Hi Luis, yes this is indeed a best practice especially for larger, more substantial models! Best, Heinrich
Great video bro!
Thanks for watching Ivan! Best, Heinrich
Thanks Heinrich for this nice coffee break! I’d be interested in having your advice for the creation of a consultant-like project dashboard. Have you already made a video on this subject ? I know there are a few on the internet already but I think yours would be better ;)
Hi, thanks for your comment. Yes, I already created one video on dashboards. You find it on the channel / also in the Excel playlist. Hope this helps! Might do more on this topic in the future - stay tuned! :) Best, Heinrich
@@FirmLearning Thanks Heinrich ! I just watched your video on dashboards and it's exactly what I was looking for :) I will make sure to watch the next videos you'll post on this topic in the future. Best, Armand
@@etherealskies Great to hear that - thank you Armand! Best, Heinrich
👍 I like the color dedicated to calculated cells. I'm stealing!
Another formatting thing I also like to add is a Footer, and maybe a Header, in the Page Setup. What I'll add there is any practical things like "page# of#" and distribution/classification of the document (e.g. "INTERNAL", "CONFIDENTIAL" or "PROPRIETARY INFORMATION" etc) but also some sort of branding (e.g. ©CompanyName YYYY).
Hi Eliane, thanks for your comment! Yes, depends on what the purpose of the document is, but agree that this might be helpful as well! Best, Heinrich
Hey Heinrich, great video as always! I noticed that you use key board shortcuts and make the work super fast. Can you maybe tell us about these shortcuts also?
Hi Omkar, thanks for the comment! Yes might create a video on some of the more helpful shortcuts in the future :) Best, Heinrich
@@FirmLearning I'd do that for PPT as well. There's some super useful ones when presenting, when moving to a specific slide etc. And one thing I always have to dig for when I haven't done it in a while is how to extract one specific slide and save it as pdf.
Agree with that tips. I think if I am a client, I can read the formatting much easy. I think we don't need format as a Table if we show this report to the client that maybe have a little experience with Excel
Thanks for watching Firza, appreciate it! Best, Heinrich
Great video Heinrich!
Great to hear that, thanks for watching Gavin! Best, Heinrich
Hi Heinrich, thanks for your practical tutorials. I am wondering whether I will be able to download the excel table you had worked on in order to practice at my end. Many thanks.
If there is one thing I hate about giving data to consultants it is when they introduce errors, with lack of attention to detail, such as Merchanside instead of Merchandise which was spelled correctly at the start.
;) Still thanks for watching Tom!
Great set of tips! Please allow me to add my humble opinion regarding what I would add there:
1. I would elaborate on the data source to explain a bit more where this data came from (X dashboard, X database... etc.)
2. I would add a key for the year field - the acronyms FW and SS might not be very intuitive if you don't have enough background (I guess that would depend on the target audience, but it might be worth explaining!)
3. Finally, some of these numbers look like they were decimals when you look at the raw data field, so it's also worth checking if you want to include decimals in there or not (This would especially affect the unit prices for some of the rows, and for some business users these discrepancies are very important!)
Hi Reda, thanks for these additional remarks. Makes absolute sense, thanks for sharing! Best, Heinrich
I thought I was the only person using the dating convention and version numbers in my filenames 👍
Way to go Roman :) Thanks for watching! Best, Heinrich
Great coffee break video in home office hahaha
Home office indeed ;) Thanks for watching!! Best, Heinrich
Great, practical video !
Hi Gordon, super happy to hear that, thank you for watching!! Best, Heinrich
I think u should also make an excel course! I would def take it. BTW, just bought the ppt course. Thanks!
Thanks Jisun, might indeed do this in the future! Best, Heinrich
It would be helpful if you included what keyboard shortcut keys you used in speech to text
Great job Heinrich. You’re excel keyboard shortcuts are great - I suggest expanding on them and getting rid of the need for the mouse even more :)
Hi Josh, fully agree, my quest for Excel mastery is still ongoing ;) Best! Heinrich
Thank you!
Yep, it would be great if you make a video about dashboards.
Hi! Already did one, have linked in above in the video! best, Heinrich
Great content, love it. Sub !
I usually format as a table anything with unique id columns so that people can run index match formulas, etc in a way which is more readable due to being able to reference the table name and column headers. Is this not always the best approach? I saw you didnt mention formatting as a table in the video.
Hi Brad, personally I do not do this and also do not really like working with names for ranges etc. Might just be a personal preference though, understand what the appeal might be. Best, Heinrich
I agree with Brad. Converting the data to an Excel table would enable both quicker formatting and referencing the data (in formula, PowerQuery, pivot table).
Painful to watch. Format as table man! (Alt, h, t) Then you can use proper data aggregation formulas (sumifs, xlookup, etc) by naming the table (alt J,T,A) without looking at it or using the mouse. Your ppt videos are great though.
Hi Nicolas, yes formatting as a table might indeed have been a good idea as well ;) Best! Heinrich
I couldn’t understand why a table wasn’t the immediate solution to making the data easier to use/present.
Default excel table formatting is not best practice in good looking ppt presentations/ reports. So you would need to do additional steps to "deformat" the table formatting. If you don't need table functionality, then it's not really needed.
Hallo Heinrich, I get it now...So you are the one that owns Heinrich-Heine Allee in Düsseldorf😜
By the way, great content Herr Rusche... I have watched almost 30-40 videos of yours so far and even purchased one of your Udemy courses...Vielen Dank!!! Hope to meet you one day in Düsseldorf as I too live in the same city.
I wish ;) Thank you for being a regular viewer Sravan, happy to hear that!! Best, Heinrich
Also helps when you spell check.
Definitely!
Hi Heinrich, I am really wondering how you manipulate the excel with your REALLY COOL shortcuts.
Will likely do a video on that soon! :) Best! Heinrich
0:20 many people do struggle working properly in excel.
0:55 why important?
1:10 first, it communicates well. bring the message across: the look and feel of the deliverable.
1:25 second, importance in professional services: end product quality.
the data sent to clients would be expected to have a higher quality. than they hold themselves accountable for.
3:05 the proper file name!
3:15 name it a proper book name.
3:55 a nice and clean look
4:30 main title and subtitle: if bold
4:55 view >> remove gridline
5:50 in quantity, it's good to have a separator.
6:10 accounting format with euro
6:40 general 2 tips:
first, put it aside. this is a standard way of modeling
8:00 second, if you're working with a more complex files/models
8:55 the hard copy/input one and the calculated one/output.
8:20 format painter !!!
I like to use the ‘page view’ while I’m working so I can see how the spreadsheet will lay out on the selected page size as I am working.
Also a great one - thanks for sharing Joseph! Best, Heinrich
A useful video thanks.
Thanks for watching! Best, Heinrich
Hi Heinrich! Thanks - any thoughts on leaving tables as ranges or format as tables? Thanks
Hi! Not my preference but trust this is of course possible as well :) Best! H
This is such awesome content
Super happy to hear that - thank you Brian! Best, Heinrich
Implement a table on your data
Ctrl + T
the column width will auto. adjust, you can choose your layout for a more cohorent feel accross all your Excel sheets and you have a dynamical range for your pivottables or formulas.
Good one, thanks for adding! Best, Heinrich
Hallo Heinrich, kurze Frage: Findest Du die Microsoft Excel Expert-Zertifizierung sinnvoll? Und würde mir das Vorteile gegenüber anderen Bewerben geben?
Hi! Gute Excel-Skills sind natürlich wichtig. Grade für deine Bewerbung bei großen Beratungen denke ich aber nicht, dass solche Zertifikate einen signifikanten Unterschied machen. LG! Heinrich
Column D is "Quantity". I would recommend to round it up/down to the nearest whole number.
Hi Kaushal, yes agree ;) Best! Heinrich
Hi Heinrich! Você precisa traduzir este conteúdo para o português! Sem dúvidas vai ser um sucesso! Sempre compartilho o seu material com colegas de trabalho.
Hi Juan, thanks for sharing the videos in your organization :) Might indeed think about translating it to more languages (or with captions) in the future. Stay tuned! :) Best, Heinrich
Another tip, though not really formatting tip - Run a spell check (merchandise is spelt incorrectly in the header) 🙂
Fair point ;) Thanks for watching Arnab! best, Heinrich
Loved the content!
Great to hear that - thank you Shiv! Best, Heinrich
Hello Heinrich, thanks a lot!! Would you please recommend free resources to learn how to interpret charts and graphs in consulting interviews ?
Hi Paola, suggest to check out the GMAT prep material on "Integrated Reasoning" as the tasks are very similar! Best, Heinrich
Thanks a lot!! Greetings from Peru
Hi Heinrich, I’m starting my journey with McKinsey in September! I just wanted to ask you, what should I do to be best prepared ? Maybe an excel advanced course? Thank you 😊
Hi Greta, this will not be absolutely required, but if you already have intermediate skills in PowerPoint and Excel when you join, this will for sure help you to add real value to the team early on! Lots of success to you and congrats for getting the job :) Best! Heinrich
A sneaky consulting trick is to title every Ppt or Excel as at least ver3 or ver4 as a minimum.
This video focuses on making the worksheet perfectly clear. Always consider the option of including an instruction or comment sheet as sheet 01.
Hi Bruce, thanks for the comment! Yes indeed for more complex models, adding a sheet with commentary is very common to do! Best, Heinrich
Why don’t you use tabs ? I like them since already formatted and automatic formulas
Maybe personal preference, but find this way of working more flexible! Thanks for watching Tom. Best, H
🎯 Key Takeaways for quick navigation:
00:00 🤖 *Introduction to Excel Formatting Importance*
- Introduction to the importance of proper Excel formatting in consulting and professional roles.
- Emphasis on the struggle many people face with Excel formatting.
- Objective of the video: to demonstrate professional Excel sheet formatting.
00:57 💡 *Reasons for Necessary Formatting*
- The necessity of formatting for clarity and communication of key messages.
- Importance of high-quality formatting in professional services and client expectations.
02:24 🛠️ *Practical Tips for Excel Formatting*
- Demonstrating practical formatting tips: renaming files, adjusting fonts, and layout modifications.
- Focus on aesthetics and clarity in formatting for client presentations.
05:20 📊 *Advanced Formatting Techniques*
- Advanced techniques: freezing panes, number formatting, and incorporating currency symbols.
- Comparing before and after formatting to highlight improvements.
06:17 📈 *Building Complex Layouts*
- Tips for building complex Excel layouts, with emphasis on vertical table arrangement.
- Recommendations for dashboard creation and managing multiple tables.
08:12 🎨 *Formatting Conventions for Complex Models*
- Importance of color-coding to distinguish between input and calculated fields.
- Example of applying formatting to enhance model comprehensibility.
09:12 📢 *Conclusion and Engagement*
- Summary and invitation for viewer engagement.
- Call to action for likes, comments, and subscriptions.
- Promotion of social media platforms and membership perks.
Made with HARPA AI
When dividing numbers you should always use the excel formula +Iferror(number 1/number2;"0") so if it divides a number that is zero it will always return the value to zero instead of "#Div/0!"
Hi Rasmus, yes this will indeed help in most cases! Best, Heinrich
Please note that ”0” is not a number, but 0 is.
There's always something I notice, but never understood: Why do people always make Row 1 and Column A empty? I'm not sure about its purpose, and it's an additional step you have to do for every sheet. Makes doing VBA reference slightly more complicated as well.
Hi Jaonn, this is rather an optics thing. Agree that if the objective is to make this machine readable, this is not the best approach. Best! Heinrich
Hi Heinrich, how about the chart file? Is it recommended to put in same table tab sheet?
Hi Fadira, not 100% sure I understand the question, can you explain a bit more pls? Best! Heinrich
@@FirmLearning Hi Heinrich, I need to present some charts periodically to client. Some times I recreate it, sometimes I re-refresh it, some times I miss a chart (one table can have several charts and there are so many tabs or sheets).
What is the tips to easily manage excel charts to make it easier to present it in periodic basis? Is it by creating a standard chart in the same excel tab with its raw data or table? Is it by using pivot chart (as long as one not forgetting to refresh it), is it by putting it in one special tab with many charts in it? Is it one chart one tab?
Or Is it by recreating chart every time it will be presented? (Thus the created chart is always the latest one, not the old one)
Need to know how is the best way to manage charts to make life easier.
Thanks you very much Heinrich (always look forward to get your new insight)
I always wondered what consultants at McKinsey are doing. Here we go, doing the work of an intern. Well-earned salary!
;) Thank you for watching! Best, Heinrich
The table borders are to bold. Use thin grey border lines.
Fair point :)
Hairdresser time, Heinrich ;)
Got that comment from my wife as well ;) Best! H
Would it be possible to say in your next video---"Where is Sarah Conner?"
;) Thanks for watching Scott. Appreciate it!! Best, Heinrich
Why do people still put the date in the title? I think it’s a Windows XP artefact, nowadays we can sort files by date (the creation date will stay the same even after sending it to a collegue a month later) with one click. It’s looks confusing and not clear. Hard to find the right document.
Hi Julia, one reason would be to mark more substantial changes. You might open a file and change just a minor detail on a format of a cell and the automatic change date indication would go up, though this is not always desired. Best, Heinrich
@@FirmLearning No, you can see the date of creation and the date of the last edit. The creation date never changes.
Hi, is an MBA necessary in order to proceed in the career of a strategy consultant?
Hi! No it is usually not necessary (but nice to have). Best! Heinrich
Universally hard coded numbers are formatted blue and calculations are calculated black...
Thank you for watching!
why not just use tables?
Yes, definitely something that could be done here! Best, Heinrich
On minute 4:00, the use of text boxes would be better, case you auto-adjust the width of that column, it doesn´t follow the width of the tittle 😬
Hi, thanks for your comment! Personally do not like working with text boxes in Excel so much, but understand it can be helpful. All the best! Heinrich
nice
Hi, thank you for watching!! Best, Heinrich
Eine leere Spalte A links von der Tabelle ist ein absolutes NoGo in Excel. Wie von mehreren bereits kommentiert, ist Ctrl+T hier m.E. geboten. Es ist ja offensichtlich, dass weitere Analysen in Pivottabellen o.Ä. erfolgen.
Hi Jens, danke dir für den Kommentar. Stimme dir zu, dass für einige Anwendungen man bestimmt keine freie Spalten dieser Art haben will. LG, Heinrich
Check out the channel of Leila Gharani here on UA-cam, she‘s an Excel pro! I learned so much. I can recommend the mini series where she designs the annual report. It teaches a lot about how design delivers information.
Thanks for watching Julia!
Yep, Leila und Mynda sind das Maß der Dinge
Isn't there a practice template available for this file?
Hi, check out the video on shortcuts that I released shortly after, it contains a version of this file that you can also use for this video! Best, Heinrich
@@FirmLearning thanks Heinrich
I would say, learn a lot of excel and tools alike that you use, then learn to do the same things but with python and automatize everything and then they cant fire you cos noone would know how to do your job as good and quick as you c:
There for sure is some truth to that ;) Thanks for watching! Best, Heinrich
I listen to you but hear the terminator
;) Thanks for watching Tomer! Best, Heinrich
As a future investment banker I had to cringe when you used the mouse in Excel and colored hard coded cells in black and formulas in black - it should be the other way around.
BIG game changer: don’t use your mouse and learn shortcuts. For example removing gridlines is Alt + W + VG, renaming sheet is Alt + H + O + R, etc.
Fully agree, my shortcut game is not at the level of someone working in IB :) Probably never will be lol
Close your eyes, and you'll be teached by Arnold Schwarzenegger
;) Thanks for watching! Best, Heinrich
You can tell this is true consultancy because it looks great but there's a fundamental spelling error - "Merchanside". Shine over substance.
(Just joking, these are great tips, thanks!)
lol fair point ;) Thanks for watchign Guido! Best, Heinrich
I'm seeing no reason not to format the data as a table - if there is any use beyond printing (read: actual analysis), then there is no reason not to format the data as a table.
Case in point: at 8:20 when the new calculated column is added, in a table, that addition is seamless in a table.
Hi Dave, yes agree, this could have been done indeed! Best, Heinrich
Heinrich, I like your videos but can you please have an actual drink in your mug.
I have, in most of my videos 😅
lol I expected much more
Sorry to hear it did not meet your expectations. Still thanks for watching! Best, Heinrich