Introduction to IT PracticalFile

Microsoft Excel
Microsoft Excel is a spreadsheet, developed by Microsoft for Windows,
macOS, Android and iOS. Microsoft Excel is a spreadsheet program that is
used to record and analyse numerical data. Think of a spreadsheet as a
collection of columns and row that form a table. Alphabetical letters are
usually assigned to columns and numbers are usually assigned to rows. The
point where a column and a row meet are called a cell.

Brief history

Dec,1985 Microsoft Launched first version of Excel.

1986-88 Microsoft releases versions 1.0.6 and1.5.
29 Dec 1988 Launch of Excel 2.1 for MS-DOS
1989 Launch of Excel 2.2 for Macintosh. New version includes
improvements in the calculation speed by 40% and added flexibility
of different styles within a single document.
1990 Excel 3.0 is launched. This version includes Workbooks and is one of
the earliest Macintosh applications to offer Users Publish & Subscribe
functionality.
4/1/92 Microsoft Releases Excel 4.0 for Windows3.1.
12/14/1993 Excel 5.0; This version includes improved Workbooks and the
replacement for Excel Macro Language with Visual Basic.
07/27/1995 Excel 7.0 forWindows95/NT.
01/15/1997 Excel version 8 forWindows97.
Excel version for Windows98.

14|P a g e
 Introduction to IT PracticalFile
Microsoft Excel Uses

Microsoft Excel is a spreadsheet program. That means it's used to create

grids of text, numbers and formulas specifying calculations. That's extremely
valuable for many businesses, which use it to record expenditures and
income, plan budgets, chart data and succinctly present fiscal results.

It can be programmed to pull in data from external sources such as stock

market feeds, automatically running the data through formula such as
financial models to update such information in real time. Like Microsoft
Word, Excel has become a de facto standard in the business world, with

15|P a g e
 Introduction to IT PracticalFile

xcel spreadsheets frequently emailed and otherwise shared to exchange data

and perform various calculations.

Excel also contains fairly powerful programming capabilities for those who
wish to use them that can be used to develop relatively sophisticated
financial and scientific computation capabilities.

Microsoft Excel User Interface

Here is some basic Introduction of Microsoft Excel interface, when you open
MS Word for the very first time.

Understanding the Ribbon

The ribbon provides short-cuts to commands in Excel. A command is an action
that the user performs. An example of a command is creating a new document,
printing a documenting, etc. Ribbon components explained.

16|P a g e
 Introduction to IT

• Ribbon start button-It is used to access commands i.e. creatingnew

documents, saving existing work, printing, accessing the options for
customizing Excel, etc.1
• Ribbon tabs–The tabs are used to group similar commands together. The
home tab is used for basic commands such as formatting the data to make
it more presentable, sorting and finding specific data within the
spreadsheet.
• Ribbon bar–The bars are used to group similar commands together. As
an example, the Alignment ribbon bar is used to group all the commands
that are used to align data together.

Microsoft Excel Window Components

All the major component of Microsoft Excel:-

• Active Cell: A cell which is currently selected. It will be highlighted by

rectangular box and its address will be shown in the address bar. You
can activate a cell by clicking on it or by using your arrow buttons. To
edit a
cell, you double-click on it or use F2 to as well.

• Columns: A column is a vertical set of cells. A single worksheet

contains16384 total columns. Every column has its own alphabet for
identity, from A to XFD. You can select a column clicking on its header.

Rows: A row is a horizontal set of cells. A single worksheet contains

1048576 total rows. Every row has its own number for identity, starting
from 1 to 1048576.You can select a row clicking on the row number
marked on the left side of the window.
• Fill Handle: It’s a small dot present on the lower right corner of the
active cell. It helps you to fill numeric values, text series, insert ranges,
insert
serial numbers, etc.
• Address Bar: It shows the address of the active cell. If you have
17|P a g e
 Introduction to IT
selected more than one cell, then it will show the address of the first
cell in the
range.
• Formula Bar: The formula bar is an input bar, below the ribbon. It
shows the content of the active cell and you can also use it to enter a
formula ina
cell.
• Title Bar: The title bar will show the name of your workbook,
followed by the application name (“Microsoft Excel”).

18|P a g e
 Introduction to IT
• File Menu: The file menu is a simple menu like all other applications.
It contains options like (Save, Save As, Open, New, Print, Excel
Options,
Share, etc.).
• Quick Access Toolbar: A toolbar to quickly access the options which
you frequently use. You can add your favourite options by adding new
options
to quick access toolbar.
• Ribbon Tab: Starting from the Microsoft Excel 2007, all the options
menus are replaced with the ribbons. Ribbon tabs are the bunch of
specific
option group which further contains the option.
• Worksheet Tab: This tab shows all the worksheets which are present
in the workbook. By default, you will see, three worksheets in your
new
workbook with the name of Sheet1, Sheet2, Sheet3respectively.
• Status Bar: It is a thin bar at the bottom of the Excel window. It will
give you an instant help once you start working in Excel.

