How to use Excel | Microsoft Excel Tutorial

Assuming you have installed Excel, probably you do not know what Microsoft Excel is used for? Then read on through to learn more about this application.

What is Microsoft Excel?

Excel is a powerful spreadsheet application for recording and analyzing numerical data. It lets users set up simple databases as well as develop full-fledged Windows applications using add-ons, forms, macros, menus, VBA, and windows.

Most large-scale financial institutions use Excel daily to process business transactions. It boasts an extensive collection of add-ons, formulas, and functions for storing and analyzing massive data sets in a fast and straightforward way.

Why Learn Microsoft Excel?

Individuals and businesses typically interact with data in several different ways. For example, we all have daily expenses we pay using monthly income. Accordingly, managing finances requires understanding one’s income and expenses. Here are four cogent reasons you should learn Excel.

Reason # 1: Excel Can Help You Solve Business Problems

Say you need to compile and compare transactions that occurred in Q4 of a previous year. After analysis, you can issue a report to your sales and marketing team. This lets you assess what to prioritize in the coming year. But how do you determine what is relevant? And how do you use leads to project next year’s returns?

Accomplishing this task undoubtedly is a tough undertaking. Honestly, if you knew how to use Excel, it should not intimidate you. With this tool, you can organize data (dates, sales records, business leads, and closed deals). Besides, it lets you perform both basic and complex calculations using its inbuilt mathematical functions and formulas.

Excel also lets users visualize data for more compelling presentations. By harnessing a spreadsheet’s power, you can derive actionable insights from business data. Therefore, you should master this tool.

Reason # 2: Excel Gets Stuff Done

Professionals desire to work efficiently and quickly. Besides organizing data, Microsoft Excel’s plethora of functions and programs will save you time, effort, and money. Most importantly, we all know how challenging compiling monthly expense reports is. With Excel, you can obtain accurate results as compared to using a conventional calculator.

Reason # 3: Improve Your Competitiveness in the Job Market

Microsoft Excel is a popular and widely used spreadsheet application. What is more, it supports collaboration between teams. This makes it suitable for departments with different platforms. Recruiters and managers appreciate the importance of a transferable skill to organizational performance. So, learning how to use Microsoft Excel increase one’s job prospects and paygrades as well.

Reason # 4: Excel Makes You Better at Your Job

Accountants and investment bankers are not the only professionals who use Excel. Business owners, students, scientists, graphic designers, and others depend on it too. Regardless of your responsibility at the workplace, this application will likely help you accomplish tasks better. What is more, mastering Excel will make you the go-to guy whenever your boss or colleagues need help.

Although it might intimidate you at first, daily using Excel to perform basic tasks will acquaint you with its features and simplify your life.

Where to Get Microsoft Excel

Several options are available for obtaining Microsoft Excel free. Either you can purchase it from a Microsoft appointed software vendor or download it directly from Microsoft.com. Even so, you must buy a license key to enjoy its full features.

Perhaps you have this question, can I download Microsoft Excel for free? Yes, you can! Some ways to obtain a free copy include:

• Microsoft free web and mobile apps. Note that these apps offer limited functionality.
• Get access to a free copy of Office 365 through your school or education institution. Nonetheless, this option lets you access the same features found on the trial version of Microsoft Office.
• Sign up for a free 30-day trial of Office 365. On the flip side, Microsoft requires your financial information before downloading a copy of Office 365. Besides, it might become difficult unsubscribing once the trial period expires.
• Finally, you can sign up for 30 to 60-day Office evaluation program. Through this option, you evaluate Office 365 ProPlus, a set of full-featured office application packages.

In this Microsoft Excel tutorial, we cover the basics of using this application, including:

• Opening Microsoft Excel
• Understanding Excel’s ribbon features
• Working with Excel Worksheets
• Customizing Excel
• Useful Excel Shortcuts
• Functions and formulas in Excel

