Learn EXCEL exercises with solutions

Excel Exercise Solution

Day 1

To start Excel 2010  go to Start ? All Programs ? Microsoft Office ? Microsoft Excel 2010

                                                                Or

Press Window +R  for Run Command and type Excel then click on OK or press Enter key

                                                                Or

If Desktop Shortcut is available, then double click on Excel icon

                                                                Or

Press window key form keyboard and type Excel then press Enter key.

Or

We can assign and use short-cut key to Open Excel Application                                    Or

We can pin in Taskbar / Start Menu and we can start from there by clicking.

1.      (a) Press CTRL +Navigation Key (Right)

(b)  Press CTRL + Navigation key (down)

(c)   Press F5 or Ctrl + G for go to command or go to Home tab ? Editing Group ? Find            ? Go to ? (it will launch a dialog box), enter the cell name in reference field ?                      press Enter Key or Clickon OK.

(d)  Type the Cell name in Name Box and press Enter Key.

(e)  In name box type A1 ? press Enter Key                  or         press CTRL+Home Key. 

2.      To prepare the given data sheet (Sales report) enters the values in respective cells ? apply formatting as required.

*   To merge cells select the cell range ? Home Tab ? Alignment group ? Merge & Centre

*   To set the Row height or Column width, select row/column ? Home tab ? Cells group  ?                  Format Command ? Row Height / Column Width ? provide value in points as  required.

*   For Bold select the cells or cells values ? press CTRL +B or CTRL+2 or Home Tab ? Font group

                                ? Bold

3.      To prepare the given data sheet (Student report) follow the above steps of point no. 2

4.      To rename the Sheet steps are-

Right click on Sheet name ? Rename ? type name as you want       or 

Double Click on Sheet name ? enter the name as you desired.

5.      Step 1: To create a folder in D:\STUDENTS location steps are-

 Start ? Computer ? D drive ? STUDENTS ? press CTRL+Shift+N to create a folder and type          folder name

                        Or

                                use New Folder command to create a folder and provide folder name

Step 2: Now come to Excel and go to File tab ? Save ? select Location ? type the file name as          given (Excel Practice) ?Save.

6.      Step 1: To open a file press CTRL +O  or  CTRL + F12 or File Tab ? Open ? specify file name ?       Open

Step 2: To navigate between Sheets you can directly click on sheet name or use keyboard shortcuts-

               CTRL+ Page Down             :              Next Sheet

               CTRL+ Page Up                   :              Previous Sheet

7.      To auto fit for Columns select the columns ? Home tab? Cells group ? Auto Fit Column 

                                Or

Double click between columns name.

8.      To merge the cells, select cell range ? Home tab ? Alignment Group ? Merge & Center

9.      To change or apply cell formatting select the cells ?Home tab ? Cells group ? Format ? Format Cells or press Ctrl+1 ? (it will launch a dialog box) select Category and related things. It will display Sample of selected category. ? Click on OK to apply the selected format (Refer the figure).

                CTRL + SHIFT + ~               :  General Format

                CTRL + SHIFT + 1               :  Number Format

                CTRL + SHIFT + 2               :  Time Format

                CTRL + SHIFT + 3               :  Date Format

                CTRL + SHIFT + 4               :  Currency Format

                CTRL + SHIFT + 5               :  Percentage Format

                CTRL + SHIFT + 6               :  Scientific Formats 

10.     Step 1: To insert a new sheet press Shift+F11 or right click on sheet tab ?Insert command.  

Step 2: Then prepare a data sheet as given (Employee Table).

Note: Make sure that the system date format is dd-mm-yyyy, otherwise enter the date values in          system date format only. To change Date format (like Location, Currency), go to Control Panel          ? Region and Language Settings

11.     To apply cell format as Date follow point no. 9.

12.     To apply cell format as Currency follow point no. 9

13.     To rename the Sheet follow “Day 1” point no. 4.

14.     Step 1: To apply border select the cells ? Home tab ? Font group ? border.

