9 Excel Basics You Should Learn

Microsoft Excel is an electronic spreadsheet oriented Application Program devised and developed by  Microsoft. It provides a variety of features to its users, such as performing diverse calculations and operations, graphing data using charts and graphical bars, creating pivot tables and plots, and a macro programming language known as Visual Basic for Applications, that helps to reduce the redundancy and monotony of routine tasks in Excel. It has been a very widely applied spreadsheet program around the globe. Excel is a part of the application program under MS Office software.

Microsoft Excel offers a plethora of features that use a grid of cells arranged in lettered columns and numbered rows to organize, formulate, manipulate and format data. It has a plethora of supplied or user-defined functions under its wings to answer statistical, engineering, mathematical and financial problems and fixes. In addition, it can be used to view and format data as line graphs, histograms and bar charts, as well as pivot tables, with a three-dimensional graphical display. Thus, the need to view, edit, organize, store, format and manipulate data, closely related to each other, and make the information interesting and consuming, when viewed by any user, is met by MS Excel.

Advantages of Excel

Excel is often used to store, manipulate and organize financial data. Thus, a variety of formulas and functions can be developed that can be used to perform the following operations:

  1. Arithmetic calculations: Summing, Subtracting, Multiplying, or Dividing cells or range of cells.
  2. Obtaining Profit and Loss in Financial Data.
  3. Obtaining Mean, Mode, Maximum and Minimum values in a range of statistical or mathematical values.
  4. Sorting Data on the basis of a desired feature, to find specific information
  5. Formulating conditions and organizing data on the basis of a specific condition applied to a cell or range of cells.
  6. Carrying out ‘What-If’ Analysis on Data.
  7. Organizing statistical data in the form of graphs or chats, to make it presentable and for simplified analysis of data.
  8. Importing Data from other database programs for simplified analysis.

EXCEL BASICS

Let us now get acquainted with some of the basic features of Excel that can help you store, organize, format, edit or manipulate data:

Excel Basics: Performing Mathematical/Arithmetic Calculations

Why use a calculator when Excel can be used to perform all mathematical operations! All you need to know is the basic syntax and application of arithmetic formulae in MS Excel.

For Example:
SUM: The mathematical values stored in a range of cells can be computed by the use of syntax:
=SUM(Beg_Cell_Address:End_Cell_Address)
OR
=Beg_Cell_Address+End_Cell_Address
in the blank cell where you want the total to appear.

Some other Mathematical functions that can be used are:
PRODUCT(), POWER(), SQRT(), SUBTRACT(), etc.

For example: In the spreadsheet ‘Society Work’, to compute the sum of working hours of all the employees in the month of January, the following formula is applied, to display the result as 69.

Note: If you want to compute the sum of all the months’ working hours without having to undergo the burden of separately applying the mathematical formula to each month column, it can be done by applying it on January column and then dragging the small cornered dot located on the bottom right corner of the cell onto the other columns where the similar formula has to be applied for computing sum of total working hours of all employees.

Excel Basics: Sorting Data

MS Excel can be used to arrange data in a predefined order (ascending or descending) on the basis of a particular condition. This can further be used for data analysis and draw various observations and conclusions from the sorted data and make the spreadsheet readable and understandable.

The following steps need to be followed sequentially in order to sort data:

  1. Select the entire data by clicking on a small icon located on the left-hand corner of the spreadsheet, near cell address A1 OR by dragging the cursor across all the cells of the spreadsheet.
  2. Click on Data tab on the Menu Bar.
  3. Click on Sort button under Sort and Filter section.
  4. Click on ‘Sort By’option and select the column address according to which data has to be sorted. Further, choose the Sort On as well as the requisite order of sorting and click on OK.

Excel Basics: Conditional Formatting of Data

Conditional formatting is an excellent feature of MS Excel that enables you to apply desirable formatting features and get you a chance, to even sort the data using the formatted result—such as colors, icons, and data bars—to one or more cells based on the cell value, that is, the value contained in the cell.

To do this, it would be required to create a Conditional Formatting Rule, also called as Conditional Formatting Condition. For example, a conditional formatting rule might be: If the money is less than 2000, color the cell pink. By applying this rule, you’d be able to quickly see which cells containing money less than 2000, and can further be used for formatting.

Creating a conditional formatting rule/condition

