• Practice
  • About 22 min
  • 5 practice questions

MS Word and MS Excel practical for accounting

Carry out the textbook's MS Word tasks and shortcuts, and use Excel's statistical, text, logical and financial functions (including SLN, PMT and RATE) and charts on accounting data.

If something looks wrong, please tell us.

By the end of this lesson you can

  • Create, save, format and lay out a Word document using menus and keyboard shortcuts.
  • Use AVERAGE, MAX, MIN, COUNT, COUNTA, COUNTIF, CONCATENATE, UPPER, LOWER, AND, OR and IF in Excel.
  • Calculate depreciation with SLN and a loan instalment with PMT, and present data in a chart.

The unit ends with practical work in two general purpose programs from MS Office. The steps below are written the way the Tamil Nadu textbook gives them. Menu names and their positions differ between versions of Office (newer versions use a ribbon, and some commands sit under different tabs), so if a step does not match your screen, look for the command of the same name.

MS Word: the main tasks

MS Word tasks given in the textbook
TaskSteps or shortcut (as in the textbook; versions differ)
Create a fileStart, All Programs, Microsoft Office, Microsoft Word; then File, Save As, give a file name, Save
Open a fileFile, Open, choose the file, Open
Save an existing fileFile, Save, or Ctrl+S
Close a fileFile, Close
Insert a tableInsert, Table, choose the number of rows and columns
Bold, italic, underlineCtrl+B, Ctrl+I, Ctrl+U
Align left, right, centre, justifyCtrl+L, Ctrl+R, Ctrl+E, Ctrl+J
Line spacing single, double, 1.5Ctrl+1, Ctrl+2, Ctrl+5
Page layoutMargins (normal, narrow, wide), orientation (portrait, landscape), size (A4, A5), columns
Page numbersInsert, Page Number, choose top or bottom of the page

Worked example 1: formatting a short note in Word

Practice text (written for this lesson): A trial balance is a list of ledger balances. It checks the arithmetical accuracy of the books. Task: make trial balance bold, arithmetical accuracy italic, underline ledger, justify the paragraph and set 1.5 line spacing. Steps: type the text; select trial balance and press Ctrl+B; select arithmetical accuracy and press Ctrl+I; select ledger and press Ctrl+U; select the whole paragraph and press Ctrl+J, then Ctrl+5; save with Ctrl+S.

MS Excel: functions in the textbook

Excel functions covered in the Tamil Nadu textbook
GroupFunctionWhat it returnsForm
StatisticalAVERAGEThe average=AVERAGE(range)
StatisticalMAX / MINThe largest / smallest value=MAX(range), =MIN(range)
StatisticalCOUNTNumber of cells containing numbers=COUNT(range)
StatisticalCOUNTANumber of cells that are not empty=COUNTA(range)
StatisticalCOUNTIFNumber of cells meeting a condition=COUNTIF(range,criteria)
TextCONCATENATESeveral texts joined into one=CONCATENATE(text1,text2)
TextUPPER / LOWERText in capitals / small letters=UPPER(text), =LOWER(text)
LogicalANDTRUE only if all conditions are true=AND(test1,test2)
LogicalORTRUE if any one condition is true=OR(test1,test2)
LogicalIFOne value if a test is true, another if false=IF(test,value_if_true,value_if_false)
FinancialSLNStraight line depreciation for one period=SLN(cost,salvage,life)
FinancialPMTPeriodic payment on a loan=PMT(rate,nper,pv,fv,type)
FinancialRATEInterest rate per period of a loan=RATE(nper,pmt,pv,fv,type)

Worked example 2: statistical and logical functions

Made-up data: daily cash sales of a shop for five days are entered in B2:F2 as 4,200; 3,800; 5,600; 2,900; 4,500. =AVERAGE(B2:F2) gives (4,200 + 3,800 + 5,600 + 2,900 + 4,500) ÷ 5 = 21,000 ÷ 5 = 4,200. =MAX(B2:F2) gives 5,600 and =MIN(B2:F2) gives 2,900. =COUNTIF(B2:F2,">4000") counts 4,200, 5,600 and 4,500 = 3. In G2, =IF(AVERAGE(B2:F2)>=4000,"Target met","Below target") shows Target met. =AND(B2>=3000,C2>=3000) is TRUE (4,200 and 3,800 are both at least 3,000); =AND(E2>=3000,F2>=3000) is FALSE because 2,900 is below 3,000, while =OR(E2>=3000,F2>=3000) is TRUE because 4,500 qualifies.