I. Getting Started with Excel

An excellent approach for learning any software application program is doing something useful with it. So, let us begin with the basics of running Microsoft Excel. Running Excel is like using any other Microsoft Windows program. Here are several ways of launching the application.

a. Opening Microsoft Excel

If you are running Windows XP, Vista, 7, 8.0, 8.1 or 10:

Method I

• Click the Start menu orb/button
• Point to All Programs
• Point to Excel
• Click on Microsoft Excel

Alternatively, you can launch it from the Start menu if pinned there. Double-clicking Excel’s desktop shortcut can also open the program. Finally, use the Search box on the Start menu to look for Excel’s shortcut and launch it quickly.

b. Understanding Excel’s Ribbon Features

Once Excel opens for the first time, it will prompt you for what you want to do. So, open a Blank workbook to begin working. In Excel, the ribbon serves as the application’s control panel. Use it directly to perform the action you need. Basically, the ribbon groups related commands together, for example, creating a new document, inserting a picture, or printing a worksheet.

Excel Ribbon Components

Excel’s ribbon has several components, including:

• A start button: provides access to commands such as create a new workbook, save, print and customization options.
• Tabs: groups and displays commands for accomplishing a core Excel task. Excel 2019 version has the following tabs: File, Home, Insert, Page Layout, Formulas, Data, Review, View, and Help. For example, the Formula tab contains commands for working with Excel functions and formulas.
• Groups: Each tab contains a group of related commands. Similarly, each group has buttons, submenus, and dialog boxes. Clicking a button runs the related command. Groups in the Excel ribbon have extra commands. To view additional commands, simply click the arrow at the bottom right corner of a Group.  
• Buttons: Also, each group on the ribbon contains various types of buttons for executing commands.
• Dialog box launchers: some Excel groups have a dialog box launcher button located on the bottom right corner. Clicking this button launches a dialog box that displays the full set of available options in a Group.

c. Working with Excel Worksheets

An Excel worksheet is a collection of rows and columns. The intersection of a row and column forms a cell used to record data. Every cell has a unique identifier known as its cell address. Columns typically have a letter as its label, while rows use a number.

A collection of worksheets comprises a workbook. By default, each workbook has three worksheets. Even so, one can delete or add extra worksheets as required. To add a new worksheet, simply click the + sign at the end of the worksheet list. Likewise, to delete or rename a worksheet, right-click its tab, and select the appropriate command. Each worksheet has a default name of Sheet 1, Sheet 2, and so on. However, you can rename worksheets with a meaningful name, e.g., Monthly Budget, Daily Expenses, and so forth.

d. Customizing Excel

Excel allows users to customize its default appearance to suit one’s personal preferences. By customizing Excel, you can choose its appearance and options available on the screen. Explore the tips below for ways to personalize some Excel features:

• Personalizing the ribbon
• Setting a theme
• Formula settings
• Save settings

Customizing the Ribbon

Suppose you want to tweak Excel 2019 default ribbon settings. Then again, you might want to hide or add custom tabs to the ribbon. Use the Options dialog box to do just that. Here are the steps:

• Click the File tab.
• Select Options on the drop-down menu to open the Excel Options dialog window.
• On the left pane, select the Customize Ribbon
• Under the Customize the Ribbon, Main Tab setting, remove checkmarks from tabs you do not want on the ribbon.
• Click the OK button to save changes.

Add Custom Tabs to the Ribbon

With Excel, you can create custom tabs and add relevant commands to them. Use the following steps to add a custom tab.

• Right-click on Microsoft Excel ribbon and select Customize the Ribbon. The Excel Options dialog appears.
• Click the New Tab button.
• Select Rename.
• Input the name MyCustomTab.
• Choose the New Group (Custom) entry under MyCustomTab and name as MyCustomCommands.
• The next step is adding commands to the custom tab you just created.
• To do so navigate to the commands listed under Choose commands from the middle pane.
• Highlight Macros and click the Add button.
• Click OK to save your edits.