Step 2: To fill a pattern select cells and go to Home tab ?Cells group ? Format ? Cell Format ?          Fill ? select a pattern which you want to fill (refer the below figure).

15.     Step 1: To prepare a table style go to Home tab ?Styles group ? Format as Table ? New Table  Style (it will launch New Table Quick Style dialog Box). 

Step 2: Here specify the name for new table style and format it as given in exercise by clicking on          Format button available in same dialog box and save it.

16.     To apply a table style select the cell range and go to Home tab ? Format as Table ? Select your Style to apply.

17.     To apply the format painter steps are –

Step 1: Select the table employee ? Home tab ? Clipboard group ? click on Format Painter icon.

                                The moment you will click on Format Painter your Pointer will turn into Brush. 

Step 2: Now drag over the cells where you want to apply.

Note: (i) To apply format painter in multiple cells or cell ranges, double click on format painter(after  applied press ESC button).

          (ii) To clear styles select Student table and go to Home Tab ? Editing group ? Clear ? Clear

                                Formats.

18.     To clear the style, select the table and go to Design Tools Tab ? Table style group ? Clear

                                Or

Select  Go to Home Tab ? Editing group ? Clear ? Formats

19.     Step 1: To fill series, Enter initial value in  cell and select it.

Step 2: Home Tab ? Editing group ? Fill          ?Series ? Select Series in i.e.  Columns or Row ? set Step Value            and Stop Value as required ? OK. Note: For auto fill, just enter two values  and select cells, then using Fill handle drag it.

20.     Step 1: Select sheet Day 1 ? select the cells range which containing Online marks 

Step 2: Home tab ? Styles ? Conditional Formatting ?Highlight Cell Rules ? Greater Than ? specify the value then select Custom format  (it will launch Format Cell dialog box)

Step 3: Select formatting as required ? OK ? OK

21.     To apply conditional formatting steps are-

Step 1: select the cell range (as shown in below figure) ? Home tab  ? Styles group ?Conditional         Formatting ? New rules ? Use Formulae to determine which cell to format (refer to below         figure) 

                Step 2: Enter the formula with relative reference ? select Format as required ? OK ? OK 

22.     Create both tables as given.

23.     Type heading for table 5 as given.

24.     Step 1: Select Total sales values from table 4 and copy it. 

Step 2: Now select Total sales values from table 5? press ALT+CTRL+V for paste special command. Step 3: under Paste select Values as well as under Operation select Add operation ? OK. (Refer the below figure)

25.     To transpose steps are- 

Step 1: copy the table ? select location (any blank cell) for paste Step 2: press ALT+CTRL+V ? enable Transpose check box ? OK.

26.     To paste as a link steps are - select data and copy ? select the location ? now right click ? Paste Special ? Other paste options ? Paste Link

                                                                Or

Now right click ? Paste Option ?click on Paste Link icon.

Note:In case of link, if original data will change, linked data will auto update.

Day 2

1.   Step 1: Start Excel and open file excel practice ?Select sheet Day 1 

Step 2: Tocalculate difference between Actual Qty and Plan Qty use formulae (refer the below figure).

2.    Press Shift+F11 to insert new sheet and rename it as Day 2.

3.   Step 1: Prepare Commission sheet as given in exercise. 

Step 2: To calculate commission enter formulae as shown in below figure and fill the remaining. To use Absolute Reference Press F4

4.    To prepare cross table write formulae in Cell B2 as shown below and drag the same to fill the entire table.

5.    Go to sheet Day1 

a.    To calculate Sum of Actual Qty for Monitor Category use function  =SUMIF()   as shown in following figure-

b.    To count the number of Products use function  =COUNTA() as shown below -

                                                =COUNTA(A3:A9)

c.    To count the number of Monitors use function =COUNTIF() as shown below-

                                                =COUNTIF(B3:B9,"Monitor")

d.    To count the blank cells in given cell range use function =COUNTBLANK() as below syntax-

                                                =COUNTBLANK(cell Range)

6.    Prepare the table as given.

