Free Excel Beginner’s Guide: Introduction to Excel

Loved it? Pin it!

Three core functions of Excel

Excel is a necessity of every office now. And since its first version we all have reasons to love it more every year and for the same reasons almost everyone (engineers, managers, medical reps, mathematicians, accountants and the list is really long) needs to be acquainted with Excel.

However, Excel’s interface isn’t as simple as other applications like Word or Powerpoint and that causes a lot to repel and not to open this otherworldly software again. The interface with things called cells that are extended far beyond to the right and endless bottom, daunting creatures named formula and then charts are just too much to comprehend.

But this hesitation is there only until you can get a grip of basic Excel knowledge and its functionality and we tend to find Excel overwhelming because we tend to look at everything it offers and does.

For me Excel help you do THREE main functions:

Record: maintaining the records or data or information in one ‘box’ (technically called cell). With thousands of cells at your disposal, each cell is a definitive location to store your data. Data can be in the form of text, number, symbols or a mix of these. Understanding the very purpose of cell in Excel and why they are arranged in particular fashion and how this arrangement help us achieve awesome results is a key to excel at Excel.
Compute: having the data or information recorded what good is it doing if you can’t use it to deduce, analyse and compute certain aspects. The simplest example one can think of is to perform mathematical operations like addition, subtraction, multiplication and division. We as a human are intelligent enough to make computations keeping information in our memory alone. For example if you have to add the price of two products you can do that easily with little brain activity. But what if you have to compute how inflation has affected prices of household products? Well I am not sure about you… but I am sure about myself that my brain cells can’t hold this much stress and I will resort to Excel. This is where Excel’s inbuilt tools especially formulas proves great help.
Summarize: The ones making decisions don’t have time or nerves to first memorize records and then compute and then make decisions. If we only record and compute then managers and decision makers will find it hard to consume. Information has to be in the form that makes decision process efficient. Excel’s tools like conditional formatting, tables, pivot tables, charts help prepare reports that can be simple comparison charts or awesome dashboard reports.

Understanding Excel Interface

Excel’s user interface is much like any other application in Office suite. When you run Excel and program opens up there is a lot to see and a lot to know right on the first screen. To accelerate the process consult the following labelled diagram that tells about almost everything you see on screen for the first time:

Excel Interface labeled diagram

excel interface right size

Excel tools and features

Following table describes what each item is and very briefly its function:

Item/Feature	Description
Address bar/Name box	This shows the address of currently active cell with reference to row and column
Program icon	A program icon. On left click a usual application menu will be displayed with minimize, maximize, restore, close etc options.
File menu button	This button acts like a file button in many other windows application and gives options to save file, access to document information, excel options, print options etc.
Quick Access toolbar	As the name suggests holds icons to functions that are often used and this bar make them readily available to be accessed quickly. It can be customized to suit users’ needs.
Active tab	The tab which is currently in front and the contents under this tab are displayed and other tabs are hidden. By default home tab is an active tab. But clicking other tabs name will make them active which gives access to different options. The contents of active tab are shown as Ribbon
Tabs	These are the tabs in which different functions are organized with relevant functions for easy access.
Functions button	Pressing this button opens up dialogue box where you can select different formulas available in excel. Frequently used formulas are listed but user can also search for formula easily
File name	This is the name of excel file that user has input while saving it. By default excel files are named as Book 1, Book 2 etc
Formula bar	If user has inserted a formula in a cell then it will be shown in this bar or field.
Group	A subdivision inside ribbon where icons of relevant features having similar purpose are placed together for easy access.
Ribbon minimize button	This minimizes the excel ribbon upwards increasing the workspace for user. Shortcut to minimize excel ribbon Ctrl+F1 does the exact same function
Application minimize button	This minimizes the excel window/application to windows taskbar
Excel help button	Clicking this button will open excel’s interactive help feature where user can search for different topics in which help is required.
Application maximize button	This button will maximize the excel window/application to whole screen.
Excel window minimize button	This button will minimize only the excel workbook/window inside excel without minimizing excel’s main application window
Excel window maximize button	This will stretch the specific excel workbook/window completely inside excel
Application close button	This will shut down the excel program including all the opened workbooks and windows
Excel window close button	This will only close the relevant workbook or window without exiting excel application
Select all button	Clicking this button will select all the cells in the worksheet.
Active row/row address	The numbers at the left of worksheet are row numbers. The active row is highlighted with changed colour which is golden that helps easy identification
Sheet tabs scroll button	If you have many sheets open in the workbook then due to limited space only few worksheets’ tabs will be visible. These buttons scroll through worksheets tabs.4will scroll to next tab whereas : button will scroll to last worksheet tab. 3will scroll to previous 9 will scroll to first worksheet tab
Macro recorder status	Macro is one function in excel. This icon indicates the status of macro activity.
Active worksheet	The worksheet which is on screen at the moment. The active one appears brighter than others.
Sheets tabs	Group of worksheet tabs that a workbook holds.
Status bar	This place is used to display information which is most of the time of quantitative nature concerning the selected cells. And if the options are selected this bar also provide information whether num lock, scroll lock, CAPS lock is active. As the name suggests this displays the status of different important features.
Insert worksheet button	Clicking this button will add one more worksheet to workbook.
Active cell	Excel’s worksheets are divided into cells. Each cell is an entity that can hold its own information or from other sources. The cell that is active has black border or enclosed in a box with a small extension at the bottom right corner called handler.
Column	Vertical collection of cells is called column and is each column has its own name in terms of alphabets which serves as its address.
Row	Horizontal collection of cells is called row and is designated with specific number called row number.
Horizontal scroll bar resizer	Dragging it will expand or reduce the size of horizontal scroll bar.
Workbook view options	Three viewing options from which user can select and switch to another view on the go by clicking each option. Options are Normal, Page layout and Page break preview.
Zoom level	In percentage this provides information how big the data is being displayed in relation to its real size. 100% is default value
Zoom out button	This decreases the size of the worksheet and its contents
Horizontal scroll bar	This bar helps scroll the worksheet right or left.
Zoom handler	Dragging this handle will zoom in or out the worksheet
Vertical scroll bar	This bar helps scrolling the worksheet up or down
Zoom in button	This increases the size of worksheet and its contents
Vertical split bar	This splits the workspace into two quadrants vertically
Application window resizer	If excel application is not maximized than this option is available at the bottom right corner that help resize the window according to user’s needs
Horizontal split bar	Dragging this will split the workspace in two horizontal quadrants one over the other
Formula bar expander	This expands and collapses the formula bar that helps in reading long formula

