📋 Loksewa Preparation · Computer Operator
Electronic Spreadsheet
Complete notes covering MS Excel concepts, features, formulas, and operations — aligned with Nepal Lok Sewa Aayog Computer Operator exam syllabus.
📄 What is an Electronic Spreadsheet?
An Electronic Spreadsheet is a software application used for organizing, analyzing, and storing data in a tabular format. It consists of rows and columns forming a grid of cells where users can enter text, numbers, and formulas.
Common examples: Microsoft Excel, Google Sheets, LibreOffice Calc, and Apple Numbers.
Key Features
Data Entry & StorageAllows input of numerical and textual data.
Mathematical CalculationsSupports formulas and functions.
Data AnalysisSorting, filtering, and conditional formatting.
Graphical RepresentationCreates charts and graphs from data.
AutomationAutoSum, Macros, and Pivot Tables.
What-If AnalysisGoal Seek and Solver for scenario analysis.
🧩 Components of a Spreadsheet
| Component | Description |
|---|---|
| Cell | Basic unit where data is entered. Identified by address (e.g., A1, B3). |
| Row | Horizontal divisions labeled with numbers (1, 2, 3 …). |
| Column | Vertical divisions labeled with letters (A, B, C …). |
| Worksheet | A single spreadsheet page where data is entered and processed. |
| Workbook | A file containing one or more worksheets (e.g., an .xlsx file). |
| Workspace | The entire working area including menu bar, toolbars, and the grid. |
⚖️ Excel 2003 vs. Latest Versions (2016 / 2019 / 365)
| Feature | Excel 2003 | Excel 2016 / 2019 / 365 |
|---|---|---|
| File Format | .xls | .xlsx |
| Rows × Columns | 65,536 rows × 256 cols | 1,048,576 rows × 16,384 cols |
| Ribbon Interface | No (classic menus) | Yes (Ribbon with tabs) |
| Conditional Formatting | Limited | Advanced (color scales, data bars, icon sets) |
| Formula AutoComplete | No | Yes |
| Pivot Tables | Basic | Slicers & timeline filters |
| Data Analysis Tools | Basic | Power Query, Power Pivot, Forecasting |
| Cloud Integration | No | OneDrive, SharePoint, real-time collaboration |
📂 Workbook Operations
Creating a Workbook
- Open the spreadsheet application (MS Excel, Google Sheets, etc.)
- Click File → New for a blank workbook.
- Shortcut: Ctrl + N
Opening a Workbook
- Click File → Open and select the saved file.
- Shortcut: Ctrl + O
- Recent files are accessible from File → Recent Files.
Saving a Workbook
- File → Save As — choose location, name, and format (.xlsx, .xls, .csv).
- Shortcut: Ctrl + S
Page Setting
- File → Page Setup — adjust orientation, paper size, margins, and print area.
- Orientation options: Portrait or Landscape.
Print Preview & Printing
- Preview: File → Print Preview or Ctrl + P
- Print options: Print Active Sheets, Print Entire Workbook, or Print Selection.
🖥️ Spreadsheet Environment
Menu BarContains File, Edit, View, Insert, Format, Tools, Data, Window, Help menus.
Standard ToolbarNew, Open, Save, Print, Undo, Redo, Cut, Copy, Paste.
Formatting ToolbarText alignment, font style, color, number formatting.
Formula BarDisplays and allows editing of cell formulas/content.
Status BarShows mode (Ready/Edit), sum, average, count, macro recording status.
ScrollbarsVertical (up/down) and Horizontal (left/right) navigation.
✏️ Cell Operations
Editing Cell Contents
- Click cell and start typing, or double-click to modify existing content.
- Edit in Formula Bar, then press Enter.
- Shortcut: F2 to edit the selected cell directly.
Copying, Moving & Deleting
| Operation | Method |
|---|---|
| Copy | Ctrl+C → select destination → Ctrl+V |
| Move | Ctrl+X → select destination → Ctrl+V, or drag the cell |
| Delete Content | Select cell → Delete (keeps formatting) |
| Delete Cell | Right-click → Delete (shifts adjacent cells) |
| Clear All | Home → Clear → Clear All |
Formatting Cells
| Format Type | Details |
|---|---|
| Font | Bold Ctrl+B, Italic Ctrl+I, Underline Ctrl+U |
| Border | Home → Borders — Thick, Thin, Dotted, Double |
| Fill Color | Home → Fill Color, shortcut Alt+H+H |
| Alignment | Left, Center, Right; Top, Middle, Bottom; Merge & Center |
| Number Format | Currency, Percentage, Date, Time, Scientific — Home → Number Format |
| Protection | Review → Protect Sheet/Workbook to lock specific cells |
Formatting Rows, Columns & Sheets
- Row height: Home → Format → Row Height, or double-click row number to auto-fit.
- Column width: Home → Format → Column Width, or double-click column letter to auto-fit.
- Insert/Delete: Right-click row number or column letter → Insert/Delete.
- Rename sheet: Double-click the sheet tab at the bottom.
- Hide/Unhide sheet: Right-click sheet name → Hide/Unhide.
🔗 Cell References
Relative Reference
=A1+10
Default type. Changes when copied to another location. E.g., copying from B2 to C2 updates A1 → B1.
Absolute Reference
=$A$1+10
Uses $ to lock both row and column. Does not change when copied anywhere.
Mixed Reference
=$A1+10
Fixes either the row or the column. Column A is fixed; row number changes on copy.
⚙️ Formulas & Functions
Formulas always start with = and can include operators, cell references, functions, and constants.
Basic Arithmetic
| Operation | Formula | Description |
|---|---|---|
| Addition | =A1+B1 | Adds values in A1 and B1 |
| Subtraction | =A1-B1 | Subtracts B1 from A1 |
| Multiplication | =A1*B1 | Multiplies A1 by B1 |
| Division | =A1/B1 | Divides A1 by B1 |
| Exponentiation | =A1^B1 | A1 raised to the power of B1 |
Statistical Functions
| Function | Example | Description |
|---|---|---|
| SUM | =SUM(A1:A10) | Adds a range of numbers |
| AVERAGE | =AVERAGE(A1:A10) | Calculates mean of a range |
| MIN | =MIN(A1:A10) | Returns smallest value |
| MAX | =MAX(A1:A10) | Returns largest value |
| COUNT | =COUNT(A1:A10) | Counts numerical values |
| COUNTA | =COUNTA(A1:A10) | Counts non-empty cells |
| COUNTIF | =COUNTIF(A1:A10,">50") | Counts numbers matching a condition |
| IF | =IF(A1>50,"Pass","Fail") | Returns value based on condition |
Text Functions
| Function | Example | Description |
|---|---|---|
| LEFT | =LEFT(A1,3) | Extracts first 3 characters |
| RIGHT | =RIGHT(A1,2) | Extracts last 2 characters |
| MID | =MID(A1,2,3) | Extracts 3 characters from position 2 |
| LEN | =LEN(A1) | Returns total character count |
| TRIM | =TRIM(A1) | Removes extra spaces from text |
| CONCAT | =CONCAT(A1,B1) | Combines values of A1 and B1 |
Date & Time Functions
| Function | Example | Description |
|---|---|---|
| TODAY | =TODAY() | Returns current date |
| NOW | =NOW() | Returns current date and time |
| DAY | =DAY(A1) | Extracts day from a date |
| MONTH | =MONTH(A1) | Extracts month from a date |
| YEAR | =YEAR(A1) | Extracts year from a date |
Lookup & Reference Functions
| Function | Example | Description |
|---|---|---|
| VLOOKUP | =VLOOKUP(1001,A2:C10,2,FALSE) | Searches column A, returns value from column 2 |
| HLOOKUP | =HLOOKUP(50,A1:E2,2,FALSE) | Searches horizontally in a table |
| INDEX | =INDEX(A1:C3,2,3) | Returns value at row 2, column 3 |
| MATCH | =MATCH(50,A1:A10,0) | Finds position of 50 in A1:A10 |
Logical Functions
| Function | Example | Description |
|---|---|---|
| AND | =AND(A1>50,B1<100) | TRUE if both conditions are met |
| OR | =OR(A1>50,B1<100) | TRUE if at least one condition is met |
| NOT | =NOT(A1>50) | Reverses the logical result |
| IFERROR | =IFERROR(A1/B1,"Error!") | Returns custom text if formula produces an error |
| ISERROR | =ISERROR(A1/B1) | Returns TRUE if there is an error |
🔢 Series, Sorting & Filtering
Generating a Series (AutoFill)
- Type the first value in a cell (e.g., 1 or 01/01/2024).
- Click and drag the fill handle (small square at bottom-right of the selected cell).
- Excel auto-generates the series (1, 2, 3 … or Monday, Tuesday …).
For more control: Home → Fill → Series — choose type (Linear, Growth, Date), step value, and stop value.
Sorting Data
- Single column: Data → Sort → Sort A to Z or Z to A.
- Multiple columns: Data → Sort → Add Sort Levels (e.g., sort by Category, then by Price).
Filtering Data
- Select data range → Data → Filter → click dropdown arrows in headers to apply conditions.
- Advanced Filter: Data → Advanced — define criteria in a separate range; filter in place or copy to another location.
Subtotals
- Sort data by the category first, then go to Data → Subtotal.
- Choose grouping column and summary function (SUM, AVERAGE, COUNT).
- Use +/− buttons on the left to expand or collapse sections.
📊 Charts in Excel
Charts visually represent data, making it easier to analyze trends and comparisons.
Steps to Create a Chart
- Select the data (including labels) to include in the chart.
- Go to Insert → Charts and choose a chart type.
- Customize using the Chart Tools tab (titles, labels, legend, colors).
- Move or resize the chart by clicking and dragging.
Types of Charts
Column Chart
Compares values using vertical bars
Bar Chart
Similar to column but horizontal bars
Line Chart
Shows trends over time with lines
Pie Chart
Proportions as slices of a circle
Scatter (XY)
Relationships between two numeric variables
Area Chart
Like a line chart with filled area below
📝 Headers, Footers & Spell Check
Inserting a Header or Footer
- Go to Insert → Header & Footer.
- The worksheet switches to Page Layout View.
- Click on the Header or Footer section and type your content.
- Use built-in codes: page number &[Page], date &[Date], file name &[File], sheet name &[Tab].
To remove: Insert → Header & Footer → delete content in the area.
Spell Check
- Click any cell or select a range to check.
- Press F7 or go to Review → Spelling.
- In the dialog, choose Change, Ignore, or Add to Dictionary.
Note: Excel checks only text data in cells — it does not check formulas or numbers.
🔄 Importing & Exporting Data
Importing Data
- Go to the Data tab on the Ribbon.
- Click Get Data (or "Get External Data" in older versions).
- Choose the source: From Text/CSV, From Workbook, From Web, or From Database.
- Follow the import wizard and click Load.
Exporting Data
| Format | Extension | Use Case |
|---|---|---|
| Excel Workbook | .xlsx | Default Excel format |
| CSV | .csv | Plain text, comma-separated — for databases |
.pdf | Non-editable sharing format | |
| XML | .xml | Structured data for databases |
| OpenDocument | .ods | OpenOffice / LibreOffice Calc |
Steps: File → Save As → choose location → select format from dropdown → click Save. Use File → Export to create PDFs directly.
क Devanagari Font in Excel
Devanagari is the script used for Nepali, Hindi, Sanskrit, and other South Asian languages.
Common Devanagari Fonts
MangalDefault Devanagari Unicode font in Windows
KalimatiCommonly used for Nepali text
PreetiVery popular in Nepal
Kruti DevWidely used for Hindi typing
Typing Devanagari in Excel
- Enable Keyboard: Settings → Time & Language → Language → Add Hindi/Nepali → Select Devanagari Inscript or Preeti keyboard.
- Set Font: Change the Excel cell font to Mangal or Kalimati.
- Input Tool: Use Google Input Tools or Microsoft Indic Language Input Tool to convert English to Devanagari.
- Sorting: Excel supports sorting Devanagari text alphabetically just like English.
Tip: If characters don't appear correctly, ensure you are using a Unicode-supported font such as Mangal or Kalimati.