EXCEL

Workbook: Workbook consist of sheets (sheets are also called as spreadsheet

or worksheet)
Excel: It is made of columns (in alphabets) and rows (in number)
Cell: The intersec on of column and row is called cell
Cell represented as C5 like that where C is the column and 5 is the row. (in
name box)
Range: It is the collec ons of cells that are generally grouped together.
It is represented as D4:J14 (which is D4 through J4 where through is
represented as : )
LAYOUT
It is in the tabs like home tab, insert tab ….
These tabs are on the top of the excel
When you click on the tab it opens the ribbon suppose if you click the Formula
tab it opens the formula ribbon.
Each ribbon is divided into groups. And groups have launch bu on which opens
more op ons. The launch bu on is also called Dialog launch bu on.
It has slide bar for moving up and down and also has a bo om slide bar zoom
in and out.
It also has views. Mostly everyone works with the normal view.
Name box that represents the cell posi on.
Formula bar
To save click file then save.

Working with Excel

Select to affect
 Enter numbers 1,2,3,4 which is like a pa ern then use fill or autofill. Drag
to fill numbers.
 Click on cell – When you start typing text it replaces everything.
  Click in cell – Where you need to double click so that you only change
where needed.
Moving
 Enter – to move down
 Shi + Enter - to move up
 Tab – to move le
 Shi + Tab – to move le

To clear anything select the range and click clear contents in clear in
edi ng
Insert
 Right click column for which before you want to inset than click insert
 Right click on the row before for which a er you want to insert than click
insert
 To adjust column, we simply drag but to get it formally and properly.
Select all those columns and then double click in between 2 columns

Replace

To replace one, all, to find go to find and search in edi ng group.

Or click ctrl+H.
Auto Correct
To make auto correc ons enabled. Go to file  more  op ons 
proofingauto correct op ons  adjust some se ngs which are required and
you can also add some replacements like if I type Tq it replaces with thank you


Then in cell type tq and enter  it gives thank you visit again.

Copy and paste

 ctrl+c and ctrl+v
 Ctrl+x and ctrl+v
 Select on cell or range you want you want to copy hover over edges than
pulse with edge arrows appear then move the cursor to another posi on
to past it.
You can simply ctrl+z to undo.
Formulas
 To write formulas in excel use = in cell
Example: =18/5
To write the formula that references 2 or more columns then use
Example: =e2*f2
 Then you can drag it by using autofill handler or you can simply double
click at cell bo om right corner.
 You type the formula or simply select the related cells.
 And when you click on the column then it shows the formula in
formula bar and in the cell
 The columns are call reference columns like e2*f2, e3*f2, e4*f4…
 Wri ng formulas using absolute reference
Example: e2*$H$1
 In H1 the fixed value is stored and if use simple e2*H1 then
remaining it will display 0 as it uses reference like e2*h1 and
then e3*h2, e4*h3 ………………. as h2, h3 are empty it gives 0.
 Here $ is used for absolute reference.
 So, use $H$1 to always refer the same fixed cell value.
 If we use any incorrect calcula ons, it gives use error.
 We can also add names to columns or cell or range you want.
 To give it name. Select the range then in name box type
the name you want and then click enter.
 Then instead of $H$1 you give the name of cell.

Func ons
Func ons in excel are sum, average, max, min, count.
In our example, if we want the sum of hours works, hourly wage ..etc.
 Then, use =sum(D2:J15) this gives the sum
 Or u can select those range a er wri ng =sum(dragging..)
 Or u can use the name you have given in the name box for the range like
=sum(taxOwned)
 Finally, to apply for remaining rows use the auto fill handle.
Forma ng Numbers, Cells, Rows and Columns
Forma ng Numbers:
Generally the numbers are in normal number format to make it in the
form of currency like dollar, rupees.
1. Select the en re column (like column c on the top columns)
2. Then select the General in numbers group in home tab.
3. Then from the drop-down menu select the currency or accoun ng
currency.
Forma ng Columns:
Select the column then
1. Select the forma ng like bold, alignment …...
Forma ng cells:
2. To copy the forma ng to the cell
3. Select the cell you want to copy
4. Then select the copy forma er in the clipboard.
5. And then select the cell (you want to copy to).
6. To paste to mul ple cells (double click the copy forma er in the
clipboard).

Add Auto Format to the top banner for easy access

V  change popular commands to all commands  then select auto format  Add  ok
Charts
To create charts in Excel.
1. Select the Data.
2. Then enter  Alt+fn+f1 OR Alt+f1.
3. Then chart design tab appears at top.
4. Then change the Chart design and then add charts elements.
We can use other design op ons and format the chart.

Print and Publishing Op ons

