MS Excel Basics
· Excel - Explore Window
·
Excel - Entering Values
·
Excel - Move Around
·
Excel - Save Workbook
·
Excel - Create Worksheet
·
Excel - Copy Worksheet
·
Excel - Hiding Worksheet
·
Excel - Delete Worksheet
·
Excel - Close Workbook
·
Excel - Open Workbook
Excel 2010 Tutorial
Explore Window
Microsoft Excel is a
commercial spreadsheet application, written and distributed by Microsoft for
Microsoft Windows and Mac OS X. At the time of writing this tutorial the
Microsoft excel version was 2010 for Microsoft Windows and 2011 for Mac OS X.
Microsoft Excel is a
spreadsheet tool capable of performing calculations, analyzing data and
integrating information from different programs.
By default, documents
saved in Excel 2010 are saved with the .xlsx extension whereas the file extension
of the prior Excel versions are .xls.
Audience
This tutorial has been
designed for computer users who would like to learn Microsoft Excel in easy and
simple steps. It will be highly useful for those learners who do not have prior
exposure to Microsoft applications.
Prerequisites
Before proceeding with
this tutorial, you should have a basic understanding of Computer peripherals
like mouse, keyboard, monitor, screen etc. and their basic operations. You
should also have the basic skills of file management and folder navigations.
Getting Started with
Excel 2010
This chapter teaches you
how to start an excel 2010 application in simple steps. Assuming you have
Microsoft Office 2010 installed in your PC, start the excel application
following the below mentioned steps in your PC.
Step 1 − Click on the Start button.
Step 2 − Click on All Programs option
from the menu.
Step 3 − Search for Microsoft Office from
the sub menu and click it.
Step 4 − Search for Microsoft Excel 2010 from
the submenu and click it.
This will launch the
Microsoft Excel 2010 application and you will see the following excel window.
Explore Window in Excel 2010
The following basic window appears when you
start the excel application. Let us now understand the various important parts
of this window.
File Tab
The File tab replaces the Office button from
Excel 2007. You can click it to check the Backstage view, where
you come when you need to open or save files, create new sheets, print a sheet,
and do other file-related operations.
Quick Access Toolbar
You will find this toolbar just above
the File tab and its purpose is to provide a convenient
resting place for the Excel's most frequently used commands. You can customize
this toolbar based on your comfort.
Ribbon
Ribbon contains commands organized in three
components −
·
Tabs − They appear across the top of the Ribbon and contain
groups of related commands. Home, Insert, Page Layout are the examples of
ribbon tabs.
·
Groups − They organize related commands; each group name appears
below the group on the Ribbon. For example, group of commands related to fonts
or group of commands related to alignment etc.
·
Commands − Commands appear within each group as mentioned above.
Title Bar
This lies in the middle and at the top of the
window. Title bar shows the program and the sheet titles.
Help
The Help Icon can be used to
get excel related help anytime you like. This provides nice tutorial on various
subjects related to excel.
Zoom Control
Zoom control lets you zoom in for a closer
look at your text. The zoom control consists of a slider that you can slide
left or right to zoom in or out. The + buttons can be clicked to increase or
decrease the zoom factor.
View Buttons
The group of three buttons located to the
left of the Zoom control, near the bottom of the screen, lets you switch among
excel's various sheet views.
·
Normal Layout view − This displays the page in normal view.
·
Page Layout view − This displays pages exactly as they will appear when
printed. This gives a full screen look of the document.
·
Page Break view − This shows a preview of where pages will break when
printed.
Sheet Area
The area where you enter data. The flashing
vertical bar is called the insertion point and it represents
the location where text will appear when you type.
Row Bar
Rows are numbered from 1 onwards and keeps on
increasing as you keep entering data. Maximum limit is 1,048,576 rows.
Column Bar
Columns are numbered from A onwards and keeps
on increasing as you keep entering data. After Z, it will start the series of
AA, AB and so on. Maximum limit is 16,384 columns.
Status Bar
This displays the current status of the
active cell in the worksheet. A cell can be in either of the fours states
(a) Ready mode which indicates that the worksheet is ready to
accept user inpu (b) Edit mode indicates that cell is editing
mode, if it is not activated the you can activate editing mode by
double-clicking on a cell (c) A cell enters into Enter mode
when a user types data into a cell (d) Point mode triggers
when a formula is being entered using a cell reference by mouse pointing or the
arrow keys on the keyboard.
Dialog Box Launcher
This appears as a very small arrow in the
lower-right corner of many groups on the Ribbon. Clicking this button opens a
dialog box or task pane that provides more options about the group.
BackStage View in Excel 2010
The Backstage view has been
introduced in Excel 2010 and acts as the central place for managing your
sheets. The backstage view helps in creating new sheets, saving and opening
sheets, printing and sharing sheets, and so on.
Getting to the Backstage View is easy. Just
click the File tab located in the upper-left corner of the
Excel Ribbon. If you already do not have any opened sheet then you will see a
window listing down all the recently opened sheets as follows −
If you already have an opened sheet then it
will display a window showing the details about the opened sheet as shown
below. Backstage view shows three columns when you select most of the available
options in the first column.
First column of the backstage view will have
the following options −
|
S.No. |
Option & Description |
|
1 |
Save If an existing sheet is opened, it would be
saved as is, otherwise it will display a dialogue box asking for the sheet
name. |
|
2 |
Save As A dialogue box will be displayed asking for
sheet name and sheet type. By default, it will save in sheet 2010 format with
extension .xlsx. |
|
3 |
Open This option is used to open an existing
excel sheet. |
|
4 |
Close This option is used to close an opened
sheet. |
|
5 |
Info This option displays the information about
the opened sheet. |
|
6 |
Recent This option lists down all the recently
opened sheets. |
|
7 |
New This option is used to open a new sheet. |
|
8 |
Print This option is used to print an opened
sheet. |
|
9 |
Save & Send This option saves an opened sheet and
displays options to send the sheet using email etc. |
|
10 |
Help You can use this option to get the required
help about excel 2010. |
|
11 |
Options Use this option to set various option
related to excel 2010. |
|
12 |
Exit Use this option to close the sheet and
exit. |
Sheet Information
When you click Info option
available in the first column, it displays the following information in the
second column of the backstage view −
·
Compatibility Mode − If the sheet is not a native excel 2007/2010 sheet, a
Convert button appears here, enabling you to easily update its format.
Otherwise, this category does not appear.
·
Permissions − You can use this option to protect the excel sheet. You
can set a password so that nobody can open your sheet, or you can lock the
sheet so that nobody can edit your sheet.
·
Prepare for Sharing − This section highlights important information you should
know about your sheet before you send it to others, such as a record of the
edits you made as you developed the sheet.
·
Versions − If the sheet has been saved several times, you may be able
to access previous versions of it from this section.
Sheet Properties
When you click Info option
available in the first column, it displays various properties in the third
column of the backstage view. These properties include sheet size, title, tags,
categories etc.
You can also edit various properties. Just
try to click on the property value and if property is editable, then it will
display a text box where you can add your text like title, tags, comments,
Author.
Exit Backstage View
It is simple to exit from the Backstage View.
Either click on the File tab or press the Esc button on the
keyboard to go back to excel working mode.
Entering Values in
Excel 2010
Entering values in excel
sheet is a child’s play and this chapter shows how to enter values in an excel
sheet. A new sheet is displayed by default when you open an excel sheet as
shown in the below screen shot.
Sheet area is the place
where you type your text. The flashing vertical bar is called the insertion
point and it represents the location where text will appear when you type. When
you click on a box then the box is highlighted. When you double click the box,
the flashing vertical bar appears and you can start entering your data.
So, just keep your mouse
cursor at the text insertion point and start typing whatever text you would
like to type. We have typed only two words "Hello Excel" as shown
below. The text appears to the left of the insertion point as you type.
There are following
three important points, which would help you while typing −
·
Press Tab to go to next
column.
·
Press Enter to go to
next row.
·
Press Alt + Enter to
enter a new line in the same column.
Move Around in Excel 2010
Excel provides a number of ways to move
around a sheet using the mouse and the keyboard.
First of all, let us create some sample text
before we proceed. Open a new excel sheet and type any data. We've shown a
sample data in the screenshot.
|
OrderDate |
Region |
Rep |
Item |
Units |
Unit Cost |
Total |
|
1/6/2010 |
East |
Jones |
Pencil |
95 |
1.99 |
189.05 |
|
1/23/2010 |
Central |
Kivell |
Binder |
50 |
19.99 |
999.5 |
|
2/9/2010 |
Central |
Jardine |
Pencil |
36 |
4.99 |
179.64 |
|
2/26/2010 |
Central |
Gill |
Pen |
27 |
19.99 |
539.73 |
|
3/15/2010 |
West |
Sorvino |
Pencil |
56 |
2.99 |
167.44 |
|
4/1/2010 |
East |
Jones |
Binder |
60 |
4.99 |
299.4 |
|
4/18/2010 |
Central |
Andrews |
Pencil |
75 |
1.99 |
149.25 |
|
5/5/2010 |
Central |
Jardine |
Pencil |
90 |
4.99 |
449.1 |
|
5/22/2010 |
West |
Thompson |
Pencil |
32 |
1.99 |
63.68 |
|
6/8/2010 |
East |
Jones |
Binder |
60 |
8.99 |
539.4 |
|
6/25/2010 |
Central |
Morgan |
Pencil |
90 |
4.99 |
449.1 |
|
7/12/2010 |
East |
Howard |
Binder |
29 |
1.99 |
57.71 |
|
7/29/2010 |
East |
Parent |
Binder |
81 |
19.99 |
1,619.19 |
|
8/15/2010 |
East |
Jones |
Pencil |
35 |
4.99 |
174.65 |
Moving with Mouse
You can easily move the insertion point by
clicking in your text anywhere on the screen. Sometime if the sheet is big then
you cannot see a place where you want to move. In such situations, you would
have to use the scroll bars, as shown in the following screen shot −
You can scroll your sheet by rolling your
mouse wheel, which is equivalent to clicking the up-arrow or down-arrow buttons
in the scroll bar.
Moving with Scroll Bars
As shown in the above screen capture, there
are two scroll bars: one for moving vertically within the sheet, and one for
moving horizontally. Using the vertical scroll bar, you may −
·
Move upward by one line by clicking the upward-pointing scroll
arrow.
·
Move downward by one line by clicking the downward-pointing scroll
arrow.
·
Move one next page, using next page button (footnote).
·
Move one previous page, using previous page button (footnote).
·
Use Browse Object button to move through the
sheet, going from one chosen object to the next.
Moving with Keyboard
The following keyboard commands, used for
moving around your sheet, also move the insertion point −
|
Keystroke |
Where the Insertion Point Moves |
|
|
Forward one box |
|
|
Back one box |
|
|
Up one box |
|
|
Down one box |
|
PageUp |
To the previous screen |
|
PageDown |
To the next screen |
|
Home |
To the beginning of the current screen |
|
End |
To the end of the current screen |
You can move box by box or sheet by sheet.
Now click in any box containing data in the sheet. You would have to hold down
the Ctrl key while pressing an arrow key, which moves the insertion point as
described here −
|
Key Combination |
Where the Insertion Point Moves |
|
Ctrl + |
To the last box containing data of the current row. |
|
Ctrl + |
To the first box containing data of the current row. |
|
Ctrl + |
To the first box containing data of the current column. |
|
Ctrl + |
To the last box containing data of the current column. |
|
Ctrl + PageUp |
To the sheet in the left of the current sheet. |
|
Ctrl + PageDown |
To the sheet in the right of the current sheet. |
|
Ctrl + Home |
To the beginning of the sheet. |
|
Ctrl + End |
To the end of the sheet. |
Moving with Go To Command
Press F5 key to use Go To
command, which will display a dialogue box where you will find various options
to reach to a particular box.
Normally, we use row and column number, for
example K5 and finally press Go To button.
Save Workbook in Excel 2010
Saving New Sheet
Once you are done with typing in your new
excel sheet, it is time to save your sheet/workbook to avoid losing work you
have done on an Excel sheet. Following are the steps to save an edited excel
sheet −
Step 1 − Click the File tab and
select Save As option.
Step 2 − Select a folder where you would like
to save the sheet, Enter file name, which you want to give to your sheet and
Select a Save as type, by default it is .xlsx format.
Step 3 − Finally, click on Save button
and your sheet will be saved with the entered name in the selected folder.
Saving New Changes
There may be a situation when you open an
existing sheet and edit it partially or completely, or even you would like to
save the changes in between editing of the sheet. If you want to save this
sheet with the same name, then you can use either of the following simple
options −
·
Just press Ctrl + S keys to save the changes.
·
Optionally, you can click on the floppy icon available at the top
left corner and just above the File tab. This option will also save
the changes.
·
You can also use third method to save the changes, which is
the Save option available just above the Save As option
as shown in the above screen capture.
If your sheet is new and it was never saved
so far, then with either of the three options, word would display you a
dialogue box to let you select a folder, and enter sheet name as explained in
case of saving new sheet.
Create Worksheet in Excel 2010
Creating New Worksheet
Three new blank sheets always open when you
start Microsoft Excel. Below steps explain you how to create a new worksheet if
you want to start another new worksheet while you are working on a worksheet,
or you closed an already opened worksheet and want to start a new worksheet.
Step 1 − Right Click the Sheet Name and
select Insert option.
Step 2 − Now you'll see the Insert dialog with
select Worksheet option as selected from the general tab.
Click the Ok button.
Now you should have your blank sheet as shown
below ready to start typing your text.
You can use a short cut to create a blank
sheet anytime. Try using the Shift+F11 keys and you will see a
new blank sheet similar to the above sheet is opened.
Copy Worksheet in Excel 2010
Copy Worksheet
First of all, let us create some sample text
before we proceed. Open a new excel sheet and type any data. We've shown a
sample data in the screenshot.
|
OrderDate |
Region |
Rep |
Item |
Units |
Unit Cost |
Total |
|
1/6/2010 |
East |
Jones |
Pencil |
95 |
1.99 |
189.05 |
|
1/23/2010 |
Central |
Kivell |
Binder |
50 |
19.99 |
999.5 |
|
2/9/2010 |
Central |
Jardine |
Pencil |
36 |
4.99 |
179.64 |
|
2/26/2010 |
Central |
Gill |
Pen |
27 |
19.99 |
539.73 |
|
3/15/2010 |
West |
Sorvino |
Pencil |
56 |
2.99 |
167.44 |
|
4/1/2010 |
East |
Jones |
Binder |
60 |
4.99 |
299.4 |
|
4/18/2010 |
Central |
Andrews |
Pencil |
75 |
1.99 |
149.25 |
|
5/5/2010 |
Central |
Jardine |
Pencil |
90 |
4.99 |
449.1 |
|
5/22/2010 |
West |
Thompson |
Pencil |
32 |
1.99 |
63.68 |
|
6/8/2010 |
East |
Jones |
Binder |
60 |
8.99 |
539.4 |
|
6/25/2010 |
Central |
Morgan |
Pencil |
90 |
4.99 |
449.1 |
|
7/12/2010 |
East |
Howard |
Binder |
29 |
1.99 |
57.71 |
|
7/29/2010 |
East |
Parent |
Binder |
81 |
19.99 |
1,619.19 |
|
8/15/2010 |
East |
Jones |
Pencil |
35 |
4.99 |
174.65 |
Here are the steps to copy an entire
worksheet.
Step 1 − Right Click the Sheet Name and
select the Move or Copy option.
Step 2 − Now you'll see the Move or Copy
dialog with select Worksheet option as selected from the
general tab. Click the Ok button.
Select Create a Copy Checkbox
to create a copy of the current sheet and Before sheet option as (move
to end) so that new sheet gets created at the end.
Press the Ok Button.
Now you should have your copied sheet as
shown below.
You can rename the sheet by double clicking
on it. On double click, the sheet name becomes editable. Enter any name say
Sheet5 and press Tab or Enter Key.
Hiding Worksheet in Excel 2010
Hiding Worksheet
Here is the step to hide a worksheet.
Step − Right Click the Sheet Name and select
the Hide option. Sheet will get hidden.
Unhiding Worksheet
Here are the steps to unhide a worksheet.
Step 1 − Right Click on any Sheet Name and
select the Unhide... option.
Step 2 − Select Sheet Name to
unhide in Unhide dialog to unhide the sheet.
Press the Ok Button.
Now you will have your hidden sheet back.
Delete Worksheet in Excel 2010
Delete Worksheet
Here is the step to delete a worksheet.
Step − Right Click the Sheet Name and select
the Delete option.
Sheet will get deleted if it is empty, otherwise
you'll see a confirmation message.
Press the Delete Button.
Now your worksheet will get deleted.
Close Workbook in Excel 2010
Close Workbook
Here are the steps to close a workbook.
Step 1 − Click the Close Button as
shown below.
You'll see a confirmation message to save the
workbook.
Step 2 − Press the Save Button
to save the workbook as we did in MS Excel - Save Workbook chapter.
Now your worksheet will get closed.
Open Workbook in Excel 2010
Let us see how to open workbook from excel in
the below mentioned steps.
Step 1 − Click the File Menu as
shown below. You can see the Open option in File Menu.
There are two more columns Recent workbooks
and Recent places, where you can see the recently opened workbooks and the
recent places from where workbooks are opened.
Step 2 − Clicking the Open Option will
open the browse dialog as shown below. Browse the directory and find the file
you need to open.
Step 3 − Once you select the workbook your
workbook will be opened as below −
Context Help in Excel 2010
MS Excel provides context sensitive help on
mouse over. To see context sensitive help for a particular Menu option, hover
the mouse over the option for some time. Then you can see the context sensitive
Help as shown below.
Getting More Help
For getting more help with MS Excel from
Microsoft you can press F1 or by File → Help → Support
→ Microsoft Office Help.
