I come across many users whose area of work require some basic level of numeric computations or entry of series of numbers and values, but for lack of know-how, they perform the basics of these tasks with much difficulty and a burden on their daily productivity.
Microsoft Excel Spreadsheet, otherwise called Excel is the preferred choice for numerical based computations with almost no limit to what results one require.
Other spreadsheet applications are available, but this article is an approach for beginners to familiarize themselves with the basic elements and principles of the most powerful and widely used spreadsheets of all time, Microsoft Excel.
The Many Shapes Of The Mouse Pointer
Very often, the difficulties people face when working with excel begins with them not understanding the many shapes of the mouse pointer on spreadsheet events. To be able to perform any meaningful computation, understanding of the changing shapes of the mouse pointer is a step ahead in getting things done right quickly.
It is only unfortunate for new users that, there are no provisions for captions of the shapes of the mouse pointers when changed during activities.
The Excel Cells
Unlike a typical spreadsheet, excel is designed with cells as a results of rows and columns, realize how the column letters start horizontally from ‘A‘, through to a never ending range?
Similarly, the rows which increases the size of the worksheet vertically is also captioned numerically from ‘1’ through to over a million rows. Truly the span of the spreadsheet makes users believe the rows and columns are infinite, but ironically, they are limited. The spreadsheet is that consuming as almost inestimable.
Typically, each cell is automatically sized by default in the workbook. Chances are you may want to resize the cells to fit your work. Hover the mouse to the division lines across the column letter or row number of the cell, and notice the change of the mouse pointer to the re-size bar (vertical resizer when positioned on the row, and horizontal resizer, when position on the column row) and then drag further.
The Active Cell And Fill Handle
There can be only one active cell at a time. The active cell is the cell of the spreadsheet that is activate for data entry. The active cell is surrounded by a thick black border, and its address is displayed in the name box. The row number and column letter are also highlighted.
By clicking on a cell in the spreadsheet, it becomes an active cell. Notice the small square in the bottom right-hand corner of the thick black border? That is what is called the fill handle.
With the aid of the fill handle, you can copy the contents of an active cell to adjacent cells, by dragging gently outside the fill handle of the initial cell to extend to a series of cells to fill to the desired cell. (Notice how the cursor changes from a thick white cross to a black tiny cross).
The fill handle is very effective to increment values from an initial cell.
With the aid of the fill handle you don’t need to type the Months of the Year or Days of the Week. For example, when the initial cell contains Months such as January or March or any other day of the week for that matter, the fill handle will automatically increment subsequently the entries till your desired period is achieve.
The Excel Name-Box
As already mentioned, excel is made up of rows and columns, which means that each cell in the excel spreadsheet is uniquely identified by a row/column name, so the first cell on the top-left corner of the excel sheet would be uniquely identified as “Cell columnA Row1″ or “Cell A1″.
With this understanding, every cell in the spreadsheet links a row and column which uniquely identifies the particular cell in reference, and in other not for the user to be bothered by counting or figuring out the position of each cells at a point in time, there is the availability of the name box which displays the name of each reference cell.
The primary function of the name box is to display the name of the reference cells or an active cell at any particular point in time.
Select All Cells Option
Microsoft Excel provides a miniature triangular button of the top left intersection between the row and column just below the name box. At the click of this button, it selects all cells as with “Ctrl +A”.
The Excel Formula
Formulas are the reason for the incredible powers of the excel functionality. Formulas typed correctly can virtually produce results for virtually any level of calculations you can imagine. The way to perform basic calculations is primarily with the help of a formula.
The framework of excel requires that computations are performed on the cell references rather than the values directly or cell content. In conventional spreadsheet, we can go like “5+6“, but with excel you will see something that looks like this “=B2 + C2“, where B2 is in reference to 5 and C2 is in reference to 6.
Formulas are typed in the cells, but advanced users can type them directly into the formula bar making reference to the effective cells.
In creating a formula, basic rules apply. A formula always starts with an equal sign “=”. The equal sign activates the formulas available to handle your request. In composing formulas, all the standard mathematical operators commonly used, such as addition, and subtraction and division apply.
Ideally, every excel formula is entered with brackets and what excel does is to automatically compute the instructions and display the results in the same cell the formula was entered. (Overwriting your computed formula with the results). You can refer to the formula bar adjacent to the name-box which will displays your formula.
Examples of simple formulas are =sum(A2:B2) =sum(A2+B2) =sum(A2*B2) =sum(A2/B2)
In performing addition in excel, it is preferred to use the colon instead of the addition sign, especially when it involves multiple cells.
Examples of some formulas listed below.
- SUM() to calculate the total of a set of numbers
- AVERAGE() to calculate the average of a set of numbers
- MAX() to calculate the maximum value within a set of numbers
- MIN() to calculate the minimum value within a set of numbers
- TODAY() to show the current date
Data Entry Format
Excel automatically recognizes dates, currency, texts and numbers in the worksheet. Understanding how excel treats these different data types will help you structure your work efficiently as possible.
Most people have not observed that excel automatically aligns numbers or currencies to the right and texts to the left position of the cell. There is a danger in mixing texts and numbers together in a single cell as it will defeat the function of numeric calculation. Excel will align these combined texts and numbers to the left and you may not be able to apply a formula for calculation or at worst, the wrong results will be displayed.
Data entry can be very tricky to most users. Sometimes when we import data from other sources into excel, excel may recognize the numbers as text, causing calculation challenges in your work. The trick in solving this simple but confusing challenge is to highlight the cells which needs to be modified and select Numbers from the drop-down button of the Number format located on the home ribbon. By default, format is set to General.
By default, all cell references in the workbook are relative cells. This means when you drag outside a cell with the fill handle, the formulas or texts will change based on the relative position of the rows and column.
If you copy the formula =sum(A1+B1) from row 1 to row 2 and 3, the formula becomes=sum(A2+B2) and =sum(A3+B3) respectively according the related cells. ColumnA is maintained, whiles the reference of the rows is changed in the relation to the row number.
Relative cells is what makes it possible to increment numbers, dates, days, Years, Weeks, etc. as you copy the initial cell with any of these examples, the cells are automatically filled with the accurate data.