Top 10 Quick Excel Tips (Part 2)
10 Most Useful & Simple Microsoft Excel Tips [Part 2]
Welcome to the second part of Microsoft Excel tricks & tips. In case you've missed out the first part, we're putting the link here for you to catch up.

Needless to say, every office worker must've known that Microsoft Excel is definitely a powerful and helpful tool in your daily work, regardless of what industry you're in.
Now, let's test out how proficient you are in Microsoft Excel.
Get yourself ready and...It's quiz time! (Yes = 1 point , No = 0 point)
Do you know how to…
  1. Add headers at the top or add footers at the bottom of a printed worksheet in Excel?
  2. Enter today’s date without type out manually?
  3. Split up information that's in one cell into two different cells?
  4. Convert a date (or a number) into a text string?
  5. Change the words from upper case to lower case?
  6. Do multiplication in Excel?
  7. Get average value in a range of cells?
  8. Combine the contents of two or more cells into a single cell?
  9. Move your row data into columns without copy and paste?
  10. Narrow down the data in your worksheet and hide parts of it from view?
So, how’s your result? If you're able to score more than 9 out of 10 in this quiz, then you can skip the following tips I'm going to share later. However, it’s okay if you messed up the quiz because I’ve prepared 10 answers for the quiz below.
1. Header & Footer
Purpose: The content of the header and footer repeats at the top and bottom of each printed page. This is useful for showcasing info, such file name, date and time.
How:
  1. Click the worksheet where you want to add headers or footers.
  2. Method 1: On the ‘Insert’ tab -> ‘Text’ group -> click ‘Header & Footer’ option.
    3. Method 2: On the ‘View’ tab -> click ‘Page Layout’ option.
  3. Type the headers or footers text in the Left, Center or Right text box, whichever you prefer.
  4. Simply click anywhere in the worksheet to close the headers or footers. Press ‘Esc’ to close headers or footers without keeping the changes that you made.
Method 1:
Image Slider
Method 2:
Image Slider
2. Add the current date to a cell
Purpose: There are many situations where you may need to enter the current date in a cell. Although you can type it manually, this can become tedious if you have to enter the date frequently.
How: Simply hold down the ‘Ctrl’ key and then press the semicolon (;) key, and it will instantly add the current date.
3. Split up information that's in one cell into two different cells
Purpose: Split a single column of text into multiple columns. For instance, you can separate a column of full names into separate first and last name columns or you can separate a column of addresses into state and city columns as well.
How:
  1. There are two ways that Microsoft Excel can split up the information from a single cell into multiple cells:
    • • By choosing ‘Delimited’ option, it means you want to break up the column based on characters such as commas, spaces, or tabs.
    • • By choosing ‘Fixed Width’ option, it means you want to select the exact location on all the columns that you want the split to occur.
  2. Select the cell/column you want to split. Select ‘Data’ tab -> click ‘Text to Columns’ under ‘Data Tools’ group.
  3. Select ‘Delimited’ & click ‘Next’ -> Select how your data is going to split. (In the example below, I will choose ‘Space’ for my case) & click ‘Next’.
  4. MS Excel will then show you a preview of what your new columns will look like. Once you satisfied with the preview, simply click ‘Finish’.
Image Slider
Image Slider
Image Slider
End Result:
Image Slider
4. TEXT Function
Purpose: TEXT function is a useful tool that helps convert a date (or number) into a text string in a particular format. It is handy when users need to view numeric data in a readable format. Do note that the ‘TEXT’ function only works to convert numeric values to text. Hence, the results are not calculable.
How:
  • • Syntax is "=TEXT (value, “format_text”)"
  • • ‘Value’ means the particular number you wish to convert to text.
  • • ‘Format_text’ means the format code you wish to apply.
Image Slider
5. Change the words from upper case to lower case
Purpose: By using the ‘UPPER’, ‘LOWER’ or ‘PROPER’ functions to automatically change the case of existing text to uppercase, lowercase or proper case.
How:
  • • The example below is change the name from all uppercase into proper case, which capitalizes only the first letter in each name.
  • • First, insert a temporary column next to the column that contains the text you want to convert. In this case, I’ve added a new column (B) to the right of the ‘Name’ column.
  • • In cell B2, type ‘=PROPER(A2)’, then press Enter. Therefore, this formula converts the name in cell A2 from uppercase to proper case. To convert all the text to lowercase, type ‘=LOWER(A2)’ instead.
Image Slider
6. Multiplication
To make the simplest multiplication formula in MS Excel, type the equals sign (=) in a cell, then type the first number or select the first cell you want to multiply, followed by an asterisk symbol (*), and hit the ENTER key to calculate the formula.
For example, to multiply 3 by 5, you type this formula in a cell: =3*5 in order to get the result. To multiply cells with the number 100 in column A3 and the number 60 in column B3, you type this formula in a cell: =A3*B3 in order to get the result.
Image Slider
7. AVERAGE Function
Purpose: The average function is a useful tool for getting the average value in a range of cells. Basically, the average function works to find the “arithmetic mean” for a group of cells.
How:
  • • If you want the average value of a set of numbers or data, you can use the formula =AVERAGE (Cell Range) as shown in the example below.
Image Slider
8. CONCATENATE Function
Purpose: This function is a good time saver when you need to combine data from 2 or more cells into one cell. Unlike the merge tool which physically merges two or more cells into a single cell, the concatenate function only combines the contents of the combined cells.
How:
  • • Syntax is “CONCATENATE(text1,text2,text_n)”
  • • “Text” means the data which you want to combine.
  • • As example below, by typing the formula =CONCATENATE (“Item No:”,” ”,C2,” ”,D2), all the data from 3 cells separately now become combined data within a single cell.
Image Slider
9. Transpose
Purpose: You can move your row data into columns without copy and paste repetitively and vice versa.
How:
  1. Start by highlighting the rows that you want to transpose into columns.
  2. Right-click it, and then select "Copy."
  3. Next, select the cells on your workbook where you want your first row or column to begin. Right-click on the cell, and then select "Paste Special."
  4. A module will appear at the bottom, you'll see an option to transpose. Check that box and select OK. Your row will now be transferred to a column or vice-versa.
Image Slider
10. Filter
Purpose: Filters allow you to narrow down the data in your worksheet and hide parts of it from view. In MS Excel, a filter can be added to each column in your data - and from there, you can then choose which cells you want to view at once.
How:
  1. Highlight the column which you wish to filter.
  2. Select ‘Sort & Filter’ under ‘Editing’ group -> choose ‘Filter’ option.
  3. By clicking the drop-down arrow next to the column headers and you'll be able to choose whether you want your data to be organized in ascending or descending order, as well as which specific rows you want to show.
  4. Only the data which you’ve selected will be shown once you clicked ‘OK’.
Image Slider
Image Slider