Changing Excel’s Theme

• Click on the File tab and select Options.
• Look for Office Theme under the General options.
• Use the color scheme dropdown box to select a color scheme to use.
• Click OK to dismiss the Excel Options dialog window. That is, it!

Tweaking Excel Formula Settings

Use this option to define how Microsoft Excel handles formulas. For instance, you can activate Excels autocomplete feature when entering formulas, change cell referencing styles and use numbers as labels for both rows and columns among other options.

• Use the File menu to launch the Excel Options dialog window
• Select Formulas on the left pane.
• Activate an option marking its checkbox. Likewise, removing the checkmark deactivates an option.

Setting Up Excel’s Proofing Settings

Proofing settings alter how Excel processes text entered in a cell. It lets one set the dictionary language for spellchecking, dictionary suggestions, and more. Access this option using the left pane of the Excel Options dialog window.

File Saving Settings

By default, Excel saves files based on the version you are using. Even so, older versions may fail to interpret or convert a newer versions default program file format. Hence, when working with teams using a different version or spreadsheet application, you may want to specify a default file format for your files. Use the following steps to accomplish that:

• On the ribbon, click the File tab
• Select Options to open the Excel Options dialog window
• Then click Save on the navigation panel
• In the “Save file in this format” drop-down list box, select the target file format.
• From now on, Excel will save files in the defined file format.

Other features this option supports include AutoSave and AutoRecover.  We all know how frustrating it is to lose a file you were working on just because you did not save it properly. Luckily, Excel’s AutoSave and AutoRecover features reduce the loss of data because of power outages, crashes, accidental deletion, and other factors.

Here we discuss how to configure both features to secure your data.

Excel AutoRecover

This feature is on by default and saves a temporary copy of your document after a preset time interval. The default time interval is 10 minutes. To change the default AutoSave time interval, follow these steps:

• Click on File then
• Select Save on the navigation panel to display Excel save settings.
• Look for Save autorecover information after every
• Use the up-down control to increase or decrease the default value.
• You can also change the default location where Excel saves auto-recovery information.
• Click OK to close the Options dialog window.

Bonus tip: for safety reasons, lower the time interval required to save auto-recovery information. Why? It increases your chances of retrieving accidentally closed files.

e. Useful Excel Shortcuts

Here are several indispensable shortcuts for working with Excel

• Ctrl + P: Opens the print dialog window
• Ctrl + N: Create a new workbook
• Ctrl + S: Save the current workbook
• Ctrl + C: copy data to clipboard
• Ctrl + V: Paste copied data
• Ctrl + Z: Undo an action
• Ctrl + Y: Redo an action
• Shift + F3: Open the function insert dialog
• Shift + F11: Insert a new worksheet
• F2: Check cell range coverage and formulas
• F7: To spellcheck

II. Functions and Formulas in Excel

And now let the fun begin!

Excel Formula Basics

Formulas and functions are Excel’s bread and butter. They are the foundation of everything you will ever accomplish with a spreadsheet application. This section is a basic introduction to concepts you need to master Excel formulas. First, let us define a formula.

What is an Excel formula?

In Excel, a formula is an expression that processes data values in a cell range or cell and returns a result.

Tip: All Excel formulas begin with the equals sign (=). For example, = A1 + A2 + A3. If cells A1, A2, and A3 have the values 5, 8, and 12, respectively. This formula returns 25.

Cell references

A cell reference or cell address in Excel is a unique alphanumeric value that identifies a specific cell in a spreadsheet. It consists of one or more letters and a number. An example is A1 which refers to the intersection of column A and row 1 with B1 denoting the second cell in Column B and so on.

Ideally, you should use cell references in formulas instead of actual cell values. Doing so makes it easy to maintain a spreadsheet. Also, you can alter values at any time without manipulating the underlying formula. The example formula above will still generate accurate results regardless of the values entered in cells A1, A2, and A3.