Features of Microsoft Excel

You Can Insert a New Worksheets at Will

You might have seen 1 o 3 default worksheets when you open the new
workbook (number of worksheets may vary depending on the settings).

You can insert a new worksheet in excel and delete the same at any point in
time. To insert any new worksheet, you simply need to press the “PLUS” icon
key, which is available at the bottom.

Click on this button to insert a new worksheet.

You can also insert a new worksheet by simply pressing the shortcut key Shift
+ F11.

19|P a g e
 Introduction to IT
Time Saving Shortcut Keys

All the time you spend on excel can be reduced drastically by masterful excel
shortcut keys. Apart from universal “Ctrl + C, Ctrl + X, and Ctrl + V,” we can
use many other abundant shortcut keys in excel.

Shortcut key for Copy:

Shortcut key for Cut:

Shortcut key for Paste:

You can refer to our article on “Excel Shortcut Keys” to learn some of the
important shortcut keys.

Get Quick Sum of Numbers

If you have serial numbers or any numerical data, then you can quickly get the
sum of these numbers with simple shortcut keys. Assume you have numbers in
10 cells and just imagine how long it is going to take you to get the total of
these numbers.

20|P a g e
 Introduction to IT

Simply select the cell where you need to get the total of these numbers and
press the shortcut key ALT + =to get the auto sum.

We get the following result.

21|P a g e
 Introduction to IT

Filtering Data

Filtering the excel data is one of the important features of Microsoft Excel. We
can filter any data available under the filter section. We can use more advanced
techniques under filtering the data. To insert a short filter, you can press the
shortcut key Ctrl + Shift + L or Alt + D + F + F.

Shortcut key to Insert the Filter:

or

For example,

22|P a g e
 Introduction to IT

Paste Special Feature

I love to paste special because it gives more edge to increase workplace

productivity. For example, look at the below data.

In A5 cell, we have a formula which is taking the range of cells from A1 to 4.

So A5 cell is dependent on the above cells; if we copy and paste this cell to
other cells, we get this formula only not value.

So, we can use the paste special technique to paste as only values. So, copy
the cell A5 and place a cursor on the cell where you need to paste.

23|P a g e
 Introduction to IT

I have copied the cell A5 and placed the cursor on the C5 cell. Now press
shortcut key ALT + E + S open paste special dialogue box.

We have plenty of options under paste special features here. In this example, we
paste only the value, so select the Values” option “to paste as the only value.

24|P a g e
 Introduction to IT

So this will paste only the value from A5 cell to C5 cell.

Like this under paste special, we can all other techniques like “formulas,
formats” etc.

Insert Random Numbers

25|P a g e
 Introduction to IT
Excel has plenty of formulas and functions in its arsenal. If you want to insert
random numbers with an excel worksheet, then you can use a built-in function
called “RANDBETWEEN.”

For the first argument, supply the least number that you want to insert.

For the last argument, enter the highest number that you want to insert.

So now RANDBETWEEN function inserts numbers from 100 to 1000.

26|P a g e
 Introduction to IT

Insert Random Fraction Numbers

To insert random fraction numbers, which are greater than 0 and less than 1, use
the RAND function.

RAND function doesn’t have any parameters, so just close the bracket and
insert the function.

27|P a g e
 Introduction to IT

Insert Serial Numbers

If you want to insert serial numbers that are incremented by 1, then we can do
this by simply fill the series option. Enter the value 1 in any of the cells.

Now using Excel FILL Handle drag till the cell where you want to insert serial
numbers.

Now click on the “Auto-Fill” option and choose “Fill Series” to get the serial
numbers incremented by 1.

28|P a g e
 Introduction to IT

We get the following result.

Data Visualization

Data is the new oil and data visualization is also one of the most
powerful Microsoft Excel features. Sparkline is an amazing excel
feature. It is also called as a visualization tool of MS Excel that enables
people to perfectly visualize the overall trend of a set of values.

In short, spark lines are mini-graphs situated inside of cells. The following
example will show you spark lines.
29|P a g e
 Introduction to IT

To create the spark lines, follow these steps Select the range that contains the
data that you’ll plot. Now, go to Insert > spark lines > Select the type of
Sparkline you want (Line, Column, or Win/Loss). In the above example, we are
using lines. Check below image to see the result:

Transpose

The TRANSPOSE function allows users to a transposed range of cells. It

returns a horizontal range of cells when a vertical range is entered as an input.
Or a vertical range of cells is returned if a horizontal range of cells is entered as
an input. It comes under a Lookup/Reference Function of features of MS Excel.

Syntax: TRANSPOSE (range)

After entering function. Below image is showing us the result.

30|P a g e
Introduction to IT

31|P a g e