Excel Tips for Basic and Intermediate Level Users

Share your talents and skills and get PAID. Join now!

Hi there! I'm back for quite some time of being away from article writing. I will not explain more what Basic and Intermediate Microsoft Excel users are. Microsoft Excel is a powerful tool widely used because of its capability to perform mathematical equations, functions, and computations accurately and organize, format and calculate data with formulas. What most basic users knew about the software like understanding what are rows and columns, applying auto summing up of figures and classifying textual and numeric data already gave them an edge over the other co-workers. They can already claim that they use Microsoft Excel appreciably well.

I had been using Microsoft Excel since 1994 and had been learning more until the present. I do not claim to be an expert in this software but I know more of it than any basic and intermediate users do. I used Microsoft Excel when doing statistical data reports and analysis. I integrate Microsoft Access tables with Microsoft Excel spreadsheets and vice versa. What I like most with Microsoft Excel is its capability to perform complex formulas without affecting its performance.

Here are my chosen tips that will be very useful for every Microsoft Excel users.

Select All

Basically, we select all data by pressing Ctrl + A buttons. I found out that others are still using the mouse which is the primitive way which is a tiresome and slower process if you have many rows and columns to copy. The fastest way to select all data is by clicking the top-left corner button as shown in the image above enclosed in a red square.

Menu Shortcut Keys

Most of the time we use a mouse to navigate menus and toolbars. Our efficiency and productivity are affected by our ability to use a mouse. Often times the mouse is very hard to navigate especially if you do not have a mouse pad, that creates delays in finishing the task given to us. Every menu and toolbar has its corresponding shortcut key. It is just a matter of continuously using the shortcut keys to be more efficient and productive.

The technique to activate the shortcut keys is to press the Alt key without pressing any other keys. You may remain pressing the Alt key or simply press it and release. You will find that all menus and toolbars have its corresponding shortcut keys represented by letters and numbers as shown in the image above.

Try sorting data using your mouse and see the difference if you use the shortcut keys instead.

Steps to do when navigating the data using the mouse:

1. Highlight the cells you want to be sorted
2. Click Data, Sort
3. Select the Column(s) to be sorted, Sort On (default by Values), and Order (default A to Z)
4. Click OK button

Here are the shortcut keys for sorting the data:

1. Highlight the cells you want to be sorted

2. Press Alt, A, S
3. Select the Column(s) to be sorted, Sort On (default by Values), and Order (default A to Z)
4. Click OK button

Your speed of highlighting the cells depend on the volume of data. The more columns and rows you have, the harder for you to highlight the data because what you know on how to highlight cells is you simply click the first cell of the top record and drag the mouse down and right to include all the data. Am I right?

Try it using the sample data that I got from Wisdom Axis website. The original copy has 1007 rows and 10 columns. That's a tough one to sort with. In our example, I will retain the first 100 rows. Click here to download the file. Normally, downloaded files are located in your Download folder.

How long did it take you to highlight the records? I tried it myself highlighting the records using a mouse and it took me 7.35 seconds. The speed will depend on how big your records are. I employ a technique by using keyboard keys and it only took me 1.91 sec to highlight all records. The same speed no matter how many records you have. How did I do that?

Here's the technique. Click the first cell of the first record (Row A2). Hold Shift and press End + Down Arrow + End + Righ Arrow keys. Try it now!

Combine the technique that I use and the shortcut keys in sorting the records you will find that it will take you a little amount of time to accomplish the task. Using the mouse navigating volume of records is very tiring and boring one.

Text to Columns

You can be able to separate the contents of a single cell into separate columns by using Text to Columns toolbar found in the Data Tools Tab.

Let us say that you are working with your SSS R3 Project. By default, the reports are saved as text files and you can be able to open it using NotePad or WordPad. Your boss wants you to convert the employee list file into a Microsoft Excel file. You have no other options to convert the file but to open it in either NotePad or WordPad and copy the list and paste it to Microsoft Excel.

Download the sample data here.

Follow these steps:

1. Open the file (EMPLOYEE_LIST)
2. Press Ctrl + A to select all
3. Press Ctrl + C to copy
4. Open your Microsoft Excel
5. Press Ctrl + V to paste the copied list

You will notice that the list of employees is not formatted by columns. To fix the output, you will use the Text to Column button.

How to fix it:

1. In your excel file, click the first employee record (Row A6)

