Blog

Beginners Guide to Microsoft Excel Spreadsheet Application 1

Beginners Guide to Microsoft Excel Spreadsheet Application 1 1

SPREADSHEET PACKAGES

Examples of spreadsheet packages

A spreadsheet is a sheet of paper that arranges data in rows and columns. A spreadsheet application is a computer application program that can be used to manipulate data arranged in columns and rows, electronically. Examples of spreadsheet packages include Microsoft Excel, Open Office Calc, lotus 1-2-3, StatView, Gnumeric, WordPerfect Office Quattro Pro, PlanMaker, Corel Calculate, Ability office, Apache Office, Microsoft Power Business Intelligence, etc.

Uses of spreadsheet applications

  1. It can be used in budget preparation and perform pivot table analysis.
  2. It can be used in the tracking of daily sales and computation of totals and averages.
  3. It can also be used in the computation of school results.
  4. You can use a spreadsheet to perform graphical analysis of data.
  5. Spreadsheets are useful in sorting of data in ascending or descending order.
  6. It can be used to perform basic statistical analysis such as regression, ANOVA, correlations, etc.
  7. It can be used to create and manage simple databases using flat-file model.
  8. It can be used to compute profits and losses of a business transaction.

Spreadsheet features and terminologies

Cell – A cell is a rectangular area formed by the intersection of a column and a row in a spreadsheet. A cell has a cell reference or name or cell address, which is the combination of the column header and row label, e.g. cell A1, G3, etc.

Active Cell – The active cell is the cell in the spreadsheet that is currently selected for data entry.

Cell Name – This is by default the cell reference or address. However, one may define a particular cell or range of cells with an alternative name. This alternative name can then be used in formulas and functions. It can also provide a quick way to jump to a particular area of the spreadsheet.

Cell Reference – This is the name of the cell that is found by combining the Column Letter with the Row Number. For example, the cell in Column “A” in Row “5” would be cell A5.

Column – This refers to the vertical bars on the spreadsheet screen. In Excel 2013, it is labeled with alphabet A – XFD. Excel 2013 can handle up to 214 labels giving a total of 16384 columns.

Chart – This is a pictorial representation that is used to summarize categorized data. There are different types of chart used in the excel spreadsheet, ranging from the bar chart, line graph, pie chart, etc.

Data – This refers to the type of information that can be stored in the cells of a spreadsheet. Spreadsheet data types include values (numbers), labels, formulas, and functions.

Formula – This is a spreadsheet data type that will perform calculation and display the result in the active cell. A formula is written using cell references and must always begin with an equal sign “=” to distinguish it from a label. E.g. “=G5/H5”.

Formula Bar – It appears directly above the column headings of a spreadsheet and will display what has been typed into the active cell.

Function – Functions are built-in formulas in a spreadsheet. It can be simple or complex formulas. Like formulas, functions begin with an equal sign “=” and use cell references in their format. E.g. (=sum A1: A5) will calculate the total sum of values in cells A1 to A5.

Gridlines – These are the horizontal and vertical lines on the screen that separate cells in a spreadsheet. Gridlines do not print by default unless the option is set in the layout options of the spreadsheet.

Name Box – Also called the address bar. It appears to the left of the formula bar and displays the name of the current cell unless you define a cell or range of cells with a specific name.

Range – A range is a group of cells in a spreadsheet that has been selected. E.g. a selection from cells B1 through F5 will be represented as B1:F5 and is shown below:

specifying range

Rows – Rows are horizontal bars on the spreadsheet screen. Excel 2013 spreadsheet contains 220 = 1048576 rows which are labeled from 1 to 1048576.

Values – These are numeric data entered into a cell. Numbers are aligned to the right by default in excel spreadsheet.

Workbook – Excel spreadsheet files are called a workbook. A workbook is a collection of worksheets that are saved together in one file.

Worksheet – A worksheet is the grid of columns and rows that information is inputted into. In Excel, one workbook can contain several worksheets. Worksheets can be named using the worksheet tabs at the bottom of the spreadsheet window. The sheet tabs can also be used to switch from one worksheet to another within a workbook. Previous versions of Excel display 3 worksheets by default when it is loaded, but 2013 and 2016 versions display only one worksheet. The user has the option of adding more worksheets as required.

INTRODUCTION TO MICROSOFT EXCEL SPREADSHEET APPLICATION

The Necessity of Excel in your Career

