Intro Video Transcript

Excel Efficiency: Tips and Tricks By David Ringstrom Hello, I am David Ringstrom, A CPA from Atlanta. Welcome to this Illumeo course session titled “Excel Efficiency Tips and Tricks Part 1. This is the first part of a series, and in this series I will be discussing numerous ways to use Excel more effectively. I’m very fond of saying “Either you work Excel, or it works you.” And what I try to focus on in my sessions are the nuances of Excel that cause folks to get tripped up or spend more time doing work than they should. So, this session is designed to empower you to put more tools in your toolbox and to help you avoid frustration when you’re working with Excel spreadsheets. I’m going to be teaching from Excel 2010 today but pretty much everything I cover is going to be in the same location in Excel 2013 or Excel 2007, if there’s any big differences then I will make notes of that on the corresponding slides. The biggest difference that you will note is that Excel 2007 does not have a “file menu” so during this presentation if you see me click on the file menu or I mention the file menu and you’re using Excel 2007 just note that you will click on that little round button that called the “Office button” instead. If you happen to still be using Excel 2003 then some of the slides will have notes in blue along the bottom of the screen and those are the equivalent steps that you would carry out in your version of Excel. I have been using Excel for many more years than I care to recount and so of course that means I have a lot of files that are still in the old “XLS” format. Those are partly still around because only recently did Microsoft finally cut off all support for Excel 2003. So, up until recently we did have a lot of clients that were still using Excel 2003, now it’s a tiny fraction of users at least that I cross paths with, and we can certainly open up these Excel 2003 books in Excel 2007 later, they have an XLS extension, and two things we need to keep in mind; I have made myself crazy more times than I care to think about it, trying to do something in Excel 2007 and late in these old workbooks found that I couldn’t do it because the work-book is in what’s called “compatibility mode.” We can see where compatibility mode shows up on the screen and have a red tip. If you see compatibility mode, just know that translates to, you’re not going to be able to use certain features in Excel as long as you keep that workbook in the XLS format. So such as in Excel 2010 and later you won’t be able to use spark lines or the slicer feature and there’s a number of other features, the little subtle nuances where you’ll try to do something you do all the time, you won’t be able to do it because you’re in an Excel 2003 workbook but Excel won’t tell you that, the feature just won’t work the way you expect it. So, as you run across these old Excel 2003 workbooks, if we go to the Step 1: file menu in Excel 2007 and later, we can click the “convert” button. Step 2: It’s going to show up automatically under ‘info’ and when we choose convert; we’ll get a download box that will give us some yada, yada about the process, Step 3: We can click the ‘check box’ then we don’t have to see that prompt again and click the ‘okay’ button. But then step 4 is very important. Step 4: whenever you are changing a file type of a workbook, you always want to make sure you close the workbook and reopen it because that transition does not fully occur until you close and reopen the workbook. So, let’s say you’re in Excel and you have a workbook that’s in XLS format and you decide ‘I’ll just go to the file menu and click ‘save as’ and save it in the new format’ in that case you’re going to need two copies of the document. One of the benefits of the convert feature is that it eliminates the old XLS file, so it basically does a swap, you trade out the old format for a workbook that has the new format and as long as in “Step 4” you click yes and let Excel close and reopen it, then you’re going to have all the functionality in Excel and you’re not going to beat your head against the wall “why can’t I do this in Excel?” it will just be some silly thing, because you open up a workbook that is still saved in the Excel 2003 format that again, has that XLS extension. To get a sense of how the convert feature works, let’s take a look at this file here, it says compatibility mode at the top and has an XLS extension, that’s how I know my functionality is going to be limited. To give you a sense of that; if I go to the ‘insert’ tab, the table feature is disabled but that’s simply because this workbook has a table in it. So that’s not always necessarily an indication that a feature can’t be used, it just means that I’ve already used the table in this particular worksheet. If I go back to another worksheet, we see the table button lights up but conversely spark lines are disabled, slicers are disables and as I work across, we’ll find that different functions and features will be disabled. Very subtle, very frustrating when that happens, so in any case to avoid problems with this as you run across these of XLS workbooks. Go to the file menu and look for that convert command; it will ask us if we want to convert it, we click okay. Click the check box so that we don’t have to see this prompt again and then click okay. This is the key step, if I said no here, then I go back to insert tab, slicers are still disabled even though it says XLSX that’s because Excel is still in compatibility mode. The compatibility mode doesn’t go away until I actually close the file and reopen it. So, we’re going to try that again, we’ll open up a different file, close this one here. Go to file  menu  recent, for example fruit.xls click open, click on save as and save it back to the desktop Then go to file menu  convert, click okay, next prompt click yes. The file closed and reopened, it happens instantly, now my spark lines are lit up, my slicers are available and also, if I went back to that folder where the file was saved (example: desktop) and click on ‘open file location’ the original fruit.xls is no longer available, it was replaced with a fruit.xlsx version.