2. Hold Shift and press End + Down Arrow keys
3. Click Text to Column
4. Click Finish button

You will now notice that the columns are now arranged properly. Experiment with the other options in the Text to Column converter wizard.

Transpose Data from a Row to a Column and vice-versa

You are given a list of data in a tabular format. A tabular format is displayed as one record per row. You are required to display one record for every column, which is the opposite of a tabular format. Retyping all the data would be strenuous for you and would take you some time to accomplish the task. What would you do to shorten the time to prepare the required report?

The above image showed the tabular format report in the left portion. On the right portion, the data is transposed into columnar format wherein the records are displayed in columns rather than rows.

Please download the file in tabular format here.

Here are the steps:

1. Open your downloaded tabular format file

2. Highlight all records (A1:D6)
3. Press Ctrl + C to copy and place your cursor position to column G1
4. Click Home, Paste and Transpose (shortcut key Alt, H, V, T)
5. Click Align Text Left button found in the Alignment toolbar

The result would be similar in the image above. Experiment by converting the columnar format into a tabular format using steps 2 - 5.

Inserting Sequential Numbers in the Records

File to use: Sample-1.xlsx

There are more ways to insert a sequential number in the records. The basic way is to manually type the numbers in every record which is the primitive way. This is only good if you have small records like say 10 records. The alternative to this is by typing the record number of the first two records. That would be 1 and 2. Highlight cells A2 and A3. You will notice a small black rectangle at the right-bottom portion of the highlighted cells. Point your mouse to that and your mouse pointer will be changed into a black thin crosshair. (Refer to the image below)

Drag the mouse downwards until to the last record and release the mouse. Still, I do not recommend this process if you are having bulk records. It would take you some time to drag the mouse if you have 1000 records or more. What a waste of time and an inefficient way of inserting sequential numbers.

Here is the best and fastest way to insert sequential numbers regardless of the volume of records.

Follow these steps:

1. Open the file. Remember we have 100 records to insert sequential number.

2. Insert a blank column in the first column.
3. Type "No." at the cell A1 center aligned. Set the font into bold and put a bottom border.
4. Type "1" at cell A2 excluding the quotation marks.
5. Place your cursor at cell A2.
6. Hold Shift and press the PageDown button until you reached the last record.
7. Click Home, Fill, Series, and type 100 at the Stop value text box. Even if you typed more than 100, still the last record would be 100.

Try that process and you will find it more convenient than the previous process that I explained here.

However, if you have more than a thousand records, you will find pressing the PageDown key so tiresome. Here's what I do to make it easier and quicker:

1. Follow the above steps 1 - 4

2. Place your cursor at cell B2
3. Press End and Down arrow keys
4. Move your cursor to the left
5. Type any figure and remember the row number found at the left
6. Type Ctrl + Home to return to the top
7. Place your cursor at cell A2
8. Hold the Shift key and press End and Down arrow keys
9. Press Alt, H, F, I, S keys one after the other
10.Type 1 in the Step value text field
11.Type the row number in the Stop value text field (do not worry if the row number is greater than the total number of records)

Combining the cell contents

Sometimes you have a pre-formatted Excel file. You are required to combine some cells. We will use the same file similar to the tabular_format.xlsx that we used earlier but this time the full names are separated by the last, the first, and the middle initial. Download the file here.

Steps to do:

1. Open the file

2. Place your cursor at cell D1
3. Insert a column
4. Place your cursor at cell D2 (the new inserted column)
5. Type '=CONCATENATE(A2,", ",B2," ",C2)' exclude the single quotes
6. Copy cell D2 and paste its content until D6

The Concatenate function combines the contents of the cells into one cell. You need to separate the cells or any character or words you want to include with a comma. In our example, we place a comma sign with space after cell A2 and space between cells B2 and C2 to have the text formatted. Ignoring the comma and spaces will combine your cell content unformatted.

The other way to combine cells contents is by using the ampersand sign (&). In our example, replace step number 5 with this: Type '=A2 & ", " & B2 & " " & C2' exclude the single quotes. The result is the same. Use either of the techniques to combine cell contents which you think is best and faster for you.

Note: Please do not confuse concatenate with merge cells. The later function gives a different result. Refer to the image below for the merge function.

So that is all guys. Hope you learn from these very simple tips that I shared in order for you to be more efficient and productive in your workplace.

I would love hearing from you by providing comments below.