10 Excel Things You Should NEVER Do and What to do Instead

i received a lot of excel files from our members over the years and i've seen the same mistakes time and again so in this video i'm going to list the 10 things you should never do in excel and what to do instead to avoid trouble 99 of the time you don't need to merge and center cells now one of the problems with merged and centered cells is they interfere when you select a range of cells and that can be super annoying for example let's say i want to get a tally of the units i can select the column of units for the uk and you can see down in the status bar that i've got a sum of 1511 but let's say i wanted to get the sum of the units for the uk and the usa as soon as i get to the usa's cell it's merged and now i'm forced to select all three columns there's nothing i can do about it the other problem with merged and center cells is they can prevent you from sorting data and copying and pasting and they can cause formula errors a better way is to use center across selection so center across selection you simply select the cells you want to center the label across same as for merging center cells except here you press ctrl 1 to open the format cells dialog box and then on the alignment tab under horizontal we're going to choose center across selection so you can see that it looks exactly the same let's repeat that for usa now when i select the column of values i don't get any issue with merge and center cells it allows me to select just the cells that i want now i should point out that there's no equivalent for center across selection for vertical alignment so for example you wanted to align labels across a series of rows you would need to still use merge and center for that one of the biggest mistakes i see is data in the wrong layout like this now at first glance the layout actually looks quite good here however the year values are split across multiple columns and when you do this it makes it difficult to use the built-in tools like pivot tables and functions that are designed to work with data in a tabular layout now if you're spending time trying to wrangle formulas to aggregate data it's probably a sign that your data is in the wrong layout the correct layout is a tabular layout so let's take a look at that here in this example i have just one column for the values and another for the year and when your data is correctly laid out it's quick and easy to summarize it with formulas or pivot tables in fact we can take this data and put it into this layout with a pivot table with just a few clicks for example here i can insert a pivot table let's pop it up here on the same worksheet so we can see it in context so here i want the years across the columns i want the category product and sales we can turn off the subtotal we can change the layout to tabular and there i've pretty much got this layout in a pivot table now thankfully you can easily fix this layout using power query to unpivot the year columns and this is just one example of the wrong data layout there are many more at the solution linked to in the card in the top right of the video dates formatted as text which usually come from files exported from external systems can be tricky to detect because they look the same on the face of the cell and sometimes even in the formula bar however if you use the keyboard shortcut control and back quote dates entered correctly will display their date serial number as you can see here in column d to revert back press control in the back quote again now the reason dates formatters as text are problematic is because you can't reference them in formulas for example here i've got a simple formula that calculates the latest date minus the first date and i get a value error because excel can't interpret these dates as dates whereas the date serial numbers correctly calculate and give me 327 days so the solution is to convert text dates to date serial numbers there are several ways you can approach this which are covered at the link in the top right of the video you've probably seen this error before which is caused when you have links to external files if you have access to the external files you can update the links however if you don't and you choose don't update you get a second warning to say that you can continue but of course your workbook may now be out of date data integrity is essential so introducing risks like this is very dangerous not to mention these functions also don't work when referencing closed external workbooks so here i've got some countif formulas if i want to edit that formula let's just f2 to edit press enter i now get a value error because the countif formula can't resolve against a closed workbook the only way to resolve the error is to open the closed workbook at the same time as this workbook now a better option if you need to reference data in an external file is to use power query to get the data and bring it into your file you can then refresh the query without opening the external file to get any updates and your formulas won't break there's a link to a tutorial on power query at the top right of the video it's quick and easy to apply formatting to a whole column or even a whole row but this just adds unnecessary data to your file because even these empty cells still contain data from excel's point of view because they're full of cell fill color instead of doing this let's control z to undo a better option is to format your data in a table ctrl t to do that my table has headers so i'll click ok the formatting is automatically applied and if i add any new rows let's say 2016 you can see the formatting is carried on now you can change the type of formatting up here on the table design tab and then in the table styles gallery and there's a load to choose from you can even create your own custom style or if you prefer to just set your own format maybe to one column you can choose the light format which doesn't have any formatting essentially and then you can simply select the column that you want to apply the formatting to and then apply it like that now when i add a row you can see the formatting's automatically copied down but i don't have any excess formatting in cells that i'm never going to use remember excel spreadsheets have over a million rows so that's a lot of empty cells with formatting if you choose to go down the path of formatting a whole column it's common practice to use cell fill colors to encode data the problem with this is you can't reference self-fill color in formulas which makes it difficult to count data that matches a fill color for example here i can't easily count how many days of the week are shaded blue however if i use numbers or text to encode the data i can then select those numbers and apply a conditional formatting so for example here i can say if the cell contains a 1 let's give it a custom format with the cell fill color in pale blue and then on the number tab i can create a custom format that hides the number by entering 3 semicolons and then okay so now we have the same appearance but with this example i can count how many days a shade of blue with a simple count formula that references the cells and we get 12. there's no need to use garish colors to highlight data instead of bringing attention to the data it makes your reader uncomfortable maybe they even need to reach for their sunglasses and the information is difficult to read it's much better to keep it simple and use complementary colors that don't detract from your message it can be tempting to shove as much data in a single cell as possible but it's difficult to then analyze that data with formulas taking this example here you might want to know how many people are listed for wednesday with this layout it's very difficult to do anything but count by i whereas a layout with separate cells for each day you can easily add a formula to count the number per day and again i could hide the y's if i prefer just to have the blue shaded cells and my count formulas will still work here i'm using the count a formula because i'm counting text adding new rows of data to a sum formula can sometimes result in the new row being omitted from the sum so for example if i add a row here and then let's say we want to add data for august and we want to copy that formula down and let's say it's 39 you can see the formula here hasn't updated to include that new row the solution is to use the offset function to return the reference to the cell directly above your formula so here the second part of my sum reference is returned using the offset function and what it's doing is it's referencing the cell my sum functions in cell j13 and then it's moving up one row minus one we'll take it up one row and it simply returns the cell reference one row above the cell i'm currently in and now if i copy that formula down and let's say we add 39 here you can see my formula has included it automatically now to be fair this was more of a problem in earlier versions of excel however it does still happen in microsoft 365 as you can see with this example so it's just something to be mindful of the dot xls file type was replaced with xlsx from excel 2007 onward however many third-party systems still have an option to export data to xls now.xls files are based on the binary interchange file format or bif for short and they store information in a binary format whereas dot xlsx files are based on office open xml format that stores data in compressed xml files in a zip format for example if we take a look at this file here and open it with zip software so i'm going to open the archive and if i click on excel you can see the different components and settings that make up this file now if you open a xls file in versions of excel from 2007 onward you will still have access to the up-to-date functionality however you're going to be limited to 65 536 rows and 256 columns xls also doesn't support auto-save and macros are able to be saved in this file format which makes it less secure than the newer xlsm file format which has more robust security plus when you try to save this file you're going to get bombarded with a load of compatibility warnings that are super annoying so the solution is to make a copy of this file and change the file format to xlsx i hope you found this video helpful you can download the file for this lesson from the link here and you'll find links to all the tutorials mentioned in the video description and if you like this video please give it a thumbs up and subscribe to our channel for more and why not share it with your friends who might also find it useful thanks for watching [Music]

Channel: MyOnlineTrainingHub

Views: 44,100

Rating: 4.9804115 out of 5

Keywords: excel, microsoft excel

Id: Edms-B4ViHo

Length: 12min 33sec (753 seconds)

Published: Thu Sep 30 2021