Having worked in a large corporation, like Citigroup, and having been part of a small founding team, there is one consistent conviction I have gained. If there is one training exercise every employee should go through, irrespective of its size, it should be centered around Microsoft Excel.
Excel is, by far, the single most used application in a majority of organizations, so it amazes me how inadequately trained employees are at it. There seems to be no formal training provided in this very important application, and employees are just expected to learn on the job.
At Citigroup we used to service U.S. based clients, which in India meant working night shifts.
My theory was that if all these employees, who usually worked long 12-hour shifts, were given proper Excel training, they would all go home about an hour earlier. I know it's an oversimplification but it's always good to have a simple objective rather than a complex one. Along with a couple of other colleagues, we devised a training course and have trained more than 200 employees.
In this post, I will discuss a few basic, important Excel lessons that will help everyone do their job better and do so independently.
Excel is an immensely powerful tool, which helps us work with accuracy and speed. However, if not used correctly it can seem daunting and can slow users down instead of helping them.
At Citigroup, we had an ongoing competition among our colleagues around who could work on Excel for the longest period of time without touching the mouse.
It may seem weird at first, but it's all about navigation. As I mentioned, if used correctly, Excel can speed up your tasks. But every time you move your hand away from the keyboard and onto the mouse, you're slowing yourself down.
So we learned a lot of navigational and functional shortcuts using keyboards.
For example, if you have a data set of 100 names in a column, and you want to go to the last name in the column, the most common way to do this is to scroll down with your mouse or one-by-one with your down arrow key until you reach the end.
Here's the faster route. Use Ctrl + Down Arrow Key for Windows or Command + Down Arrow Key for Mac.
Simple. As I’m sure many Excel users have experienced, lists can be 1,000 or even 10,000 names long. Similarly, if you want to move to the end of the row, which has 30 columns, use Ctrl + Right Arrow Key for Windows or Command + Right Arrow Key for Mac.
If you think about it for a moment, it becomes very intuitive. As you have seen above, using Ctrl / Command gets you to the last data point. Now, you understand that using Ctrl / Command with the proper directional arrow keys gets you to the end of either the row or the column.
Then, it becomes easier to build upon this.
Let’s take the same example of 100 names in a column, but pretend instead of just going to the last name in the column, you want to select those 100 names to copy and paste it somewhere else.
Again, the common way to do this is to use your mouse to select the first cell, and drag down till you reach the end. The quicker way to do this is by clicking Shift + Control + Down Arrow Key for Windows or Shift + Command + Down Arrow Key for Mac.
We already know Ctrl / Command + Arrow Key takes us to the end of the data set, now we have added “Shift” to our process to help us select that data set.
Regardless of the size of your data set, it will always take the same amount of time to select this data set versus using your mouse to copy from the first cell to the last cell. The amount of time it will take to copy 100 names, and copy 10,000 names will be very different.
If you want to select an entire row, try Shift + Spacebar. If you want to select an entire column, use Ctrl + Spacebar. These are small hacks that will help you eliminate using your mouse and keeping your fingers on your keyboard.
In my 10 years of Excel experience, I've learned that navigation and selection are the two most common activites performed in the software; therefore, simply learning how to navigate around in it will help you achieve one of Excel's biggest benefits: speed.
Another very powerful function of Excel is Paste Special. We all know the famous Copy + Paste options, but Excel provides even more immensely powerful features for pasting copied data in different ways.
Below are all the available Paste options, let's look at the three most common ones: Formulas, Values and Formats.
- Formulas: This is useful when you have a cell with a formula that is well-formatted - with borders, color and a specific font -- and you want to copy just the formula to another location. Simply copying and pasting would copy all the aspects of the original cell, including the color, border and font. And if the destination cell has another format, you will need to change it back to its original format.
- Values: Similarly, if you have a formatted cell with formulas, and you want to only copy the value of the cell, then this function is useful. Or, if you have an entire section full of formulas, and you want to turn that into absolute values, simply copy the entire section, and paste it on top of it with this function: paste special + values.
- Formats: This is useful when you have an Excel sheet you have perfectly formatted, and you want to replicate that format somewhere else, in the same file or another file. Using paste special + formats will copy just the format, without any other value or formula.
Conditional formatting is also extremely effective and powerful.
Humans respond well to visuals. Conditional formatting helps us visualize the data, depending on certain conditions.
For example, let's pretend you need to create 30 variations of a marketing slogan. The restriction is that it can't be more than 50 characters.
We can use a simple function, like “len,” (short for length) to determine the character count.
To top it off, we can apply conditional formatting to say: “If the count of characters is greater than 50, color the cell red, or if the count of characters is less than 45, color it green."
This quickly tells you if the count is above 50, but still gives you the opportunity to decrease the slogan to the applied character count.
Shortcuts like this help improve accuracy within Excel, and the beauty of it is the efficiency improves as the data set increases. Therefore, if you are analyzing 30 marketing slogans, you can do it manually, but if you are analyzing 3,000 sentences' word count, it makes it exponentially easier to use the shortcuts listed above.
Even a simple tool like autofill that allows you to populate a column with sequential dates removes a lot of manual work.
To use it, input the first two dates. Select them. Then you will see a cross on the bottom left, like in the gif below. Select it, and drag it to the end. This seems like a very simple solution, but when used in other situations, such as filling in serial numbers, or even filling in even and odd numbers, it certainly saves a lot of time.
Now, let's chat about freeze panes, which is one of my favorite shortcuts.
I’ve had so many people ask me about the concept of freeze panes. They understand the concept, but they aren't sure how to use it. Here's what you need to remember: north west.
Freeze panes are used when the size of the sheet you are analyzing is bigger than your screen size, which is most often the case. Let's say you have a date column on the far, left column and city names on the first row.
Maybe you are trying to find a particular cell that corresponds to a date and a city, and it is all the way in the bottom, right-hand corner. But everytime you go there, you lose the date and city name. Enter freeze panes.
To freeze the top row and far, left column, place your cursor on B2.
Excel freezes all rows above the cursor and all columns to the left of it, so everything to the north and everything to the west is frozen regardless of where you scroll.
In the gif below, you'll see how the dates and cities disappear when scrolling before applying the freeze pane.
Keep in mind that Excel cannot be taught, it must be explored. Only by consistenly using it will we get better at it, and overcome our fear of it.
Learn the basics first, and then build more knowledge on top of that, and you'll begin to work more and more efficiently. More often than not we know what we want, and we know Excel can do it, but unfortunately, many people are unable to find the functions they need to achieve their objectives. By learning the basics, it will help you get there.
As for the companies, teaching Excel can have a huge (positive) effect on employee satisfaction and morale because it shows them that you care about their professional growth.