And this is just a start. There are other tabs and each tab has few groups and each group has many different buttons to know and explore. And by the way there are many things that are not even on the ribbon. And secretly there is one tab that is turned off by default. In short, Excel has a lot to offer and its just up to you how much you benefit from it.

Important: Some menu items have three dots after them. They are there to signify that these commands will invoke a dialogue box.

Working with Excel – Basics

As an Excel user I will be constantly making use of two areas most of the time:

Ribbon consisting of tabs
Work area consisting of cells

Cells, Columns and Rows

One of main feature you immediately notice is that you have Alphabets at the top and numbers on the left. Alphabets are simply names of columns and numbers are simply names of rows. With column and rows named in this fashion help us name each and every cell inside work area. Having named a thing makes easier identification. This is exactly we are achieving in Excel. For example if I am asked to move to cell B2 I Exactly know which cell it is. In Excel world it is known as Cell Address.

Important: When naming the cell Column reference is given before Row reference. For example A1, E14, HS76 etc.

Navigating from one cell to another

It is as easy as with other things. You can use pointer keys to move the active area and to jump from one cell to another.

Entering the record

To enter a record you can type just like normal and on pressing Enter key on the keyboard will confirm the record or data to be inserted in that particular cell.

Editing the existing record

If you need to make a change in Existing record you may have it as active cell and start typing but it will completely overwrite or replace the existing content. However, if you don’t want to overwrite but to make a change then you have to enter the ‘Edit mode’ first by pressing F2 key. Now you can make the change.

Fetching contents of another cell

Knowing that each cell has address we can fetch the data from another cell without retyping again. For example you have some information in cell A1 and you want it in cell A5 as well. Then you simply need to press “=” sign and give the cell address which is A1 and press Enter. Job done!

If you have to fetch data from multiple cells in a row or column? Then you can make use of Fill handle to quickly do it by dragging it right, left, up or down. Once you have a cell reference in one cell and you drag the fill handle Excel populate the next cell automatically with appropriate cell reference or cell address.

For example you have data in Column A starting in cell A1 and you want to duplicate that in column B. You just need to refer to first cell and drag the fill handle down. And if you check the reference in each cell later you will find that Excel has automatically incremented the row number as you dragged down. Same is the case with columns if you move from left or right.

But what if you want the references not to change automatically? There will be occasions when you won’t be happy with this automation and that is where one must learn how to use Absolute, Mixed and Relative cell references.

Remember your cell address is actually a combination of Column and Row address.

When you use simple row:column addresses you have to option to make the references absolute or relative and this enables you to reference in four different ways for example if you have to refer to cell A1 you can refer in the following ways:

$A$1 – this makes both rows and column static

$A1 – this makes the column static, however, relieves the row and it will change when the contents of this cell are dragged downwards or upwards

A$1- this makes the row static, however, relieves the column from being static and it will change when the contents of this cell are dragged leftwards or rightwards

A1 – this puts no restriction at all on any part of reference and rows and columns will change if they are dragged using a fill handle.
Remember: You can put $ sign by typing or by selecting the cell address and repeatedly pressing the F4 key on the keyboard as Excel cycles through reference styles.

