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 tools and features
Following table describes what each item is and very briefly its function:
|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
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.
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:
Tutorials on work space adjustments
>> Coming Soon
This Guide is under continual update and new topics are adding. Please bookmark this page to come back again to learn additional topics.
Following are some of the topics under process to be released soon:
- Excel charts
- Pivot Tables
- Sort, Filters and Slicers
- Power Query