Worked example 3: SLN depreciation

Made-up data: a delivery van costs ₹4,80,000 plus ₹20,000 registration, so total cost = ₹5,00,000 (in Excel, =SUM of the two cells). Salvage value ₹50,000, life 5 years. =SLN(500000,50000,5) returns (5,00,000 − 50,000) ÷ 5 = ₹90,000 a year. Check: 90,000 × 5 = 4,50,000, and 4,50,000 + 50,000 = 5,00,000, the cost. As the textbook does, include installation and transport charges in the cost before applying SLN.

Worked example 4: loan instalment with PMT

Made-up data: a loan of ₹1,00,000 at 12% a year, repaid in 12 equal monthly instalments. Because payments are monthly, the rate per period is 12% ÷ 12 = 1%, and nper = 12. Enter the loan as a negative present value, as the textbook does: =PMT(12%/12,12,-100000,0,0) returns about ₹8,884.88 a month. Check: 12 × 8,884.88 = 1,06,618.56 repaid, so total interest is about ₹6,618.56, which is less than a full year's 12,000 on the whole loan, as it should be, since the balance falls each month. RATE works the other way: given nper, the payment and the loan, it returns the rate per period; multiply by 12 for a yearly rate when payments are monthly.

Charts

To draw a chart, as the textbook describes: enter the data, select the range including headings, go to the Insert menu and choose the chart type (column, line or pie), then right-click the chart and choose Add Data Labels; axis titles are added from the chart tools. A chart type can be changed later, for example from column to line. Use a column chart to compare amounts, a line chart to show change over time, and a pie chart to show shares of a whole.

Making a chart in Excel

  1. Enter data Labels and values
  2. Select range Include headings
  3. Insert chart Column, line or pie
  4. Add data labels Right-click the chart
  5. Name the axes From the chart tools

In PMT and RATE, match the rate to the payment period: monthly payments need the yearly rate ÷ 12.

Key takeaways: Word shortcuts save time (Ctrl+S save, Ctrl+B/I/U formatting, Ctrl+L/R/E/J alignment, Ctrl+1/2/5 spacing). Excel's statistical, text, logical and financial functions handle accounting data; SLN gives straight line depreciation, PMT a loan instalment and RATE a loan's interest rate. Menus differ between Office versions, but the commands keep their names.

Check your understanding

  1. 1. In MS Word, the shortcut to justify a paragraph is:

    1. Ctrl+J
    2. Ctrl+E
    3. Ctrl+L
    4. Ctrl+5
    Show answer

    Answer: A. Ctrl+J justifies, Ctrl+E centres, Ctrl+L aligns left, and Ctrl+5 sets 1.5 line spacing.

  2. 2. Made-up data: cells A1:A6 hold 120, 450, 'Cash', 980, blank, 1,250. What do =COUNT(A1:A6) and =COUNTA(A1:A6) return?

    1. 5 and 5
    2. 4 and 6
    3. 4 and 5
    4. 3 and 5
    Show answer

    Answer: C. COUNT counts cells with numbers: 120, 450, 980, 1,250 = 4. COUNTA counts non-empty cells: those four plus 'Cash' = 5; the blank cell is not counted.

  3. 3. Made-up data: a machine costs ₹1,20,000, salvage value ₹20,000, life 8 years. =SLN(120000,20000,8) returns:

    1. ₹15,000
    2. ₹17,500
    3. ₹10,000
    4. ₹12,500
    Show answer

    Answer: D. SLN = (cost − salvage) ÷ life = (1,20,000 − 20,000) ÷ 8 = 1,00,000 ÷ 8 = ₹12,500.

  4. 4. A cell holds 46. What does =IF(A1>=50,"Pass","Fail") show?

    1. Pass
    2. Fail
    3. TRUE
    4. 46
    Show answer

    Answer: B. 46 is not greater than or equal to 50, so the test is false and IF returns the second value, Fail.

  5. 5. For a loan repaid in monthly instalments at 9% a year, the rate argument in PMT should be:

    1. 9%
    2. 9%×12
    3. 9%/12
    4. 12/9%
    Show answer

    Answer: C. The rate must match the payment period. Monthly payments need the monthly rate, 9% ÷ 12 = 0.75%.

Privacy · Terms · Refunds · Delivery