I’m sure that just about everyone at some point in their professional lives has come across Excel and needed to use it to get some task or report completed. For myself, in a past life, I was the Manager of Data Analysis at a large company. My group produced reports on sales for 5,000+ stores on a daily and weekly basis to be sent to managers and the executives — all using Excel. My wife uses it with her job to pull data together for herself and her partners. And to be honest, I use it for just about everything I do — and my wife loves making fun of me for it. As soon as we start talking about the next family vacation, she jokes about wanting to know how fast I will be starting a spreadsheet. For me, Excel is just a very natural place for me to organize and track things.
I know we have other options now. For example, there is OneNote, which is a program I do like and do use for certain things, but I still fall back to Excel more often than not. I can honestly say this was not always the case. Back in college, when one of my classes had a section all about using Excel, I remember how glad I was when it was over.
Oh, how that has changed for me!
In college, Excel was something I was forced to learn. But later, working in a data analysis department, I was faced with two choices: either learn Excel or find another job. Well, I’m not one to run away from a challenge and I definitely enjoy learning new things, so I chose to learn Excel. For those of you who happen to be new to Excel, I understand what it’s like to have that overwhelming feeling of not knowing where to start. I originally began writing this as a single article to cover three different skill levels, but as I wrote, it kept getting longer and longer. At the same time I felt I was leaving some things out that I didn’t want to leave out. That’s when I decided to break this into three different articles and have each article focus on a specific skill level. So if you are interested in more advanced items feel free to take a look at Part 2 or Part 3 of this series.
Let’s get going!
Copy Sheets
One feature that I have used countless times, and something that even my wife thinks is incredible, is how to copy and move sheets around within Excel. This is useful if you want to make a copy of a sheet so you can change some data or test some changes while leaving the original data intact. Also, you may want to combine specific sheets from different spreadsheets into a single file. Copy sheets will achieve all of this for you. As shown in the image below, if you simply right-click on the sheet, or sheets, as you can do this with multiple sheets at the same time, you will see a pop-up menu appear with an option labeled ‘Move or Copy’. If you click on this option, a new window will appear also shown below. This is where you have a few options.
- You can move the sheet to a new location in the current spreadsheet.
- To do so simply choose the new location in the ‘Before sheet:’ section and click OK.
- You can move the sheet to a different spreadsheet.
- To do so you must first have the second spreadsheet open.
- Then choose the second spreadsheet in the ‘To book:’ drop-down list.
- Then choose the location within the second spreadsheet in the ‘Before sheet:’ section and then click OK.
- You can move the sheet to a newly created spreadsheet.
- In the ‘To book:’ drop-down list the first item should read ‘(new book)’. Select this option.
- Then choose the location within the second spreadsheet in the ‘Before sheet:’ section and then click OK.
- Perform any of the three above, but make a copy and contain the original sheet.
- To make a copy simply make sure to select the ‘Create a copy’ check box as shown in the image below.
Freeze Pane
Suppose you have many columns and rows of data and they all contain numerical values as shown in the image below. As you scroll to the right and down, you will be faced with a sea of meaningless values with no indication of what you are looking at.
To help with this, you will want to freeze certain columns and rows to lock them in place so they don’t move as you scroll. This is located on the View ribbon under the option named ‘Freeze Panes’ as shown below. You will notice there are three options to choose from. The two labeled ‘Freeze Top Row’ and ‘Freeze First Column’ are pretty straightforward and do just as the name implies. The first option labeled ‘Freeze Panes’ allows you to freeze both columns and rows at the same time. This option will freeze every row above and every column to the left of the currently selected cell. Therefore, in the scenario above I would select cell B2 and then select freeze panes. This would lock down the first row containing my headers as well as lock down the first column containing my reference dates.
Paste Special
Excel is very powerful when it comes to copying data around within itself. Obviously, you can perform a simple copy and paste action, but suppose you had a column full of formulas and you wanted to copy just the resulting data to another location. Or perhaps, you spent some time formatting a sheet or set of data and would like to copy that same formatting to another area. Or maybe, you spent some time adding headers to the first row, but decided you would rather have those values listed down the first column instead. Excel allows you to paste items in special ways based on what you choose.
To perform this action, simply copy the data as normal, but when you decide to paste it, right-click on the cell that you wish to paste the data. This will bring up a menu with an option labeled ‘Paste Special’. When you click on this, it will open a new window as shown below. In this new window, there are several options as to how you can paste the data you just copied. Most are straightforward, but one might be confusing unless you understand it. The very last option labeled ‘Transpose’ will rotate the data selected, on its axis. If you wish to rotate all of your header values from horizontal to vertical, this would give you that result.
Select Everything
I’ve seen users painfully and slowly drag the mouse over all of the data to select it for some task they need to perform. A quick and painless way to select every single cell in the sheet is to click on the upper left hand corner of the grid as shown below. This will select every cell within that grid.
Remove Duplicates
Say you have a list of names or cities, and you know there are values listed multiple times within that list, but you want to get a unique list so that each value is only listed once. You have a couple of options to do this and depending on what you want to happen to the original list. The first option is to remove all duplicates from the original list completely. Simply select the column containing the data and on the Data ribbon there is an option labeled ‘Remove Duplicates’. Simply click on this and it will remove the duplicates and report the results letting you know how many duplicates were found and removed and how many unique values are left.
The second option also gives you the ability to copy the unique list to another location keeping your original list intact. Again, select the column containing the Data and on the Data ribbon select the ‘Advanced’ filtering option. This will open a new window where you can choose to copy the data to another location, select where you wish to copy the data and choose to only copy a unique list of records.
Auto Adjust Column/Row Sizes
If you have data within a cell that isn’t showing all of the information; you may need to re-size the column or row. To quickly achieve this simply select the columns or rows you wish to re-size and double-click on the space between any of the selected columns or rows as shown below. This will automatically adjust the size to show all data.
Create New Lines of Text
In the case you want to enter several lines of text within a single cell but want to have each line of text appear on its own line might seem impossible. By default once you press the Enter key Excel will accept the text and go to the next cell. To get around this simply use Alt + Enter. This will act as a return and place the cursor at the beginning of the next line.
Status Bar
A very powerful tool within Excel is the status bar. This provides live results based on the currently highlighted cells. There are several options available with this as well. To set these options, you simply right-click on the status bar. The bar is located at the very bottom of the Excel window as shown below. The main items of interest are the ones I have highlighted below. By selecting these, Excel will provide live calculations. Suppose you want to quickly know how many cells contained data and what the sum was for that same data, all that would be required is that you select those cells and the results will be listed on the status bar at the bottom of the Excel window.
Formula Bar
Just by looking at a cell you do not know where the information is coming from. You don’t know off hand if it’s a formula and those are the generated results or if those are values entered into that cell. You cannot determine this even by simply clicking on the cell. You have to double click the cell to enter into it before you can get this information. That’s why I always have the Formula Bar visible. By simply clicking on a cell, I can determine if there is a formula present, and if so, what that formula is, and at the same time, view the result in the actual cell. To make the formula bar visible go to the View ribbon and in the Show section there are four options you can turn on and off.
Auto-Fill Data
Say you have several thousand rows of data and you want to create a SUM() formula at the far right and you want that formula to be added to all the rows of data. You could copy the cell and highlight all of the cells and paste the value in. But if you have several thousand rows this could take a few minutes. A quick built-in method is the auto-fill option. If you select the cell that contains the formula you will notice a little solid box in the bottom right hand corner. When you move the cursor over this area it will change from a hollow plus sign to a solid black plus sign. When this occurs simply double click and the formula will auto-fill down the sheet.
The guideline on how this works depends on the data in the column to the left. It auto-fills until it hits a blank cell marking the end of the data. So if you have gaps in the data to the left, it may not auto-fill to the bottom properly. This also works with multiple cells selected. So if you create multiple calculations to the right of some data, you could select all of the cells in the first row and perform this action to copy all of the formulas at the same time.
Conclusion
These are just a small sample of the items that make Excel useful even for users just beginning on the tool. But if you knew all of these tips and are looking for a little bit more, hopefully, that’s where one of my next two blogs will come in handy for you. In the next article I plan to dig a little deeper for those users who are somewhat familiar with Excel. If you found this information useful or have any questions concerning Excel, let me know below, or connect with me on Twitter, I’d love to chat!