7.    To calculate total, average and Result refer the below figure

 a. For calculating total, use Sum() function as below

=SUM(C48:F48)               and drag the same till last record.

b.    For calculating average, use Average() function as below

=AVERAGE(C48:F48)      and drag the same till last record.

c.    For Rank calculation use the following syntax

                =RANK(G48,$G$48:$G$54)                       and drag the same till last record.

d.    For calculating Result, use IF(), And() function as below

                =IF(AND(C48>=40,D48>=40,E48>=40,F48>=40),"Pass","Fail")     and drag the same till last record.

8.    Prepare the table Number as given in exercise and then use functions to get result as shown in below figure-

Day 3

1.    Create the table as given.

2.    Step 1: To create the descriptive statistics, go to Excel Options ? Add-Ins ? Go ? Select Analysis

Tool Pack as shown below ? OK.

Step 2: Select the table   ? go to Data Tab    ? Analysis Group   ? Data Analysis ? select Descriptive analysis as shown below   ? OK.

Step 3: now provide input as shown in the dialog box and click on OK option.

Result will be as following-

3.    To calculate the employee table fields refer the following-

a.For Basic calculation-

=IF(AND(D2="HOD",E2="ITT"),45000,IF(AND(D2="HOD",NOT(E2="ITT")),35000,IF(AND(D

2="Staff",E2="ITT"),30000,23000)))

                        Or

=IF(AND(Designation="HOD",Dept="ITT"),45000,IF(AND(Designation="Staff",

Dept="ITT"),30000,IF(Designation="HOD",35000,23000)))

Note:In place of Designation and Dept please pass cell reference.b. For DA calculation-

=IF(D2="HOD",F2*0.15,F2*0.12)

c.    For TA calculation-

=IF(AND(D2="HOD",OR(E2="ITT",E2="ACCOUNTS")),F2*0.1,0)

d.    For Special allowance calculation-

=IF(AND(D2="HOD",C2<DATE(2010,1,1)),2500,0)

e. For Gross calculation - =sum(F2:I2)

               ?            For PF calculation -

      =(F2+G2)*12.5%

f.    For Annual Net Sal -

=(Gross – PF)*12         i.e.  =(J2-K2)*12

g.     For Tax calculation -

=IF(L2>1000000,(L2-1000000)*0.3+125000,IF(L2>500000,(L2500000)*0.2+25000,IF(L2>250000,(L2-250000)*0.1,0)))

                                          Or

=IF(L2<=250000,0,IF(L2<=500000,(L2-250000)*0.1,IF(L2<=1000000,(L2500000)*0.2+25000,(L2-1000000)*0.3+125000)))

4.    Go to sheet Day2 -Table 8 and to calculate  grade use the following syntax

=IF(AND(I48="Pass",H48>=60),"A Grade",IF(AND(I48="Pass",H48>=50),"B          Grade",IF(AND(I48="Pass",H48>=40),"C Grade","Nil")))

5.   Step 1: Insert a new sheet and rename it Sales-Report.

Step 2: Now make same heading as heading or field name given in sales report table.

                                The data in sheet Day 1 is as following-

Step 3: Now in sheet Sales-Report select Cell B2 and enter lookup function as shown in following

                                figure--

                Formula for Plan Qty. is                =VLOOKUP($A$2,$A$2:$E$9,4,FALSE)

Day 4

1.    Step 1: To open a new workbook press CTRL+N or go to File Tab ? New ?                   select Blank Workbook  ?Create

Step 2: Enter the data as given in exercise in Sheet1, Sheet2, and Sheet3. 

Step 3: To get consolidated report select a cell in Sheet4 (insert new sheet if required). Now follow the steps-

(i)      Data Tab ?Data tools group ? Consolidate 

(ii)       Now add references from Sheet1, Sheet2, and Sheet3 one by one ? 

                      check on check boxes for Top Row, Left column and Create Links to source data ? OK 

                       (refer the following figure). 