When the data in spreadsheet is ready for prin ng and publishing once check
whether the data is fi ed in the page. To do this
Prin ng
1. Go to file  print. Check the data is fi ed in page or not.
2. If not go to Page Layout lab  orienta on  And change it to Landscape
if its required
3. Then you go for prin ng. File  print.
Even if you are not sa sfied with the print.
1. Go to view tab.
2. Select the Page break view and verify.
3. If the right-side line was cu ng the graph or data then move the line to
most right (simply adjust).
4. And again, switch back to normal view
Now print
1. File  Print
2. Adjust the number of copies, printer, and se ngs (print ac ve sheet,
print en re work book, print selec on).
3. Number of pages (like 1 to 1, 1 to 2) and print the sheet on one page
4. Adjust the changes according to your requirement.
5. Now click Print. (And select the loca on where you want to save your pdf
and give it a name).
Save As
You can select save as and then change different file type based on the type
of file you want to change (like html, pdf…)
Publish
To publish your sheet simply, select the share at top right corner. And select
one Driver and you can also a ach the workbook or Pdf.

INTERMEDIATE IN EXCEL
Managing a Large Spread Sheet
To Zoom the spread sheet
Go to the view tab  then to Zoom group there you can use the op ons
1. Zoom (Choose op ons or custom or fit to selec on)
2. 100% Zoom (Easy zoom handle)
3. Zoom to Selec on (Select a part and the choose this)
4. You can also use status bar controls for zooming. (Which is at bo om
right corner).

Split
It is used to view different columns and rows side by side.