Caution! All Excel formulas return a value even if it is an error.

Copying and Pasting Excel Formulas

An advantage of cell references is that they update automatically when copied to adjacent cells. This eliminates the need to enter the same formula in multiple cells.

Types of Cell References

Excel uses three types of cell references, namely absolute, relative, and mixed cell references

• Relative Cell References

Excel defaults to relative cell references. A relative cell reference contains no dollar sign. In addition to that, relative references change based on the relative position of rows and columns when copied or moved across multiple cells. Use relative references to calculate values in adjacent rows or columns.

• Absolute Cell References

Absolute cell references have a dollar sign appended to the column letter and row number. Append a dollar sign to a row and column when you need to “lock” a reference. In this way, the cell reference remains constant even if a formula is copied or moved to another cell. An example of an absolute cell reference is $A$1.

• Mixed Cell References

Mixed cell references contain a relative and an absolute coordinate such as $A1 or A$1. Basically, it is a hybrid of relative and absolute references.

Bonus tip: Use the F4 key to toggle between relative and absolute reference modes.

Creating Formulas

As previously stated, all Excel formulas begin with the = sign. No equals sign, no formula! So, to enter a formula, do the following:

• Select a cell where a calculated value will appear
• Type the equals sign (=)
• Define the appropriate cell references or range. Press Enter.

Tip: Use the mouse to select cell ranges instead of typing.

Editing a formula

You have 3 options for editing a formula:

• Select a target cell, navigate to the Formula bar, edit.
• Double click a cell, edit directly.
• Highlight a cell that contains a formula, press F2, edit directly.

Regardless of the option you use, always press Enter to confirm changes. Use the Escape key to cancel any edits and leave a cell.

Excel Functions

Just like formulas, Excel has a collection of powerful functions. As you hone your Excel skills, you will come across these elements frequently. Some times people use these terms interchangeably. Despite being related, they are not similar. On the one hand, a formula begins with the equals sign. Whereas a function is a predefined formula that accomplishes a specific task.

Microsoft Excel includes many functions for calculating the sum, average, maximum and minimum value of cell ranges.  Some example functions are SUM, AVERAGE, COUNTIF, and YEARFRAC.

Structure of a Function

Functions are like recipes which transform ingredients into something different. Excel functions consist of three essential elements, namely:

• A function name: a unique identifier that informs Excel what you want to do.
• Arguments: the ingredients you input into a function to obtain the desired value.
• An output: the final product a function presents to the user.

Let us look at Excel’s SUM function to learn about a function’s syntax. As Excel’s most used function, it takes several numerical arguments.  Simply, enter the following formula in an empty cell

=SUM (3,7) and press enter. Excel will output 10 as the answer. Also, this function can take multiple arguments

=SUM (1, 2, 3, 4, 5) with the answer being 15.

Entering Functions in Excel

To enter a typical Excel function, you need to:

• Know its name
• Provide the arguments
• Press Enter to confirm the formula

Tip: Remember that a function’s arguments must always appear inside parenthesis. Otherwise, you will receive an error message.

Nesting Functions

Sometimes you might need to use multiple functions in a calculation. Say we need to calculate an individual’s current age. We can nest the TODAY () function to provide the end date to the YEARFRAC function as illustrated below. The result will appear in cell B1

=YEARFRAC (B1, TODAY ())

Excel Best Practices

• Use the .xls format to save workbooks. This ensures that your file is compatible with older Excel versions.
• Give descriptive names to columns and worksheets as well.
• Avoid using complex formulas that require many variables. Instead, break down formulas into manageable chunks. And then build on the results.
• Prioritize inbuilt Excel functions instead of entering formulas manually

Now that we have come to the end of this post, we hope it benefited you. In the future, we expect to cover advanced Excel features for readers. Happy Excel-ling!