10 Excel Things You Should NEVER Do and What to do Instead - My Online Training Hub
video description
Date: 2022-04-08
Related videos
Comments and reviews: 10
Central
Leading Zeros. I was working with binary data and needed the leading zero. So -1- needed to be 01. But how the heck do you get the leading zeros to show up? PRESTO! Format cells and put two -00-s in a custom format and like magic, there's the leading zero showing in the cells. BUT the formula bar still shows a single number despite the formatting so you can not use formulas that use the leading zero on the cells even though the cells -show- a 01, 02, 03 etc. For example, if Cell A:1 contains a custom -00- formatting and the cell shows 07 (for the number 7), you cannot extract the leading zero with a Mid function, =Mid(A1, 1,1) because even though the cell -shows- 07, the formula bar shows the regular 7. Now, I know what you're thinking. Covert the cells to text. Wonderful, but it doesn't work. The convert doesn't reference the cell, it references the cell's formula bar value so you _still_ get a 1 digit 7.
Of course I can go through the 5543 records one at a time and change the values manually (and collect social security when I'm finished).
I did find a solution.
If you reply, I'll tell you what it is. Or, let's see if you can guess it.
reply
Leading Zeros. I was working with binary data and needed the leading zero. So -1- needed to be 01. But how the heck do you get the leading zeros to show up? PRESTO! Format cells and put two -00-s in a custom format and like magic, there's the leading zero showing in the cells. BUT the formula bar still shows a single number despite the formatting so you can not use formulas that use the leading zero on the cells even though the cells -show- a 01, 02, 03 etc. For example, if Cell A:1 contains a custom -00- formatting and the cell shows 07 (for the number 7), you cannot extract the leading zero with a Mid function, =Mid(A1, 1,1) because even though the cell -shows- 07, the formula bar shows the regular 7. Now, I know what you're thinking. Covert the cells to text. Wonderful, but it doesn't work. The convert doesn't reference the cell, it references the cell's formula bar value so you _still_ get a 1 digit 7.
Of course I can go through the 5543 records one at a time and change the values manually (and collect social security when I'm finished).
I did find a solution.
If you reply, I'll tell you what it is. Or, let's see if you can guess it.
reply
wsullivn1
As usual, a great video! You left out two of my favorite pet peeves. First, NEVER put a number in a formula. I've inherited models that contain something like =A1+B1-1.37. Where did the 1.37 come from - who knows? INSTEAD use a name where you describe what the number means in the Name Manager comments, or create a sheet with named cells for all the adjustments and factors you're using and provide an explanation for each. It also helps if you use this factor in multiple places in the spreadsheet and it changes, you only have to update it in one place. Second, NEVER put labels separately in cells. INSTEAD create a custom format that contains the label.
reply
As usual, a great video! You left out two of my favorite pet peeves. First, NEVER put a number in a formula. I've inherited models that contain something like =A1+B1-1.37. Where did the 1.37 come from - who knows? INSTEAD use a name where you describe what the number means in the Name Manager comments, or create a sheet with named cells for all the adjustments and factors you're using and provide an explanation for each. It also helps if you use this factor in multiple places in the spreadsheet and it changes, you only have to update it in one place. Second, NEVER put labels separately in cells. INSTEAD create a custom format that contains the label.
reply
Martin
I've started to use Center Across Selection a while ago. The REALLY annoying thing: it's only horizontal... Why??????
Also, regarding #9. Instead of using the OFFSET function, I just leave a blank row between the data and any formula (such as SUM or AVERAGE), and I include the blank row in the formulas. Then, if you add rows, you select this blank row. The new inserted rows will come on top and automatically be included in the formulas. I also find it helps with presentation, especially when there are a lot of data rows (or columns for that matter): the data are in one place, and the calculations are just a bit separate and easy to find.
reply
I've started to use Center Across Selection a while ago. The REALLY annoying thing: it's only horizontal... Why??????
Also, regarding #9. Instead of using the OFFSET function, I just leave a blank row between the data and any formula (such as SUM or AVERAGE), and I include the blank row in the formulas. Then, if you add rows, you select this blank row. The new inserted rows will come on top and automatically be included in the formulas. I also find it helps with presentation, especially when there are a lot of data rows (or columns for that matter): the data are in one place, and the calculations are just a bit separate and easy to find.
reply
Meni
One piece of advice that I would add to your -ten commandments- is this:
Never use hard-coded values instead of parameters/arguments.
If you use a value that might change (for example: VAT percent, budget year, Sales Tax), don't hard-code it in the formula.
So, if you want to calculate the price of an item:
Don't write: =ItemPrice-1.17
But: =ItemPrice-(1+VAT) where VAT is stored either as a named range or as an argument in a cell.
The advantage: If your VAT percent changes, you don't -tamper with- innumerous formulae, You change the VAT value in one place only.
reply
One piece of advice that I would add to your -ten commandments- is this:
Never use hard-coded values instead of parameters/arguments.
If you use a value that might change (for example: VAT percent, budget year, Sales Tax), don't hard-code it in the formula.
So, if you want to calculate the price of an item:
Don't write: =ItemPrice-1.17
But: =ItemPrice-(1+VAT) where VAT is stored either as a named range or as an argument in a cell.
The advantage: If your VAT percent changes, you don't -tamper with- innumerous formulae, You change the VAT value in one place only.
reply
Julie
Non-tabular data is my biggest problem in Excel. I have data with projects as rows, and future projected allocations as columns by month. The thing is, I'm using those columns for future planning, not past reporting, so the data is dynamic and need to be able to change the values on the fly. It helps when I can visualize a single project over time in a row, in context with other adjacent projects. Pivot-table does not allow you to edit the data. Any tips for that scenario?
reply
Non-tabular data is my biggest problem in Excel. I have data with projects as rows, and future projected allocations as columns by month. The thing is, I'm using those columns for future planning, not past reporting, so the data is dynamic and need to be able to change the values on the fly. It helps when I can visualize a single project over time in a row, in context with other adjacent projects. Pivot-table does not allow you to edit the data. Any tips for that scenario?
reply
Maciej
0:47 -there's nothing I can do about it- - that's not true. You can simply select the 6 cells under the UK, and then holding control, select the USA's ones. This sum feature works with multiple selections, it doesn't have to be continous. Although I fully agree it's better to avoid merging cells, it's goods to know one can still do something when faced with mess like this.
reply
0:47 -there's nothing I can do about it- - that's not true. You can simply select the 6 cells under the UK, and then holding control, select the USA's ones. This sum feature works with multiple selections, it doesn't have to be continous. Although I fully agree it's better to avoid merging cells, it's goods to know one can still do something when faced with mess like this.
reply
iClone
6:41 can someone tell me what she's clicking here, I can't replicate this. Is she selecting the entire column by clicking -E,- or is she selecting the header? Either way, when I try it, I can't get it to highlight just the column contained within the table. I either end up selecting the entire column extending down, or I end up just highlighting the individual cell.
reply
6:41 can someone tell me what she's clicking here, I can't replicate this. Is she selecting the entire column by clicking -E,- or is she selecting the header? Either way, when I try it, I can't get it to highlight just the column contained within the table. I either end up selecting the entire column extending down, or I end up just highlighting the individual cell.
reply
UltimateBargains
5:50 Cells only contain data when the cell is modified with data. This applies to formatting.-
The cell only contains formatting data when it contains actual data.-
Empty (untouched) cells inherit formatting from their row or column header information.-
That's why applying formatting to an entire row or column doesn't drastically expand the file.
reply
5:50 Cells only contain data when the cell is modified with data. This applies to formatting.-
The cell only contains formatting data when it contains actual data.-
Empty (untouched) cells inherit formatting from their row or column header information.-
That's why applying formatting to an entire row or column doesn't drastically expand the file.
reply
Aline
Last month I had a huge problem in my excel, it took 5 hours with an employee from Microsoft to solve it, he told me that it could be related with some spreadsheet that I uploaded and some supplement could have been the reason it wasn't working properly. Every day I fear that could happen again -.
reply
Last month I had a huge problem in my excel, it took 5 hours with an employee from Microsoft to solve it, he told me that it could be related with some spreadsheet that I uploaded and some supplement could have been the reason it wasn't working properly. Every day I fear that could happen again -.
reply
Silvia
Oh wow. There is so much I still don't know about Excel even though I've been using it for many years now. My excuse is that at work, people only use it as a very simple tracker of sorts, without pivot tables or anything else. I dare say few of us at work are aware of how powerful Excel can be!
reply
Oh wow. There is so much I still don't know about Excel even though I've been using it for many years now. My excuse is that at work, people only use it as a very simple tracker of sorts, without pivot tables or anything else. I dare say few of us at work are aware of how powerful Excel can be!
reply
Add a review, comment
Other channel videos