2.    To trace Dependents for Gross of third employee, select that cell and go to Formulae Tab ? Formulae Auditing Group ? Trace Dependents.

3.    To trace Precedents for Gross of third employee select that cell and go to Formulae Tab ? Formulae Auditing Group ? Trace Precedents.

4.    To remove formulae from a cell, copy the formulae and paste it as Value.

5.    To use Goal Seek follow the steps- Data ?What if Analysis ? Goal Seek

In Goal Seek Dialog Box do the following-

Set Cell:                       Select Net Salary cell of first employee

To Value:                     Enter a new value i.e. (current cell value +500)

By Changing Cell:     Select basic cell for first employee and then click on OK Button. It will calculate and                                    display the updated value.

Note: (i)       For using Goal Seek, To Value cell must be formulated including changing cell

        (ii)          By Changing Cell must be a constant (it should not be a formulae)

6.    Select entire table, copy it and go to new location (i.e. new sheet or new cell in the same sheet), then go to Paste Special ? select Option Values ? OK.

a.    To sort the records Employee name wise steps are- Select any Employee name Sort A to Z.

                                                                                Or

Select any Employee name ?Data tab ? Sort & Filter group ? click on   icon or Sort

b.    To sort the records Net salary wise steps are- 

Select any Net salary ? Home Tab ? Editing group ? Largest to Smallest.

Note: For multilevel sorting in excel, select the data and use custom sort command from editing group of Home Tab. It will launch Sort dialog box. Here select the fields in given order and also select the sorting order. If you are not getting Fields name then enable Check Box My data has headers.

c.    To calculate Dept wise Net salary do the following-

i.     Sort the data Dept wise 

ii.      Select the whole data with heading 

iii.    Use Subtotal function from Outline group of Data Tab.  iv.    At each changes: Dept

v.       Use function: Sum

vi.     Add subtotal to: Annual Net Sal

vii.      Now click on Ok button.

It will group the data and provide group wise sum with Grand Total for selected Field.

7.    Prepare the table Party Outstanding Reports as given in exercise and format it accordingly.

a.    To concatenate texts use concatenate function or & operator as shown in following figure.

b.    Copy the above table and paste it in new location and use text functions to convert in upper and proper casein new Location (Cell), Syntax is -

                        =PROPER(Text)                       or   =PROPER(Cell ref.)

                        =UPPER(Text)                          or   =UPPER(Cell ref.)

c.    To compare two texts use function Exact.      Syntax is:             =EXACT(text1,text2)

8.    For Data validation -

a)    To apply Data validation which accept only values, steps are-

Select cell range A1:A5 ? Data tab ?Data Tools group ? Data validation ? Setting ? 

Allow:          List 

Source:          CPT, PCC, IPCC, FINAL  ? Ok (refer the below figure). 

b)   Select cell range B1:B5 ? Data tab ?Data Tools group ? Data validation ? Setting ? 

Allow:         Whole number

Data:            Between

Minimum:  500

Maximum:  5000    ? Ok.

c)    Select cell range C1:C5 ? Data tab ? Data Tools group ? Data validation ? Setting ? 

Allow:          Date

Data:            Less than

End Date:  03/31/2013 (date should entered in date format)? OK.

d)   Select cell range D1:D5 ? Data tab ?Data Tools group ? Data validation ? Setting ?

Allow:          Text Length

Data:            equal to

Length:        5             ? OK.

9.    To analyze the data using Scenario Manager follow the steps-

Step1: Calculate the sales, Expenses and Profit as per given Expected rate. (Refer the formulae in                below figure to calculate).

Step2: Select the cells D2:E3 and Go to Data tab ? Data tools ? What If Analysis ? Scenario

                                Manager (it will launch a dialog box)

Step3: Now click on Add button 

Scenario name:                                best

Changing cells:                                  D3:E3 (for storing values as shown in below figure) ? OKà

Step4: Enter the new value for this scenario as given in exercise and click on Add button to add more  scenarios. 

Step 5: Repeat step 4 for Average and Bad ? OK.

                  Note:  (i)            To view the different result use show command from scenario manager dialog box

              (ii)           If required you can insert a summary of recorded scenario by using Summary       command from the same dialog box. It will insert the scenario Summary in new sheet. For                resultant cell select Expected in 2014 i.e. A7:B9 cell range. 

10.   Step 1: To calculate loan sheet prepare table as given in following figure-

Step 2: Now use financial functions as shown in below figure.

11.   To prepare a data table follow the steps-

Step 1: Enter the given data in excel sheet as below-

Step 2: Now calculate the EMI using PMT function in cell B6 i.e.

=PMT(B2/12,B3,-B1)

Step 3: Select the cell range B5:F6 and go to Data Tab ?

                Data tools group ? What if Analysis ? Data        Table. It will launch a Data table dialog box.  Step 4: Here for Row input cell: select cell B3 as shown in        figure and click on OK button.

Note:  A part of data table can’t be change.

12.   To find the date difference use =datedif() function. Refer the following figure-

The function syntax is :

=DATEDIF(FirstDate,SecondDate,"Interval")

            Where, FirstDate:         This is the earlier of the two dates.

                        SecondDate:     This is the more recent of the two dates.

                        "Interval”:         This indicates what you want to calculate.

            These are the available intervals.

Note: If required to display you can use concatenate function or operator to display as a sentence. Output will be as following-

13.   Please refer the above point no. 11.  

To calculate total cost, Syntax is

        =product(value1, value2, value3,…)

14.   To add a Data Form follow the steps-

Step 1: File ? Options ? Quick Access Toolbar ? Commands Not in Ribbon  ? Form ? Add>> ? OK

Step 2: Select employee data including Fields name ? Quick Access Toolbar ?Form (form will appear          like below figure). 

Step 3: Here using New command you can enter data into excel sheet. Using Data Form Deletion will          be permanent.

15.   Step 1: Go to Home tab ? Find & Select ? Formulae, (it will select all cells those contain formulae) Step 2:  Go to format cell dialog box by pressing CTRL+1 ?select protection Tab ?enable Hidden option? OK.

Note: it will be effective only after protecting sheet.

16.   To protect sheet go to Home tab ? Cells group ? Format ? Protect Sheet ? entre password              to unprotect (optional).

                                                                                Or

                Review Tab ? Changes Group ? Protect Sheet

17.   To protect entire workbook go to Review Tab ? Changes Group ? Protect workbook.

Note: Password is optional to unprotect the same.

Day 5

1.    To insert a Pivot table report steps are-

Step 1: Select data including headings? Insert Tab ? Table group ? Pivot Table. It will launch a dialog box as below figure.

Step 2: In the above dialog box data range already selected. Now specify the location where you want      to place the report ? OK

Step 3: Now from Pivot table field List you can choose fields which you want to add in report as shown  in above figure.

2.    To filter the records do the following steps-

                Step 1: Select  Heading  ? Home Tab  ? Sort & Filter ? Filter                      Or Data Tab ? Filter

                                It will add filter to all selected Fields.|

Step 2: click on Filter Button of product  ?  select Computer.

Step 3: click on Filter Button of Value ?  Number Filer ? set criteria 

                                                or

To apply Advance filter do the following-

Step 1:  go to Data Tab ? Advanced ?  it will launch Advance filter dialog box.

Step 2: Select List range (entire data) & criteria range as shown below ? OK

3.    (a)  To record a macro steps are-

 Step 1: Select given sheet ? View Tab ? Macros group ? Record Macro (It will launch record  macro dialog box)

                Step 2:  In this dialog box provide a macro name as well as set shortcut key as you desired  (make sure          that key has not been assigned earlier) ? OK

                Step 3: The moment you will click on OK button, recording will start. Now insert a Pie Chart for  given  data and then Stop recording. 

Note:     (i)             Macro will run in Macro enabled workbook only otherwise enable the related setting       from Options. It’s better to record a macro in new workbook and save it as a Macro                          enabled workbook (.xlsm).  

(ii)       To enable Macros steps are -

                                                File ? Options ? Trust Center ? Trust Center settings ? 

                                                                                                Macro Settings ? Enable all Macros ? OK

(iii)      While naming the macro take care of following points- ?            The name does not begin with a letter or an underscore

•      Space or other invalid characters are not allowed in name

•      The name conflicts with an Excel built-in name or the name of another object        in the workbook

(b)  To run macro steps are-

                                View Tab ? Macros ? View Macros ? select the Macro to run ? click on Run Button

                                                                                Or

                                you can press keyboard shortcut which is already assigned to that macro

                                                                                Or

                                Press CTRL+F8 to view macro ? now select the macro and click on Run Command.

4.    Step 1: Go to the “Day 1” worksheet in your workbook. 

Step 2: Select a cell from where you want to split it ? View Tab ? Window group ? Split (It will split window in multiple resizable Panes).

5.    To remove the split option, steps are-

                                 View Tab ? Window group ? click on Split icon.

6.    To apply Freeze Panes steps are-

Select cell as given ? View Tab ? Window group ? Freeze Panes ( It will freeze the above rows and left columns from selected cell).

7.    To unfreeze panes click on Freeze panes icon. It will unfreeze if already freeze.

8.    To insert Sparkline steps are-

Step 1: Select the output cell ? Insert Tab ? Sparkline Group ?use Line or Column any. 

Step 2: Now select the data range (all subject marks for individual) ? OK.

9.    Step 1: Select the Employee data ?Page Layout Tab ? Page Setup group ? Print area ? Set Print

Area 

Step 2: File ? Print          or press CTRL+F2 for print preview.

10.   To set header and footer in Excel steps are-

Step 1: Page Layout Tab ? Page Setup group ? launch this group. 

Step 2: In page seetup dialog box select Header/ Footer group ? Custom Header and set header as  given in exercise. Do the same for Footer also.

11.   To set paper size, orientation and margins you can use page setup window as shown in above figure of Point no. 10 of Day 5

                                                Or

Page Layout Tab ? Page Setup group ? select commands as required and set it. 

12.   Open a workbook containing some data.

a)    To repeat row / column in sheets for printing go to Page Layout Tab ? Page Setup group ? Print titles. Here select the row / columns to repeat.

b)   To fit contents in a single page if exceeds one or two row / columns for printing use command Print under file tab ? Scaling ? Fit sheet on One page (refer the below figure).

c)    To move or copy sheet in existing or other workbook steps are- Right click on sheet ?Moveor Copy ?select the Book where you want to place it as well as position ? OK  Note :It will Move the sheet. If you want to copy it then enable checkbox for Create a copy. 

d)   To search from entire workbook go to Home tab ? Editing group ? find & select ? Find

                                                                                Or

 Press CTRL+F ? Options ? Within : workbook.

e)   To work with comments in excel go to Review Tab ? Comments Group and use command as you required. To insert comment in cells you can right click and use command Insert comment.

f)     To insert multiple cells, select the cells and press CTRL++ and choose the direction for shift cell.

To insert multiple rows/columns select multiple row/column and press CTRL++

g)    To insert an object in worksheet go to Insert Tab ? Text group ? Object.

h)   To define a name select the range and type name in name box.

                                                                                Or

Select the range and go to Formulae Tab ? Define Names group ? Define name.

i)     To enable Track Changes in excel go to Review Tab ? Changes group ? Protect and Share workbook ? Enable the option: Sharing with track changes (refer the below figure).

j)     For multicolumn sorting use Custom Sort command from Sort & Filter of Editing group of Home tab.

Shortcut keys 

(a)    Function Keys 

(b)   SHIFT + Function Keys 

(c)    CTRL + Function Keys 

(d)   Other + Function    keys 

(e)    CTRL + Number Keys 

(f)  CTRL + Alphabet Keys 

(g)   Other Shortcuts 

(h)   Selecting data 

(i)  Extending data 

(j)  Formatting data 

All the very best J