Apr 21

If you are a beginner to Excel, you must be overwhelmed by Excel's extensive features.

Fret not.

Simply learn these 10 simple hacks tailored especially to Excel for Beginners.

Excited? Let's start with the most frequently asked question by beginners...

Excel for Beginners: 10 Basic Tips Everyone Must Know

More...

Excel for Beginners:

10 BASIC TIPS EVERYONE MUST KNOW

Answers to the 10 most frequently asked questions by Excel beginners

Infographics - excel for beginners

Tip #1: Adding a New Line in Cell

This is the number one question that beginners often ask – how to add new lines in a cell.

In other programs, you simple hit Enter. But in Microsoft Excel, Enter brings you to the cell below.

How do you do something like this:​

How to add new lines

Here's the secret:

  • Step 1: Type your first line of text.
  • Step 2: Holding down the ALT key, press {enter}. Ta-da you got it!
  • Step 3 (Optional): If the text is all joined together without spaces, such as "Howtoaddnewlines", then click on that cell, go to Home | Alignment | Wrap Text
Wrap Text

Tip #2: How to Merge Cells in Excel

You have columns for each month. Jan, Feb, Mar, ..., Nov, Dec. But you want to show the year across the months above, like this:

merge cells
  • Step 1: Select all the cells that you want to combine into one big cell.
  • Step 2: Click Home | Alignment | Merge & Center. Wow, you're done!
merge cells 2

Although I think it's ok to use the Merge & Center feature for an Excel beginner, there are some issues especially if you want to sort and filter the selection as Merge & Center alters the structure of the cells around it. A better alternative is to use the Center Across Selection feature. 


Tip #3: Inserting Bullet Points

Give me some bullets!

Nope, not the kind of bullets in the picture.

So where are the bullet points in Excel?

Unfortunately Excel does not have a button for bulleted list.​

Let me show you 3 methods of adding bullet points in Excel.​

Shooting bullets
  • Method 1 (if you have the numeric keypad): hold ALT key and press 7.
  • Method 2: Copy the bullet point from somewhere else e.g. Word, Powerpoint or Google. Press CTRL + V to paste. 
  • Method 3: Go to Insert | Symbols | Symbol. Select General Punctuation under Subset. Find the bullet point and double click on it to insert. If you have recently used it, you can also find it in the Recently used symbols too. 
Symbol - bullet point

 You can also combine with tip #1 to create a list of bullet points in a single cell.


Tip #4: Remove Duplicates to Have an Unique List of Values

Duplicates are really painful in a list sometimes. 

You manually scroll down the list one by one, trying to see if there are any duplicates to remove

Your eyes are tired and your vision starts to get blurry. 

There has got to be a more efficient way right?

(hint: it is only one click away.)​

Remove Duplicates
  • Step 1: Select all the data that you want to clean up.
  • Step 2: Click on Data | Data Tools | Remove Duplicates. That's it! Easy peasy.

Sometimes instead of removing duplicates, we may just want to highlight the duplicates. For an example of how to find and remove duplicates, click here.


Tip #5: How to Insert Leading Zeroes in Excel

If you ever tried to key in numbers starting with 0 like employee numbers or postal codes, you will realize that Excel the Smarty Pants always drops the leading zeroes.  

Similarly if you try to key in international phone numbers with +, Excel drops the plus sign too.

But today, you will learn how to get Excel "just do it".

zero
  • In the cell that you want, enter ' (apostrophe or some call it single quote) and start typing.
  • Notice that the contents are aligned left as text. When Excel is not using its brain, anything that we type is considered a text.

Tip #6: Simplest Way to Copy from Microsoft Excel to Word or Outlook

Often we are tasked to create reports in Word or email some information in Outlook.

We copy over tables of information from Excel to Word or Outlook.

We copy charts from Excel to Word or Outlook.

And we spend a long time trying to get the layout in Word and Outlook to look the same as Excel. 

On top of that, some idiots may accidentally delete some of the data.

The simplest solution I have for beginners is to just make it into a picture.

  • Step 1: Copy your table, chart or cells in Excel with CTRL + C.
  • Step 2: In Microsoft Word or Outlook, right click and select Picture under Paste Options.
Paste as Picture

Ta-da! Fast and nice-looking results.


Tip #7: Look Professional with External and Internal Hyperlinks

It looks so much more professional if you insert hyperlinks into your Excel spreadsheet.

Do you want your bosses to click different worksheet tabs and navigating here and there​? Getting bosses to play treasure hunting in Excel is never a good idea.