You can create a formula-based conditional formatting rule in four easy steps:

  1. The cells that need to be formatted should be selected.
  2. The desired conditional formatting rule should be created by using a specific formula.
  3. The formula created should return Boolean values as result, that is, TRUE or FALSE.
  4. Formatting option needs to be set, and click on OK, to save the conditional formatting condition.

In the below example, the function only returns TRUE (1) for odd numerics, according to the formula:

Conditional Formatting provides a way to visualize data and make worksheets understandable and simplified and may also produce a way of sorting relevant and desired information.

Excel Basics: Removing Duplicate or Redundant Data in Excel

Whenever a user is working with large values of data, it is inevitable for the user, however high the efficiency, to eliminate some errors, such that some duplicates may find their way in and make the data redundant and non-reliable for usage. However, rather than getting frustrated and irritated of the high amount of duplicity, Excel provides a way of removing duplicate data and make it highly informative and usable.

The following steps need to be followed sequentially in order to remove duplicate data:

  1. Select the entire data by clicking on a small icon located on the left-hand corner of the spreadsheet, near cell address A1 OR by dragging the cursor across all the cells of the spreadsheet.
  2. Click on Data tab on the Menu Bar.
  3. Click on Remove Duplicates option under Data Tools selection.
  4. Select the column where you want to remove redundant or duplicate data.
  5. Click on OK.

Excel Basics: Locking Cells

Excel is a multidisciplinary spreadsheet program that allows you to protect the contents and specific data in a sheet that the user desires or wants in a spreadsheet, being rendered unavailable for editing or further changes. In doing so, you can choose whether users are allowed to select or edit a specific cell or a large array of cells, insert or delete rows or columns, allow conditional formatting, sort specific content in a sheet or variety of other options, that can only be applied to cells not locked or cells that are available for editing.

How to Lock Specific Cells in a Worksheet

There would be times or requirements when the pre-user wants that only certain cells should be available for the users to edit or format, while the other cells should store that data permanently, and should not be made available to edit, which may lead to erroneous results or impacts.

Consider the example of spreadsheet, Society Work. Though the fields or columns called S.No, Names, Branch, and Department Selected For would be constant and need not be edited, the field or column called Payment Status needs to be regularly updated. Thus, in this case, all cells except Payment Status need to be locked, since they should not be edited or formatted by other users.

  1. Select the cells that you don’t want to be locked: These would be the cells that would be made available to the user to edit afterwards and the content inside them can be changed or modified.
  2. Right-click on your selection of cells, click on the option ‘Format Cells’, and further click on the Protection tab in the dialog box.

    Also, in an alternate manner, click on Home Tab in the Menu Bar, provided the cells to be formatted are already selected, click on the expansion icon next to the option of Alignment, and then click on Format cells option. Further, in the Format cells dialog box, click on Protection Tab.

  3. Click on the check box next to ‘Locked’ option, which is by default, highlighted and click on OK.
  4. Click on Review Option on Menu Bar, and further, click on Protect Sheet option to protect the sheet.

Now, any cells that the pre-user did lock under the Format Cells option will be locked, while the unlocked cells will be editable and can be modified.

Excel Basics: Creating Macro

If there is a certain work or command or task that needs to be followed or done large number of times, that repetitive task can be automated, in order to reduce the redundancy of writing or undergoing the same task repeatedly or successively.

A macro is an action or a set of actions that is devised to run as many times as you want. When you create a macro in Excel or other programs, you are recording your mouse clicks and keystrokes that will be followed in an automated manner, such that the workload of a particular redundant task is reduced.

How to Record a Macro

  
Provided that the Developer Tab is already visible on the Menu Bar, you can follow the aforementioned easy steps of recording a macro in Microsoft Excel sequentially:

  1. Click on the Developer Tab on the Menu Bar, and go to the Code section.
  2. Click on Record Macro option, such that a dialog box appears.
  3. Optionally, enter a user-defined name for the macro in the Macro name box, which needs to be distinct and cannot be used beforehand as another Macro. Also, an already defined Microsoft Keyword cannot be used as Macro Name.

    Enter a shortcut key in the Shortcut key box, location for using Macro, which is on ‘This Workbook’ by default and an appropriate description in the Description box, which will help to explain about what the Macro wants to establish or accomplish and then click OK to start recording.

  4. Perform the actions you want to automate, that need to be a part of the macro, such as entering boilerplate text or filling down a column of data or performing a particular sum.

    For example, if you want a Macro to display the sum of cells C4 to C23 in a Cell C24, whenever the Macro is run using the same user-defined Macro Name, along with the pressing of the shortcut key, within the prescribed location of running Macro, the similar action would be achieved.

  5. Click on the Developer Tab and click on Stop Recording, for the Macro Recording to stop.
  6. Save your file in a macro-enabled view. In order to preserve your macros and be available in future, you’ll need to save your workbook in the following manner:
    • Click on the File menu on the Menu Bar and select on Save option.
    • Click on the File Type menu underneath the file name field.
    • Click on Excel Macro-Enabled Workbook.
    • Give a user-defined name to the workbook and click on Save to finally preserve Macro and the workbook.