Microsoft Excel spreadsheet application is the most popularly used and one of the industry leading spreadsheet application. It has powerful visualization and analytical tools for recording and analyzing a wide range of data. Different industry data ranging from oil & gas, telecommunications, manufacturing, banking, etc, can be recorded, organised and analysed using MS Excel tools. The range of intelligent tools in Excel can help you perform calculations using formulas, display data using tables and a wide range of charts and graphs, and share & collaborate with colleagues in real-time.

Microsoft Excel spreadsheet application happens to be one of the most important applications that anyone who wants to work in Oil & gas must learn. This is because, no matter your role, you will have to use it at some point in your work schedule. There are skills that are really important to learn in Excel, especially for those who want a career in oil & gas. Some of these skills include understanding and applying filters to data, working with Vlookup and Hlookup, understanding and reporting data with Pivot tables and Charts, etc.

In as much as one can dive into learning the above-listed tools and work with them. At some point in your career, you may encounter problems depending on your job role. This is why it is important for us to learn and understand the basics of Excel and how to use simple functions. Also, a basic understanding of MS Excel spreadsheet application will enable you to work with other spreadsheet applications and add-ins, especially, Power BI and Petroleum office add-in.

This tutorial is the first in the series of tutorials that will teach required skills in Excel spreadsheet application for beginners, through to the intermediate & advance level for anyone who wants to work in any sector, be it oil & gas, banking, or any other.

Feel free to ask questions by using the comment section, and be sure that your questions will be answered, no matter how trivial. You will never be lost at all. Let’s get started!

Note: We are working with Excel 2013/2016

Loading and Exiting Excel

To load a Microsoft Excel spreadsheet application, do the following:

  1. Open Run command by holding the Windows logo key and press R key on the keyboard (Windows logo + R)
  2. On the dialog box that appears (see above), type excel and click OK, MS Excel opens.
Opining MS excel
  • On the window that is displayed (see above), select Blank workbook to open excel workbook and begin entering data.
  • You can also choose any of the available templates. E.g. Personal Monthly budget, Family budget, etc.
  • The left pane in the above window shows existing documents, especially, recent ones. You can choose the workbook you want to open here.

To exit the Microsoft Excel spreadsheet application, do the following:

  1. From the MS Excel window shown below, select Xin the top right corner of the window.
  2. Your MS Excel application terminates. But if you have not saved your work, it will ask you to save changes made to your workbook.
save changes
  • Select Save or Don’t Save as you desire.

WORKING WITH MS EXCEL WORKSHEETS

Entering and Editing Data

You can enter and edit text, numbers, dates, or times in one cell, or in several cells at once, or on more than one worksheet. To enter and edit data in the Microsoft Excel spreadsheet application:

  1. On the worksheet, select a cell.
  2. Type the number(s) or text that you want, and then press ENTER on the keyboard to continue in another cell downwards or press TAB on the keyboard to continue in another cell rightwards.
  3. When entering or editing data, a cell may display ##### when it contains data that has a number format that is wider than the column width. To see all text, you must increase the width of the column.

To edit or correct data in a cell, do the following:

  • Double-click the cell that contains the data, OR
  • Select the cell that contains the data and then click anywhere in the formula bar.
  • Use the left or right arrow key to move to your point of correction and correct appropriately. You can use BACKSPACE on the keyboard to delete unwanted data.

To enter the same data into several cells at once:

  1. Select the cells into which you want to enter the same data. The cells do not necessarily have to be adjacent.
  2. In the active cell, type the data, and then press CTRL+ENTER.
  3. You can also enter the same data into several cells by using the autofill handle to automatically fill data in adjacent worksheet cells. You may also double-click the autofill handle to fill the relevant cells.

Saving Data

  1. Hold down the CTRL key on the keyboard and press S on the keyboard (CTRL + S). OR
  1. On the File Menu, click Save or Save As. The following window will appear. In the middle pane, choose where you want to save (This PC or OneDrive – Personal). OneDrive – personal requires an internet connection. In the right pane, choose the folder (say, My Documents).
  2. The Save As dialog box appears.
Saving workbook
  1. Type the name you want to use to save the workbook in the File Name Box, and choose the location you want to save your workbook from the Save in Combo Box. (Note: if you did not choose a location, the workbook will be saved by default in Documents folder)
  2. Click the Save button to save your workbook.

Opening Existing Workbook

  1. Open the Run command by pressing (Windows logo + R)
  2. On the dialog box that appears, type excel and click OK, MS Excel spreadsheet application opens.
  3. To open an existing workbook, on the left pane, under Recent, select a Workbook to open.
Opining MS excel
  1. If the workbook you want to open is not displayed, click on Open Other Workbooks. Then choose the file you want from the folder location.