How about having a Table of Contents to take directly to where they want within the worksheet. Or even open up the websites you want them to see. 

Instant teleportation!

link
  • First up, external website links. Type any text in a cell and press {enter}. Select the cell and press CTRL + K. Enter the url into the Address bar. 
hyperlinks - websites
  • Next, for internal links within the same workbook. Type in a text to link and press CTRL + K. Select Link to: Place in This Document. Then click on the worksheet to link to (e.g. Sheet1). Specify which cell to highlight in the selected sheet. Press OK.
hyperlinks - workbook

Now watch your bosses and colleagues' jaws drop as they jump through different dimensions. Portus!

For a better understanding of hyperlinks, read this detailed hyperlink guide.

If you are an advanced user who knows how to create a VBA macro, check out the VBA code for an Automatic Table of Contents.


Tip #8: Add Dynamic Excel Header and Footer to Show Page Numbers

During a meeting, someone says, "As you can see from this chart..."

Nope, we don't see. Why?

We have no idea which document or page he is talking about. 

Adding Excel header and footer is easy, let me show you.

  • Step 1: From the status bar, go to Page Layout view (2nd button).
header footer - page layout
  • Step 2: To add a header, scroll to the top of the page. Click on the area for a left, center or right header. You can add in static text as in the example below. Once you are done, click outside of the header area to confirm the new header. Do the same for footer.
header footer - header area
  • Step 3 (Optional): To add page numbers, first select a footer area (I used a right footer here). Click on {Header & Footer Tools} Design | Header & Footer Elements | Page Number. Type {space}of{space} in the footer. Then select Number of Pages from the menu. Once you confirm the footer, it will show something like "1 of 14" depending on the number of pages.
header footer - page number
  • Step 4 (Optional): You may also find it useful to insert a print date and sheet name if you have many versions of the workbook or many worksheets within the workbook. Select a footer area. Click on {Header & Footer Tools} Design | Header & Footer Elements | Sheet Name. Press {enter} to go to the next line. Select Current Date to insert the date of printout. 
header footer - sheet name

Tip #9: How to Print Perfectly into a Single Page

We are almost at the end of this basic tutorial.

Everything's done right?

We hit print, only to realize that it has been printed on several pieces of paper. The papers need to be taped together in order to make sense of the printout. Ugly isn't it?

Now, here's how to print everything nicely into a single page:

print
  • Step 1: Go to File | Print.
  • Step 2: Decide if you want portrait or landscape printout. Click on Portrait Orientation to change to Landscape Orientation.
  • Step 3: Click on No Scaling.
Print Options
  • Step 4: Select Fit Sheet on One Page.
Printing - Scaling options 2
  • Step 5 (optional): If there is too much white space in the right margin of the paper (you can see from the print preview), then you can print it as multiple pages instead. Choose Fit All Columns on One Page
  • Step 6: Enjoy your perfect looking printout!

Tip #10: Protect Your Worksheet by Converting Excel to PDF

You put in a lot of time and effort using tips #1 to #9 to create an awesome spreadsheet in a shared drive. The same idiots in tip #6 meddled with the spreadsheet and accidentally deleted the data away. You now got to spend a whole day trying to recreate the spreadsheet.

" Prevention is better than cure" – Desiderius Erasmus, Dutch Philosopher 1466-1536

Converting Excel to PDF idiot-proofs your spreadsheet since they can't do anything at all – except view.

pdf

​Here's how to export Excel as a PDF:

  • Step 1: Holding down the CTRL key, select the worksheets that you want to convert to PDF. If you want the all the worksheets, skip this step. 
  • Step 2: Go to File | Export
  • Step 3: Click Create PDF / XPS
Export to PDF
  • Step 4: Choose where you want to save and hit Publish.
Export to PDF - publish
  • Step 5 (optional): If you wanted to export all the worksheets in the workbook, go to Options...
Export to PDF - options
  • Step 6 (optional): Select Entire workbook under Publish what, and hit OK
Export to PDF - entire workbook
  • Step 7: Send your users the PDF version and laugh out loud, knowing that they can't do anything to the PDF. 

If you really have to send an Excel workbook to others, you can lock down certain cell elements such as hiding formulas or preventing users from selecting certain cells. See this Microsoft article for more information. 


Now that you have mastered the 10 basic tips to Excel for Beginners, I would like you to do 2 things:

  1. Share this article with other Excel beginners – your family, friends and colleagues, so that they can learn these 10 simple tips too. They will thank you for it!
  2.  Leave a comment below of what other Excel monsters you are facing and need help with.