Another fact to remember is that you can perform a certain task with a simple key press or combination of keys on the keyboard. It is called shortcut. For example F2 and F4 are two examples we just discussed above. Similarly pressing Ctrl + S to save the file is also a shortcut.

Tutorials on Excel Working

Top 10 Excel keyboard shortcuts for Accountants
40+ Excel navigational shortcuts to make accounting job super efficient
Excel ALTernate ‘Powercut’ – One key to rule ALL shortcut keys
Microsoft Excel Keyboard Shortcuts – Mega Collection
Excel Mouse Mayhem – 35+ Mouse tricks for mouse mongers

Formatting and handling Data

Excel isn’t just another Notepad when it comes to styling or precisely formatting the data. It has all the basic formatting tools you find in word processor. However, Excel advances in formatting zone with such features that are not available with other applications in Office suite. And most importantly some can do much more than just changing the appearance of data.

Things to learn in home tab

Clipboard: With few ordinary ones and couple of extraordinary ones. Cut, copy paste is not new to us I assume and they perform the same basic function just like other applications. However if you click the drop-down arrow buttons then you will find few extra bits worthy of learning.

Copy as picture: this will copy the data as a snapshot of that place which you can literally size and edit just like any other picture.

Paste special: this command can do much more than simple paste like pasting just the values or transpose the content.

Format painter: If you have a cell that is formatted to desire and you want the same formatting to be applied on another cell then simply turn the it on and click over the target cell and it will apply all the format settings in an instant.

Tutorials Specific to Home Tab

10+ Excel features in Home tab that every beginner MUST learn
30+ Copy & Paste Tricks You Must Know – Mouse Keyboard Autofill Paste Special and More!

Font: In this group you can choose to change font family, styling of text, size, apply or change borders and change color of cell and font.

Alignment: In this group you have commands that let you change the text or content alignment in cell. You can also change the orientation i.e. you can have your text at a certain degree of angle instead of simple horizontal.

Wrap text: As you can change the width of columns at your will, you may have a text that is longer than the width. Wrap text, as the name suggest, will wrap the text by inserting line breaks so that your content can fit in the same width.

Merge & Center: It will merge multiple cells in one and make the content center aligned.

Number: One of my favourite group to work. No analysis will ever be complete without numbers. Excel gives us ample ways to format numbers as well. You can format numbers in the form of currency, telephone number, zip-code, date, percentage and above that ability to make your own formatting style.

Tutorials on Number Formatting

Dealing with Telephone numbers in Excel – Dialing in the correct format
Using Excel Custom Number Formatting as Data Validation Tool
Formatting Dates in Excel the better way using Custom Number Formatting – How To

Styles: This group has options that help you give meaning to your data with style.

Conditional formatting: help you style content in a particular fashion if they fulfill a certain condition. Its a powerful tool and one can employ it to great extent.

Format as table: turn the range in a table. I will discuss about tables and their abilities in more details later. For now check out following tutorials to make yourself aware of the amazing things they can pull off:

Cell styles: ready made styles available for you to be applied on a click of a button and saving you time.

Tutorials on Tables/Conditional Formatting

20+ Excel Table tricks to turbo charge your data
Absolute or Static structured references in Excel table – How to
Comparing two columns to highlight Duplicates in the same row – Excel Conditional Formatting

Cells: want to add more rows or columns before or after your active cell? This is the group you must access. You have options to insert/delete rows, columns and even worksheets. You can even hide/unhide a worksheet, row and column. And not to forget you can change the color of worksheet tab and even lock the worksheet to prohibit editing or even opening.

Editing: Last group in home tab provides you with basic editing functions like:

Find and select: find the desired result AND select it if you desire.

Sort and filter: arrange the data in descending or ascending order

Clear: clear all the applied format settings or only specific ones

Tutorials on Data Editing

One Minute Excel Tutorial – Sorting
Multi level or Multi column sort in Excel – How To
How to sort Rows horizontally left to right instead of Columns vertically top to bottom in Excel
Selecting and Highlighting difference using Excel Go To Special Row / Column Difference
Quickly fill Blank cells with the Value in the Cell Above
Custom Autofill series and Custom sorting with Custom lists in Excel
Automating Data Entry using Data Validation in Excel

Making changes to work space

While working in Excel you may need to make changes to work space as well. Like hiding or showing grid-lines, headings, formula bar, to show/hide ribbon or to zoom-in or out for better readability. For this we have two specific tabs i.e. Page layout and View.

Page layout tab options are more specific to prints that you will get. For example if you need to change overall font family, change margins of the page, orientation, customize print area or insert page break.

Excel Tutorial – Ctrl P is Power in Control (Printing)

View tab options are more specific to what you see on screen. In simple words the changes you make in this tab are won’t affect your print results and are only visible on-screen. Following are some of the tutorials to give you head start: