My name is Ned, I am so excited you are reading this. This is a transcript of our first Facebook live session. If you prefer watching instead of reading, here’s a link with the entire video:
My idea with these sessions is to try and see how meeting live and discussing what we have shown you in our Udemy courses would complement what we will discuss here on Facebook.
How would you know when is going to be our next Facebook Live sessions? Make sure you subscribe. Make sure you like our Facebook page, and please make sure you go to our website 365careers.com/live-session.
Simply open 365careers.com. Then go to ‘Live Sessions’:
And you will be able to leave your email here to receive a notification indicating when our next Facebook live session is going to take place.
Having said that, let’s dive into today’s topic.
Why you need speed in Excel. Let us discuss that.
As you can see this guy speeding up toward the Excel icon, he is going to get there, sure. If you are fast in Excel, you will have time to focus on different activities on the job and in addition, you will be able to finish your work before the deadline and be able to check, which is very important. Another reason is that you can finish your work on time and fast and you will be less stressed. Stress is something I hate in the work place, and I will be really happy even if one person becomes less stressed on their day-to-day job as a result of today’s session.
TIP1: HOW TO SCROLL FAST IN EXCEL
This is a relatively easy topic; many of you who have taken our 365 Careers Excel courses and have completed at least 10%, already know how to scroll fast. Whenever you press Excel arrow keys, let us say you press the right arrow key once at a time, you will move towards the right direction and if you press the left arrow key, you will move towards the left. And by doing this, you will eventually get to where you want to be.
However, if you do this in combination with the Control key, you will be so much faster. Now, I am going to press Ctrl+ lower arrow key. This is something that will take me to row 500. Pretty easy.
You can press Ctrl+ upper arrow key and you can go to the first non-blank cell.
I can do this command in all directions; I can go to the right by pressing Ctrl+ right key and it takes me to the last number cell of this table.
I can also press Ctrl+ left arrow key, which takes me to the last number cell of this table.
It is pretty quick and allows you to be very fast when moving in Excel. Feel free to practice on your own, I think this will be great. Let’s go to tip 2.
TIP 2: HOW TO SELECT MULTIPLE EXCEL CELLS
Here is how to select multiple cells in Excel; hold the shift key and you will select multiple cells.
I am holding the shift key and pressing the down arrow, which allows me to select multiple cells.
However, if I do this with the combination of Ctrl+ shift + down arrow, I will be able to select the entire range up until the last non-blank cell.
I can select some cells using Shift+ arrow, but I can do it really fast when I use Ctrl+ Shift+ arrow key combination.
Does anyone have any doubts about this? No? Let’s go ahead to tip 3.
TIP 3: KEYBOARD SHORCUTS IN EXCEL
What we have done so far is use keyboard shortcuts, both for selecting cells and moving fast in Excel. Let’s use some other keyboard combinations; this is the best way to be fast in excel and to ensure you are doing everything efficiently, you should make use of shortcuts as often as possible.
In the sheet you see here, we have some data, which should be organized in a table however, right now it isn’t. We need to use Excel shortcuts to do this very quickly. I have a few tasks to carry out. First of all, we need to add a filter. I am going to go to the ‘Online course’ cell and I will select the first row, which would be the header of the table.
I am going to press Ctrl+ Shift+ right arrow key, selecting the header of the table as fast as possible. If I simply press the Alt key, I will activate the ribbon. Then we can use a shortcut combination to add a filter.
I will go for Alt + A + T to add a filter.
However, I made a mistake. Sometimes, I make mistakes. Everybody does. When you have such blank cells, you have to select the entire table otherwise, the filter will show you the first row only, it will not show anything below. I am going to select the entire table now and press Alt+ A+ T, and we will add a filter for the entire table.
My next task will be to remove all blank rows; I am going to simply open the filter with the alt key.
Then I will simply use the Space key to filter the cells I would like to see – Blanks.
I will use another shortcut; Shift+ Space to select the 12th row,
and I will hold the Shift key and select the few blank rows.
Make sure you have selected all of them and then I will press ctrl+ – to delete all of the selected blank rows we have filtered.
To unfilter, I am going to select the entire filtered row and will press Alt+ A+ T and here, we have all non-blank cells.
Another task we have is to remove all blank columns. I am going to use Ctrl+ space to select the first column and use Ctrl+ – to delete it. Then I will repeat the same for columns E and G.
Now, we have a nice table however, we need to improve its layout according to task 4. Therefore, I am going to reproduce the formatting we have shown in our lessons. First, I am going to change the color. However, I am going to use Excel shortcuts, I am not going to do this with the mouse. I am going to press Alt + H for home menu, FC for font color and this is how we would change the font color.
I will like to bold it, and to do that I will press Ctrl+ B. Here it is. We have our table header in bold.
Let’s format the ‘Total’ row as well. I am going to select Format Cells because I would like my ”Total” borders to be coloured and that is a bit more difficult to do with shortcuts. Let’s do this a bit faster. First, you have to select the color and then you will select the type of border to be added.
Now, we can change the font color with shortcuts. Alt+ H + FC and I will select the same dark blue color again.
Ctrl+ B and it is bold, I think our table looks much nicer now.
Although I would have liked it better if both ‘Quantity’, ‘Price’, and ‘Total’ were aligned to the right. So, I am going to select this part of the table and I will press Alt+ H + AR, and everything will be aligned perfectly.
This is how you use keyboard shortcuts to format a table and delete rows and columns. An operation you need to do very often in Excel.
TIP 4 & 5; PASTE SPECIAL FORMULAS/PASTE SPECIAL FORMATS.
When do you have to use commands like Paste Special Formats, Paste Special Formulas, and Paste Special Values? Whenever you would like to paste your data and keep a specific type of format, or whenever you would like to keep the actual values only. Think of it like ‘selected pasting’. You choose what will be pasted – the values, the format, or perhaps the formulas. I am going to show you an actual application of this now.
Let’s go ahead and copy the table we have here. I will select the table, and will use Ctrl+ C to copy,
and then apply the shortcut combination for paste special – Alt+ E + S.
This is a special shortcut for paste special Alt+ E + S. Press the keyboard buttons one right after the other. And then to conclude, I will press F for formulas. (A+ E + S + F = Paste Special Formulas)
Here, we have pasted our table without any formatting, but we have kept all the formulas that were inside. Actually, there was only one formula inside. I have copied the entire table we have here without its formatting. Why? Because I prefer the formatting in the table that we have above. What I am going to do is copy the table above and then we paste its formatting Alt+ E + S + T.
I did tips 4 and 5 together. Tip 4 – paste special formulas; and tip 5 – paste special formats; I hope you are enjoying these tips, not sure if these are not basic for some of you, but they are really useful, and this is definitely where most users should start in order to become faster in Excel.
TIP 6: REPEAT THE SAME ACTION.
I have created 2 tasks here; we can use the F4 key to repeat the same action in Excel.
We have the ”Price” and ”Total” headings, which are aligned to the left. Of course, we can select both and align them to the right at the same time. However, to give you an example of how F4 functions, I am going to align Price to the right first – Alt+ H + A + R.
and I will go to ”Total” and will press F4, which will repeat the last action we have carried out.
F4 does exactly what you have carried out a second ago.
TIP 7: GROUPING SHEETS.
Grouping sheets can be useful in different situations. Of course, there are also other ways to carry out actions on multiple sheets. However, it is good for you to know how grouping sheet functions. We are in the sheet ‘Tip 7 – Sheet 1’. I can hold the Shift key and then select ”Tip 7 – Sheet 2”, which is the second sheet.
As you will see here, Excel would indicate us that we have grouped the sheets
I can easily move them around together. This can be time saving and useful whenever you have to carry out operations on multiple sheets.
Once we have done this operation, Excel removes the ‘Group’ sign we have at the top where the title of the file is, and this goes to indicate that the two sheets are not grouped anymore.
To group them again and move them to the left, I am going to hold the Shift key and simply click on the two sheets I would like to be grouped.
Another way to use the grouping function of Excel is to carry out the same action in both sheets at the same time. Whenever we want to do that, we can simply group the 2 sheets and write some texts, numbers and add some formatting and now, you have the same thing in both sheets.
If I go to a third sheet, I am automatically interrupting the grouping mode we were in, and the 2 sheets are independent once again. This is how grouping functions.
TIP 8: CONVERT (FUNCTION)
The CONVERT function is something very interesting for me and it is actually a time saver when you have to work with international measurements. All you have to do is type =CONVERT and then select the measure you would like to convert.
In this case, it will be 1.6, which is in meters (the height of a person) and select the unit from which you would like to convert it. In our case, it is meters, so I would type ‘m’. The second argument will be the measurement we would like to obtain (foot in this case).
I am going to close the formula, and I am going to convert 1.6 meters to 5 ft. This allows me to use this formula for the that entire row and make the conversion really fast.
If I was using Google converter or some other tool, it would have required so much more time. Let’s do the same for kilograms, I am going to be more efficient since our main topic today is Excel speed. I am going to copy the formula and simply press F2 (which would allow me to open and edit the formula directly), and will change kilogram to ‘lbm’, which stands for ‘pound mass’ and I will press Enter. Here we are; 112kg = 247 pounds. Let’s copy and paste downwards.
The last two columns contain data about the preferred temperature of these people. I am going to copy the function we created. Of course, it needs to be edited. However, please note that as soon as you open the formula and start typing, a list of possible measures will open up. I knew these 3 by heart and that is why I didn’t look at this list, so you can also look at the list and I can assure you that it is going to be useful. I will write ”C” and ”F” here and the conversion is completed into Fahrenheit. This is how the CONVERT function works.
TIP 9: DATEDIF (FUNCTION)
We have the following data: two ranges containing dates.
To calculate the difference between the dates in these two ranges, we could use the ‘DATEDIF’ function. It is a very interesting function. As you can see, when we type it in Excel nothing appears.
This is a function that was introduced back in 2000.
I actually haven’t shown this tip in the Beginner to Pro in Excel course on Udemy, and I hope it adds some value to you. DATEDIF is a function that very few people know about, it is in Excel, but can be used only by people who already know it is there. The way it works is that you first select the first argument and then the second argument to calculate the difference between the two dates. Then you will have to select the measurement in which you would like to calculate the difference.
The NUM error I received here means that Excel struggles to make a calculation because it knows we are calculating the difference between two dates, and it doesn’t want to display a negative value, it knows the difference should be positive.
If I go and substitute the first date in B4, and the second date in C4, this will work. This is how the DATEDIF function works. First, we have to insert the earlier date, and then we insert the second date, which is the later date and we write “d” which goes to indicate that we would like to calculate the difference between two dates in days. And here’s the result we obtain – 44.
Let’s calculate the difference in months; I will substitute ”d” for ”m” and we obtain the difference is 1 month. That is exact because the difference between the 25th of June and the 12th of May is 1 month.
Anytime you have to calculate the difference between dates, DATEDIF is a nice function to apply.
TIP 10: FIX CELL REFERENCES PROPERLY
To be fast in excel, you have to fix cell references properly and that is what we still haven’t discussed how to do.
I would really appreciate it, if you join our next Facebook live session when we will discuss fixing cell references properly. I am really excited that we got do this today, I have been wanting to meet you and interact with you for a long time now. I hope these meetings will become a regular event, and we will organize them every week. I am here for you, I would like to answer any questions you might have. Feel free to comment and thank you so much for joining today’s session!
As I said earlier, you could easily learn when 365 Careers’ next Facebook live session will be by opening our website 365careers.com, clicking on live sessions, and simply checking out the date of the next session along with the topic we will discuss.
Thanks so much everyone and see you in our next Facebook live session!