Related Posts
PwC India Hi Folks, I left my stable job at Deloitte USI and moved to Germany for a better job. But my mom had a major health issue and I'm planning to travel back to India to support her as I'm a single child. I'm working in one of the top organisation in Germany with a base of 90k euro and looking for good opportunities in India. Availability: immediately Tech stack: SAP BASIS/Hana with azure/GCP Exp: 8.8 EY PwC India Pwc AC Accenture Deloitte HCL Technologies
Additional Posts in Consulting
Should I buy iwatch of it's too sad !?
Layoffs at Walmart corporate 😬😬
New to Fishbowl?
Download the Fishbowl app to
unlock all discussions on Fishbowl.
unlock all discussions on Fishbowl.
Know that you will commit excel sins. Lots of them. Don’t beat yourself up. We all do it. That all being said, make sure that you’re always mindful of when you need to lock and unlock cells within a formula
1. Writing numbers into formulas instead of referring to that number in a separate cell (e.g typing =B3*0.8, instead of putting the putting 0.8 into cell D3 and typing =B3*D3).
2. Not noting sources / rationale behind assumptions. You think you’ll remember where an assumption came from. You won’t. I’ve done this and seen this happen too many times!
3. Not clearly differentiating between input cells and calculations. Always use different sheets and/or different colours. Provide a key.
4. Merging cells, as someone else said. Always use ‘centre across selection’ instead (ALT H F A is the shortcut to get here, and you can use CTRL + Y to repeat)
5. Pointing formulas to isolated cells instead of building the model in a way that allows you to use a consistent formula (e.g. sumifs, index match, sumproduct) for a given row or column. This means being diligent about building mapping tables and helper columns as needed.
6. Not keeping tables neat. Get to know the border shortcuts (Alt H B ...) - it will save you tons of time, and it’s so much easier to follow an Excel that has neat borders separating different groups of columns/rows, and clearly identifying total rows.
7. Not stating units
8. Changing the format of the source data. You want to be able to paste in a refreshed set of source data without having to re-link all your formulas
9. Not checking for locked cell errors in your formulas before troubleshooting elsewhere. No matter how careful I thought I was, I swear that 9 times out of 10 when a formula wasn’t working how I thought it should, it was due to locking the wrong cells
10. Not building in (visible) error checks. Ideally you want a master error check cell for each tab that tells you if there’s an error on that tab, and then a master one across the whole model.
Bit of a stream of consciousness, but hopefully helpful. In short, always be ready for someone else (another team members, a client) to potentially need to see your model. Sometimes you need to take shortcuts to do some quick calculations and hit a deadline. Have sufficient pride in and ownership of your model that you then go back and fix these shortcuts later. Keep things neat and in line with best practice as you go as much as possible - it’s worth the slightly longer time investment upfront to do it right first time.
And ask for help if you need it - people are generally happy to offer tips and advice :)
Good luck!
@A3 - it’s a little difficult to explain in a small text box here, but will do my best by giving a simple example.
Say you have an output you need to build where you need to show figures (e.g. cost) by category, so you write the different categories in rows 3-6. In a calculation sheet behind, you have the costs, but by sub-category.
What you want to do is have a mapping of sub-categories to categories (ideally in an input / assumptions sheet at the back), and create a ‘helper’ column on your calculations sheet that uses index match to capture the relevant category for each sub-category. You can then use sumifs on your output tab that points to this helper column to sum the cost by category.
What you do NOT want to do is point your formula on the output tab directly to the cell references that relate to a particular category e.g. =sum(‘Calculations’!C3:C10) as the formula for cost category 1, =sum(‘Calculations’!C11:C14) for cost category 2, etc. This means that if a sub-category changes category (which does happen), then you have to manually update the formula on the output tab to make sure it captures the right sub-categories, rather than just updating the mapping on your input sheet. It also means that the formula is not consistent on your output tab - the formula to pull the cost is different for each cost category.
This might seem an obvious example, but the principle should be applied wherever possible - avoid referring directly to cell references (I.e. =some random cell or cells), and bias to using sumifs, index match, sumproduct, etc wherever possible. Exceptions would be important input cells that are used frequently (e.g. growth rate, a cell containing 1000 that use as a divisor) - in this case, you can point directly to them as a locked cell in your formula, or use the naming approach that someone suggested above.
Hope that helps? DM me if still unclear :)
This girl I knew typed out 1-200 to make a list
help her learn and get better
Chief
Not using excel to calculate something and then hardcoding it.
Ha its always fun to find things that have been randomly hard coded in a matrix of formulae for years and years
Merge cells
And you can’t auto fit row height properly
The post that said vlookup>index match
@C1, definitely. The Array Function comes in handy for large databases and to create pivot tables. That's my 1%, but yeah everyone should master Index/Match first 🤓
Whenever you're stuck, post it on fishbowl and we'll come to the rescue 💪🏽
Leaving in external links unless absolutely necessary.
If you need the link, copy it as a comment and then hard code the value.
Rising Star
Not removing the F1 button from your keyboard
F1 is the Help button, which we obviously don't need cause we are very excel savvy (and humble)
F2 let's you edit a cell directly, which is frequently used.
By removing the F1 button, you no longer accidentally hit it and get the stupid pop up Help window.
See that scroll bar? Don’t touch it.
Why
These are all already good. Also... not organizing your workbook into multiple sheets. Not using consistent formatting. Not citing sources. Not separating source data from derived data. Not using a shit ton of keyboard shortcuts
Truth! Keyboard shortcuts are sexy 😍
Building tables of data and not using the 'table' functionality. Makes it so much easier when using formulae and so much more robust when the workbook format changes