Note:
In case of 2007 Version of MS Excel, click on Microsoft Office Button on the top left of the Excel Program, and click on Save. A dialog box appears, asking to save in ‘Excel Macro-Enabled Workbook’ mode. Click on ‘Yes’ to save the workbook and to preserve the Macro.

Macro is a complex feature to use and understand, and you can even use them to open in other Office application programs. When the macro is recording in Excel, virtually everything you do is added to the macro command, from the hovering of the mouse to the striking of the keyboard!

Excel Basics: Performing Data Analysis

Data analysis, also known as analysis of data or data analytics, is a method to clean, inspect, transform, model and introspect data with the aim to discover and decipher new information, form suitable conclusions, edit and format data accordingly, and to validate the decision making process in a suitable and efficient manner. Data analysis has multiple facets, constituting a variety of diverse approaches under a variety of names, in different domains of life, that help to perform a variety of operations and validate the decision making process.

An Analysis ToolPak is an Excel add-in program that is essentially the basis of data analysis and examination and provides data analysis tools for financial, statistical and engineering data analysis and is used widely for data examination and inspection.

Excel can perform several statistical tests and analysis using the various features provided by it. First, make sure you have your Data Analysis Tool Pak installed for performing Data Analysis. You should see the option of Data Analysis on the far right of your tool bar. If you don’t see it, go to FILE and then choose OPTION option, and finally click on ADD INS and add the Analysis Tool.

This is further used to obtain mean, median, mode, standard deviation and other mathematical and statistical analysis of data.

Excel Basics: Freezing Panes

Freezing Panes is a feature of MS Excel that allows the user to view a spreadsheet in a simplified and understandable manner, such that it reduces the stress of the user to continuously go back up to the top to see column headers, while those headers can be frozen. This can also be applied to rows, and can help in freezing cells, which would be retained on the spreadsheet screen, even on scrolling throughout the huge spreadsheet.

The aforementioned steps need to be followed sequentially to freeze panes:

  1. Select the cells or panes that you want to freeze.
  2. Click on View Tab on the Menu Bar.
  3. Click on Freeze Panes option under Window section.

Now, scroll around and across the spreadsheet to see the desired information frozen on the screen, thus enhancing, the readability and understandability of spreadsheet, while at the same time, reducing the workload of scrolling.

Excel Basics: Creating Charts and Graphs in Excel

Creating Charts and Graphs in Excel is an essential feature as it allows the data to be presented in a statistical manner, which makes it easier to visualize data and understand it in a simplified manner. While creating charts and graphs is very indigestible manually and may be erroneous, it is extremely simple when done on Excel.

How to create charts and graphs ?

The following steps need to be followed orderly to create charts and graphs in MS Excel:

  1. Enter or Select the data for which the pictorial representation has to be created.
  2. Click on Insert Tab under the Menu bar, and click on Chart as desired.
  3. Choose the type of chart on which you want to represent data: Pie, Horizontal, Bar, Line, and so on.
  4. Modify the details of X-axis and Y-axis, use suitable values, and set the desired type for the scales of axis.
  5. Verify the data and click on OK to obtain the desired chart, graph or pivot table.

Thus, MS Excel provides a variety of basic and fundamental features that helps to view, edit, organize, store, format and manipulate data, closely related to each other, and make the information interesting and consuming, when viewed by any user.

Comments are closed.


Magoosh blog comment policy: To create the best experience for our readers, we will only approve comments that are relevant to the article, general enough to be helpful to other students, concise, and well-written! 😄 Due to the high volume of comments across all of our blogs, we cannot promise that all comments will receive responses from our instructors.

We highly encourage students to help each other out and respond to other students' comments if you can!

If you are a Premium Magoosh student and would like more personalized service from our instructors, you can use the Help tab on the Magoosh dashboard. Thanks!