If the split bu on does not appear. Click the customize quick access toolbar.
1. Customize quick access toolbar.
[Link] more commands
3. Then, add split in quick access tollbar and in customize ribbon
4. Select all commands in choose commands from
5. Then search for split then click add and ok.
[Link] to Customize Ribbon repeat step 4 and 5. (Before adding split add a
split group.
Three ways to split
1. Split Horizontal: Click the row from which you want to split then select
the split bu on.
2. Split Ver cal: Click the column from which you want to split then select
the split bu on.
3. Split Horizontal and ver cal: Click the cell then select the split bu on.
The split occurs from above the cell and le to the cell.
 To adjust the split click on the split and drag.
To remove split. Either select the split bu on or double click on the
split line.
 Freeze pane
When we scroll down we miss which column the cell belongs to. And
similarly when we move right we miss which row the cell belongs to so for
that we has an op on called freeze pane.

1. It is in view tab in windows group.

2. Select freeze top row to fix top row.
3. Select freeze first column to fix first column fix.
To fix more than one column fixed select the cell next that column and
select freeze pane
4. To fix row and column select the cell next to it and select the freeze
pane.

Add name and reorder worksheet

Worksheet is also known as spread sheet.
1. Click + sign to add worksheet
2. Select and drag to move the spread sheet
3. Double click to rename the spread sheet
4. Right-click on it to add colour and other proper es.
Naming cells formulas and Constants
1. Select the cell → type a name in the Name Box (which is top le above
your column name A) → press Enter.
2. To name the formulas, Select the cell which has formula  go to
formulas tab  select define name  give it name , scope and value.
Give it name, scope, comments and refers to so that when you click =(name
you have given ) in any cell it displays =
3. To add any new formula go to formulas tab  select define name  give
it name , scope and value.

Date and Time

For date in cell click  ctrl + ;
For me in cell click  ctrl + shi + ;

Working with mul ple worksheets

1. Moving between worksheets  ctrl +pgUp and ctrl + pgDn
2. Edi ng same cell in mul ple worksheets (for con nues worksheet)
Select shi then select the sheets or furthest sheet up to which you want
to change  release shi  change the cell (you can see mul ple sheets
have been affected.
3. Edi ng same cell in mul ple worksheets (for random worksheet)
4. Opening in mul ple windows
View tab  windows group  new window
On top of window you can see 2
5. Move data from one sheet to other sheet
 copy and paste using ctrl+c and ctrl+v
 Or you can right-click on sheet and select move or copy  to
another work book Or sheet.  select create a copy and then click ok.
6. Linking worksheet: when you do step 5 then you have linked the both
worksheets, so you can refer anything from wkb 2 to wkb 1
7. Crea ng a summary worksheet

Working with Data

Grouping Data
To group the data in excel
1. Select the groups which might be columns or rows
2. Then go to Data Tab  Outline group  Click it and select the group
3. Then you observe 2 numbers which are 1 and 2 at top-le of row and
column start and 2 signs (+,-) at top or le , 1 (or - ) for collapse 2(or +)
for Expand.

4. To ungroup again select the Ungroup in outline tab then select row or
column and ok.
Impor ng data into Excel
Go to Data tab  Get Data  There are op ons like from file, from
database and etc  Select approximate and select the data you want to
import
Hyperlink to Another file
 1. Enter Text in the Cell
2. Then select the insert tab then  Select the link in it then add a
webpage or email or file .

ADVANCED CHART SKILLS

Crea ng a New Chart
1. Select the data for which you want to create a chart.
2. Click on the Insert tab  in charts group select the recommended charts

Customizing the chart

A er crea ng a chart 2 new tabs appear which are chart design and
format
In this we can change the colours, chart style, change chart style,
quick layout.
 Adding and Edi ng chart tles
Select add chart element to
1. Chart tle
2. Axis tle
3. Grid lines
4. Legend
5. Data labels
6. Axes
 You can also use format tab, to add text, word art and etc customiza ons.

If you want to change the y-axis or x-axis values. Then right-click on the values
then click on format axis Then change.

Adding Graphics and Textboxes to charts

1. Go to insert tab  go to illustra ons group  select the add picture and
add.
2. To add text boxes Go to insert tab  go to illustra ons group  select
shapes and ad text boxes.
 Adding WordArt to a Sheet
Go to insert tab  go to Text group  Select the word art to add and
customize it.

Adding SmartArt to a Sheet

Go to insert tab  go to illustra ons group  select the SmartArt to add
and customize it. These are like cycle, list, process, hierarchy.

WORKING WITH DATA LISTS

Using Data Forms to Add and Edit Records
First add the form bu on to quick acces toolbar using customize quick
access tool bar.

1. Click on any cell then select the form bu on in quick access tool bar.
2. Form dialog box open select new to add new record.
3. To search for any record. Click the criteria then enter the data you know
it will show the record.
4. And you can delete, scroll, find next, find prev.
This is the best way when there are a greater number of records.
Sor ng Data
You can sort in two places
1. In home tab  Edi ng group  Sort
2. In data tab Sort and filter group sort
3. Select any cell in name field or number value  Then select the sor
either a-z(asec) or z-a(desc)
4. Or you can select a custom sort. Where you can apply 2 levels of
sor ng.

Filter Data
You can filter in two places
5. In home tab  Edi ng group  Filter
6. In data tab Sort and filter group Filter
7. Select the first row and then select the filter bu on.
8. The drop-down arrows appear on every cell of top row.
9. Click it to customize the filter.
[Link] can deselect all and select want you want.
[Link] you want a custom filter to apply, Select number filter  Custom
filter. Then add equals, greater than… then value thena add a
condi on and apply same.
[Link] remove the filter condi ons, select the clear.
[Link] remove filter again select the filter.
DOCUMENTING AND AUDITING A WORKSHEET
Adding Comments and Notes
We can add comments
1. Select the cell to which you want to add comment.
2. Then go to Insert tab  Select comment
3. Or right-click on the selected cell  then click new comment.
4. You can add comment there; you can also set resolved or delete by
clicking three dots.
5. A purple mark indicates at the top of the cell.

We can add Notes

1. Select the cell to which you want to add comment.
2. Right-click on the cell  new note.
3. Add notes and you can also edit or delete notes.
4. A red mark indicates at the top of the cell.

Watch Window
If you want to keep an eye on a range of data while working on sheet, you can
add it to watch window.
1. Formulas tab  watch window add watch  select down arrow and
select the range  then click ok.

The watch window stays on the screen even if you move to other sheets
or workbooks.
2. You can remove it by again selec ng watch window op on.

Other Audi ng Features

Goto Formulas tab  in formula audi ng group
1. Select Trace precedents: To display the cells that the current cell depends
on
2. Select Trace dependent: To display the cells which are dependent on
current cell.
3. Remove arrows: To remove both trace precedent and dependent arrows
4. Click show formulas: To show the formulas
5. Error checking: Checks the en re sheet for errors
6. Error Checking drop-down  Trace Error : Select the cell which has error
and click the Trace Error to find what is the error.
7. Evaluate formula: select the cell with formula and click evaluate formula
to observe step by step evalua on of formula.
 ADDITIONAL PRINTING OPTIONS
Changing Margins and Orienta on
Goto Page Layout  Page Setup
Customize Margins, Orienta on, Size, Print Area, Breaks, Background, Print
Titles (In this, goto  sheets  set comments to print at end).
Then, go for prin ng.

PIVOT TABLE
Select the data you want to pivot and click ctrl+T.
It will give a pivot table and table styles and more op ons you can customize.
To create Pivot table
Goto Insert tab  pivot table

Click ok.
Then select the fields to create pivot table.

Then two tabs will appear which are pivotable Analyse and Design you can
customize them.
For number format  to add accoun ng for current currency.
VLOOKUP
It is like search a book in library.
Searching a name in phone book.
 Vlookup can only look right for looking up informa on, it cannot look le .
I has four parameters

1. Lookup_value: what we are searching for

2. Table_Array: where we need to search for
3. Col_index_num: which column the output is ( start from 1 in table array)
4. Range_lookup: true for approx. match and false for exact match.

XLOOKUP
 Same like vlookup. But Xlookup can look for informa on to it’s le and
right.
 The default is true for exact match.

1. Lookup_value: what we are searching for

2. lookup_Array: where we need to search for
3. return_Array:what we need to return as output
4. if not found:value to shown if not found
5. match_model 6. search_model