MS Excel 2013 guide
MS Excel 2013 गाइड
Formulas, functions, formatting, sorting, filters, charts and print settings in Excel 2013, the way efficiency tests ask for them.
The ribbon tabs in Office 2013 are FILE, HOME, INSERT, PAGE LAYOUT, FORMULAS, DATA, REVIEW, VIEW. Tabs in capitals such as TABLE TOOLS appear only when you click inside a table, chart or header. Some exams remove tabs or switch shortcuts off; your exam page lists its rules.
Formulasफ़ॉर्मूला
| To do this | Go to | Tip |
|---|---|---|
| Start a formula | Click the cell›type =›the formula›Enter | Every formula starts with =, for example =B3+C3. |
| Total with AutoSum | HOME›Editing›AutoSum | Check the range AutoSum guesses before pressing Enter. |
| Copy a formula down | Drag the fill handle (the small square at the bottom right of the cell) | Or double-click the fill handle to fill to the end of the data. |
| Fixed (absolute) reference | Type $ before the column and row: $B$12 | Press F4 while the cursor is on a reference to add the $ signs. |
| Percentage | Type =B3/C3›HOME›Number›Percent Style | Or multiply by 100 when the question asks for the number, not the % format. |
| Round a result | =ROUND(formula, 2) | The second number is the decimal places. |
Functions you are asked forफ़ंक्शन
| To do this | Go to | Tip |
|---|---|---|
| SUM: total | =SUM(B3:B12) | |
| AVERAGE: mean | =AVERAGE(B3:B12) | |
| MAX and MIN: highest, lowest | =MAX(B3:B12) / =MIN(B3:B12) | |
| COUNT: how many numbers | =COUNT(B3:B12) | COUNTA counts filled cells of any kind. |
| COUNTIF: count matching rows | =COUNTIF(D3:D12,"Pass") | Text conditions go in double quotes. |
| SUMIF: total matching rows | =SUMIF(C3:C12,"Jaipur",E3:E12) | Condition range, condition, then the range to add. |
| IF: a result by a condition | =IF(F3>=40,"Pass","Fail") |
Formattingफ़ॉर्मेटिंग
| To do this | Go to | Tip |
|---|---|---|
| Merge and centre a title | HOME›Alignment›Merge & Center | Select the title cells across the table width first. |
| Wrap text | HOME›Alignment›Wrap Text | |
| Cell alignment | HOME›Alignment›align buttons | |
| Borders | HOME›Font›Borders›All Borders | Thick Box Border for an outside border only. |
| Fill colour | HOME›Font›Fill Color | |
| Rupee, comma and decimal formats | HOME›Number›Number Format / Comma Style / Increase Decimal | More Number Formats > Currency picks the ₹ symbol. |
| Column width and row height | HOME›Cells›Format›Column Width / Row Height | AutoFit Column Width fits the longest entry. |
| Conditional formatting | HOME›Styles›Conditional Formatting›Highlight Cells Rules | Greater Than, Less Than, Between, Text that Contains. |
| Cell styles | HOME›Styles›Cell Styles |
Sort, filter and sheetsसॉर्ट, फ़िल्टर और शीट
| To do this | Go to | Tip |
|---|---|---|
| Sort | HOME›Editing›Sort & Filter›Custom Sort | Tick My data has headers; pick the column and the order. Also on DATA > Sort & Filter. |
| Filter | HOME›Editing›Sort & Filter›Filter | Use the arrow in a heading to choose what to show. |
| Freeze the heading row | VIEW›Window›Freeze Panes›Freeze Top Row | Freeze Panes freezes above and left of the selected cell. |
| Rename a sheet | Right-click the sheet tab›Rename | Or HOME > Cells > Format > Rename Sheet. |
| Insert a sheet | The + button after the sheet tabs | Or HOME > Cells > Insert > Insert Sheet. |
| Sheet tab colour | HOME›Cells›Format›Tab Color |
Chartsचार्ट
| To do this | Go to | Tip |
|---|---|---|
| Insert a chart | Select the data›INSERT›Charts›Column / Pie / Line / Bar | Select the headings with the numbers so the chart gets its labels. |
| Chart title | CHART TOOLS DESIGN›Chart Layouts›Add Chart Element›Chart Title | Click the title box and type the exact text. |
| Data labels and legend | CHART TOOLS DESIGN›Add Chart Element›Data Labels / Legend | |
| Change chart type | CHART TOOLS DESIGN›Type›Change Chart Type | |
| Chart style | CHART TOOLS DESIGN›Chart Styles |
Page setup and printingपेज सेटअप और प्रिंट
| To do this | Go to | Tip |
|---|---|---|
| Orientation, margins, paper size | PAGE LAYOUT›Page Setup›Orientation / Margins / Size | |
| Print area | Select the cells›PAGE LAYOUT›Page Setup›Print Area›Set Print Area | |
| Repeat heading rows on every page | PAGE LAYOUT›Page Setup›Print Titles›Rows to repeat at top | |
| Fit to one page | PAGE LAYOUT›Scale to Fit›Width: 1 page | |
| Header and footer | INSERT›Text›Header & Footer | Or PAGE LAYOUT > Page Setup dialog > Header/Footer tab. |
| Print gridlines | PAGE LAYOUT›Sheet Options›Gridlines›Print |
Now practise it.
194 MS Excel papers are waiting, marked question by question.