Information and Communication Technology

(COMPUTER SKILLS IN NURSING PRACTICE)

(By Nomi Waqas Gul)

Chapter 4: Microsoft Excel

for Data Entry and Analysis

51 | P a g e
 Information and Communication Technology
(COMPUTER SKILLS IN NURSING PRACTICE)
(By Nomi Waqas Gul)

Chapter 4: Microsoft Excel for Data Entry

and Analysis
 Introduction to Spreadsheets
 Creating and Formatting Worksheets
 Entering and Editing Data (Numbers, Text)
 Basic Formulas and Functions (SUM, AVERAGE, COUNT)
 Creating Charts (Pie, Bar, Line)
 Data Entry Best Practices
 Examples: Patient Temperature Logs, Medicine Dosage Charts

1. Introduction to Spreadsheets
A spreadsheet is a digital tool used for organizing, analyzing, and storing data in tabular form. It
consists of a grid made up of rows (horizontal) and columns (vertical), where the intersection of
a row and a column forms a cell. Each cell can hold data such as text, numbers, or formulas.

Key Features of Spreadsheets

1. Data Organization
o Store information in a structured format.
o Use rows and columns for easy reference.
2. Formulas and Functions
o Perform automatic calculations (e.g., addition, averages, percentages).
o Built-in functions like SUM(), AVERAGE(), IF() make tasks easier.
3. Data Analysis
o Sort and filter data.
o Create pivot tables for summaries.
o Generate charts and graphs.
4. Automation
o Use formulas and conditional formatting to highlight trends.
o Advanced tools (like macros) can automate repetitive tasks.
5. Applications
o Business: budgeting, financial analysis, sales reports.
o Education: student grading, attendance records.
o Personal: expense tracking, schedules, to-do lists.

Examples of Spreadsheet Software

 Microsoft Excel (most widely used)

 Google Sheets (cloud-based collaboration)
 LibreOffice Calc (free, open-source option)

52 | P a g e
 Information and Communication Technology
(COMPUTER SKILLS IN NURSING PRACTICE)
(By Nomi Waqas Gul)

MS Excel Worksheet
An MS Excel worksheet is a single page (or sheet) inside an Excel workbook where you enter
and manage your data. A workbook can contain multiple worksheets, each acting like a separate
page in the file.

Structure of an Excel Worksheet

1. Rows and Columns

o Rows: Horizontal lines numbered 1, 2, 3... (up to 1,048,576 rows in modern
Excel).
o Columns: Vertical lines labeled A, B, C... (up to 16,384 columns, ending at
column XFD).
o The intersection of a row and column is a cell.
2. Cell
o Each cell has a unique address (e.g., B2 means column B, row 2).
o A cell can contain:
 Text (names, labels, comments).
 Numbers (data values).
 Formulas (=A1+B1).
 Functions (=SUM(A1:A5)).
3. Active Cell
o The currently selected cell where data will be entered.
o Highlighted by a bold border.

Important Elements in a Worksheet

 Worksheet Tabs: At the bottom, lets you switch between multiple sheets (Sheet1,
Sheet2, etc.).
 Name Box: Shows the active cell’s address.
 Formula Bar: Displays the content (data, formula, or function) of the active cell.
 Gridlines: Light gray lines separating rows and columns, making data easier to read.

Basic Operations You Can Do in a Worksheet

1. Data Entry → Enter numbers, text, or dates.

2. Formatting → Change font, color, alignment, borders, background.
3. Formulas & Functions → Perform calculations.
4. Data Management → Sort, filter, search.
5. Charts & Graphs → Visualize data.
6. Multiple Sheets → Use different worksheets in one file to organize large projects.

53 | P a g e
 Information and Communication Technology
(COMPUTER SKILLS IN NURSING PRACTICE)
(By Nomi Waqas Gul)

✅Example:

 If you are making a student marks sheet:

o Column A → Student names
o Column B → Math marks
o Column C → Science marks
o Column D → Total (using formula =B2+C2)

2. Creating and Formatting Worksheets

An Excel worksheet becomes more useful when you know how to create, organize, and format
it for clear presentation.

A. Creating a Worksheet

1. Open Excel → By default, you get a new blank workbook with one worksheet.
2. Add a New Worksheet
o Click the “+” icon next to existing sheet tabs at the bottom.
o Or use Shift + F11 to insert a new worksheet.
3. Rename a Worksheet
o Double-click on the sheet tab → type the new name.
o Example: “Sales_2025” instead of “Sheet1”.
4. Delete/Move/Copy Worksheets
o Right-click the sheet tab → select Delete, Move, or Copy.
o Useful for organizing data across multiple sheets.

B. Formatting a Worksheet

Formatting makes data clear, professional, and easy to read.

1. Cell Formatting

 Font Style/Size/Color → Bold, Italic, change text size.

 Cell Alignment → Left, Center, Right, Top, Middle.
 Borders & Shading → Add gridlines or background color.
 Number Formats → Display data as currency, percentage, date, decimal, etc.

2. Row and Column Formatting

 Adjust Column Width & Row Height → Double-click border between headers.
 Insert/Delete Rows or Columns → Right-click → Insert/Delete.
 Hide/Unhide Rows or Columns for cleaner data display.

54 | P a g e
 Information and Communication Technology
(COMPUTER SKILLS IN NURSING PRACTICE)
(By Nomi Waqas Gul)

3. Worksheet Formatting

 Themes & Styles → Use built-in styles for consistent formatting.

 Conditional Formatting → Highlight cells based on rules.
o Example: Highlight marks below 40 in red.
 Freeze Panes → Keep headers visible while scrolling.
 Page Layout → Set margins, orientation (portrait/landscape), and print area.

C. Example

Imagine you are creating a Monthly Expense Sheet:

 Step 1: Insert worksheet → Rename it “Expenses”.

 Step 2: In Column A → Enter categories (Food, Rent, Travel, etc.).
 Step 3: In Column B → Enter costs.
 Step 4: Format Column B as Currency.
 Step 5: Apply Bold & Borders to headings.
 Step 6: Use Conditional Formatting → highlight any expense above $500.

3. Entering and Editing Data (Numbers, Text)

A worksheet is only useful when you enter data into its cells. In Excel, data can be text,
numbers, dates, or formulas.

A. Entering Data

1. Text (Labels)
o Used for names, titles, categories.
o Example: “Student Name”, “Math Marks”.
o By default, text aligns to the left of a cell.
2. Numbers (Values)
o Used for calculations (marks, prices, salaries, etc.).
o Example: 85, 2500, 3.75.
o By default, numbers align to the right of a cell.
3. Dates & Times
o Excel recognizes dates/times as numeric values.
o Example: 29/08/2025, 10:30 AM.
4. Formulas & Functions
o Start with an = sign.
o Example: =B2+C2 (adds values of cells B2 and C2).

55 | P a g e
 Information and Communication Technology
(COMPUTER SKILLS IN NURSING PRACTICE)
(By Nomi Waqas Gul)

B. Editing Data

1. Overwrite Method
o Click the cell → type new data → press Enter.
2. Edit in Formula Bar
o Select the cell → make changes in the Formula Bar → press Enter.
3. In-Cell Editing
o Double-click the cell → edit directly inside it.
o Press Enter to save, or Esc to cancel.
4. Delete Data
o Select the cell → press Delete key.

C. Tips for Entering Data

 Use Tab to move to the next cell on the right.

 Use Enter to move down to the next cell.
 Use Arrow keys to navigate between cells.
 Use Ctrl + Z to undo mistakes.

D. Example

If you are making a Student Marks Sheet:

A B C
Student Math Science
Ali 80 70
Sara 90 85

 Enter names in column A (text).

 Enter marks in column B & C (numbers).
 Edit by double-clicking if you made a mistake (e.g., change Sara’s Math marks from 90
→ 95).

✅In short:

 Text = labels/titles.
 Numbers = values for calculations.
 Editing = overwrite, double-click, or use formula bar.

56 | P a g e
 Information and Communication Technology
(COMPUTER SKILLS IN NURSING PRACTICE)
(By Nomi Waqas Gul)

Basic Excel Formulas and Functions

✅Remember: Every formula in Excel starts with =.

A. Arithmetic Formulas (Manual Calculations)

1. Addition → =B2+C2
o Adds values in two or more cells.
2. Subtraction → =C2-B2
o Subtracts one value from another.
3. Multiplication → =B2*C2
o Multiplies values.
4. Division → =C2/B2
o Divides one value by another.
5. Exponents (Power) → =B2^2
o Square of B2 (or use any exponent).
6. Parentheses (Order of Operations) → =(B2+C2)*D2
o Always calculated first inside brackets.

B. Basic Statistical Functions

1. SUM()
o Adds numbers in a range.
o Example: =SUM(B2:B10)
2. AVERAGE()
o Finds mean (average) value.
o Example: =AVERAGE(B2:B10)
3. COUNT()
o Counts only numeric values.
o Example: =COUNT(B2:B10)
4. COUNTA()
o Counts all non-empty cells (text + numbers).
o Example: =COUNTA(A2:A10)
5. MIN()
o Finds the smallest number.
o Example: =MIN(B2:B10)
6. MAX()
o Finds the largest number.
o Example: =MAX(B2:B10)

57 | P a g e
 Information and Communication Technology
(COMPUTER SKILLS IN NURSING PRACTICE)
(By Nomi Waqas Gul)

C. Logical Functions
1. IF()
o Checks a condition and returns a value if TRUE or FALSE.
o Example: =IF(B2>=50,"Pass","Fail")
2. AND()
o Returns TRUE if all conditions are true.
o Example: =AND(B2>=50, C2>=50)
3. OR()
o Returns TRUE if any condition is true.
o Example: =OR(B2>=50, C2>=50)

D. Text Functions
1. CONCATENATE() / CONCAT()
o Joins text from different cells.
o Example: =CONCAT(A2," ",B2) → joins first name + last name.
2. LEFT()
o Extracts leftmost characters.
o Example: =LEFT(A2,3) → first 3 letters.
3. RIGHT()
o Extracts rightmost characters.
o Example: =RIGHT(A2,2) → last 2 letters.
4. LEN()
o Counts number of characters in a text.
o Example: =LEN(A2)

E. Date & Time Functions

1. TODAY()
o Shows current date.
o Example: =TODAY()
2. NOW()
o Shows current date & time.
o Example: =NOW()
3. DAY(), MONTH(), YEAR()
o Extracts day, month, or year from a date.
o Example: =YEAR(A2)

58 | P a g e
 Information and Communication Technology
(COMPUTER SKILLS IN NURSING PRACTICE)
(By Nomi Waqas Gul)

F. Lookup Functions (Basic)

1. VLOOKUP()
o Searches for a value in the first column and returns a related value.
o Example: =VLOOKUP(101,A2:C10,2,FALSE)
2. HLOOKUP()
o Same as VLOOKUP, but searches in the first row.

Example
Suppose you have this data:

Name Math Science

Ali 80 70
Sara 90 85
Umar 75 95

 Total Math Marks → =SUM(B2:B4) = 245

 Average Science Marks → =AVERAGE(C2:C4) = 83.3
 Highest Math Mark → =MAX(B2:B4) = 90
 Lowest Science Mark → =MIN(C2:C4) = 70
 Pass/Fail Check for Ali → =IF(B2>=50,"Pass","Fail") → Pass

✅In summary:

 Math formulas: +, −, ×, ÷
 Statistical: SUM, AVERAGE, COUNT, MIN, MAX
 Logical: IF, AND, OR
 Text: CONCAT, LEFT, RIGHT, LEN
 Date/Time: TODAY, NOW, YEAR
 Lookup: VLOOKUP, HLOOKUP

59 | P a g e
 Information and Communication Technology
(COMPUTER SKILLS IN NURSING PRACTICE)
(By Nomi Waqas Gul)

5. Creating Charts (Pie, Bar, Line)

Charts in Excel are visual representations of data. They make it easier to understand patterns,
comparisons, and trends.

A. Steps to Create a Chart

1. Enter Data in a worksheet (with labels and values).
2. Select Data Range → highlight the cells containing data.
3. Insert Chart
o Go to the Insert Tab → Charts group.
o Choose Pie, Bar, Line, etc.
4. Format the Chart
o Add chart title, axis titles, data labels.
o Change colors, styles, and layout.

B. Types of Basic Charts

1. Pie Chart

 Shows proportions/percentages of a whole.

 Best for one series of data (e.g., market share, expense categories).

Example:

Category Expense
Rent 500
Food 300
Travel 200

Pie chart shows each category as a slice of the circle.

2. Bar Chart (or Column Chart)

 Displays data with bars (vertical or horizontal).

 Best for comparing values across categories.

60 | P a g e
 Information and Communication Technology
(COMPUTER SKILLS IN NURSING PRACTICE)
(By Nomi Waqas Gul)

Example:

Student Marks
Ali 80
Sara 90
Umar 75

Bar chart compares student marks side by side.

3. Line Chart

 Displays data as points connected by lines.

 Best for showing trends over time (months, years, days).

Example:

Month Sales
Jan 500
Feb 700
Mar 900

Line chart shows how sales increase across months.

C. Chart Elements to Customize

 Chart Title → Name your chart.
 Axes → Show X-axis (categories) and Y-axis (values).
 Data Labels → Show exact values on bars/slices/points.
 Legend → Explains colors or symbols.
 Gridlines → Helps in reading values easily.

✅In short:

 Pie chart → shows parts of a whole.

 Bar/Column chart → compares categories.
 Line chart → shows trends over time.

61 | P a g e
 Information and Communication Technology
(COMPUTER SKILLS IN NURSING PRACTICE)
(By Nomi Waqas Gul)

6. Data Entry Best Practices

Entering data correctly is the foundation of a good spreadsheet. Poor entry leads to errors in
calculations, charts, and reports.

A. Organize Your Worksheet

1. Use Clear Headings
o Place column titles in the first row (e.g., Name, Marks, Date).
2. One Type of Data per Column
o Keep names in one column, numbers in another.
o Avoid mixing text and numbers in the same column.
3. No Blank Rows/Columns
o Makes sorting, filtering, and formulas easier.

B. Enter Data Correctly

1. Consistent Format
o Dates should all be in the same format (e.g., DD/MM/YYYY).
o Numbers should not have text mixed in (e.g., write 2500, not 2500 Rs).
2. Avoid Extra Spaces
o Use TRIM() function to clean unwanted spaces.
3. Use Drop-down Lists (Data Validation)
o Prevents spelling mistakes (e.g., “Male/Female” instead of multiple variations).

C. Use Excel Features to Save Time

1. AutoFill
o Drag the fill handle to copy data, extend series (Jan, Feb, Mar…).
2. Copy-Paste Special
o Paste only values, formats, or formulas as needed.
3. Freeze Panes
o Keep headers visible while scrolling.

D. Reduce Errors
1. Data Validation
o Restrict input (e.g., marks between 0–100).
2. Use Cell References in Formulas
o Don’t type numbers directly inside formulas.
3. Protect Worksheet
o Lock important cells so they can’t be edited by mistake.

62 | P a g e
 Information and Communication Technology
(COMPUTER SKILLS IN NURSING PRACTICE)
(By Nomi Waqas Gul)

E. Formatting for Readability

 Use bold headings and consistent font size.
 Apply number formatting (Currency, Percentage, Date).
 Use conditional formatting to highlight errors or trends.

✅Quick Example:
A Student Marks Sheet with good data entry:

Name Math Science Date

Ali 85 78 01/08/2025
Sara 92 88 01/08/2025

 Proper headings, no blanks, correct formats, clean layout.

7. Examples: Patient Temperature Logs,

Medicine Dosage Charts
A. Patient Temperature Log
Used by nurses/doctors to track patient’s daily temperature.

Date Time Patient Name Temperature (°C) Remarks

01/08/25 8:00 Ali Khan 37.2 Normal
01/08/25 14:00 Ali Khan 38.5 Fever Detected
02/08/25 8:00 Sara Ahmed 36.9 Normal
02/08/25 14:00 Sara Ahmed 37.8 Slight Fever

Features:

 Date/Time format → keeps records organized.

 Conditional Formatting → highlight temps ≥ 38°C in red.
 Chart Option → Line chart can show temperature trend over time.

63 | P a g e
 Information and Communication Technology
(COMPUTER SKILLS IN NURSING PRACTICE)
(By Nomi Waqas Gul)

B. Medicine Dosage Chart

Helps in scheduling and monitoring patient medication.

Patient Dosage Start

Medicine Frequency End Date Remarks
Name (mg) Date
Ali Khan Paracetamol 500 Every 6 hours 01/08/25 03/08/25 For fever
Antibiotic
Sara Ahmed Amoxicillin 250 Every 8 hours 01/08/25 07/08/25
course
Every 12
Umar Farid Ibuprofen 400 02/08/25 04/08/25 Pain relief
hours

Features:

 Consistent units (mg, ml, etc.) → avoids confusion.

 Data Validation → drop-down list for “Frequency” (Every 6 hours, 8 hours, 12 hours).
 Conditional Formatting → highlight if End Date is today (reminder).

✅Why these examples are useful in Excel:

 Organize medical records clearly.

 Automate reminders (using conditional formatting or formulas).
 Create charts for trends (fever monitoring, medicine usage).

💊Example 2: Medicine Dosage Chart

A Medicine Dosage Chart helps nurses, doctors, or caregivers track what medicines patients
take, at what dose, and at what time.

Sample Layout in Excel

Patient Dosage Start End Next Dose
Medicine Frequency Remarks
Name (mg/ml) Date Date Due
Every 6 01/09/25
Ali Khan Paracetamol 500 mg 01/09/25 03/09/25 For fever
hours 14:00
Sara Every 8 01/09/25 Antibiotic
Amoxicillin 250 mg 01/09/25 07/09/25
Ahmed hours 16:00 course
Umar Every 12 02/09/25
Ibuprofen 400 mg 02/09/25 04/09/25 Pain relief
Farid hours 20:00

64 | P a g e
 Information and Communication Technology
(COMPUTER SKILLS IN NURSING PRACTICE)
(By Nomi Waqas Gul)

Best Practices for Medicine Chart

1. Standard Units
o Always record dosage in mg or ml.
2. Clear Frequency
o Use dropdown lists for common timings: Every 6 hours, Every 8 hours, Every 12
hours.
3. Dates Tracking
o Record Start Date and End Date for course completion.
4. Next Dose Due
o You can calculate it automatically using formulas:
o Example: =StartDate + TIME(6,0,0) → adds 6 hours to start time for next
dose.
5. Conditional Formatting
o Highlight rows if End Date = TODAY() → reminder to stop medicine.
o Highlight missed doses in red.

Benefits of Using Excel for Dosage Chart

 Prevents overdose or missed doses.
 Tracks multiple patients in one file.
 Can generate printable reports for hospital records.
 Can integrate with charts (e.g., total medicine usage per week).

65 | P a g e
 Information and Communication Technology
(COMPUTER SKILLS IN NURSING PRACTICE)
(By Nomi Waqas Gul)

⌨️Most Common D. Worksheet & Workbook

Management
Excel Short Keys
 Shift + F11 → Insert new worksheet
 Ctrl + Page Up → Switch to
A. File & Workbook previous worksheet
 Ctrl + Page Down → Switch to next
 Ctrl + N → New workbook
worksheet
 Ctrl + O → Open workbook
 Alt + = → AutoSum
 Ctrl + S → Save workbook
 F12 → Save As
 Ctrl + P → Print E. Formatting
 Ctrl + W → Close workbook
 Ctrl + F4 → Exit Excel  Ctrl + 1 → Format Cells dialog box
 Ctrl + B → Bold
 Ctrl + I → Italic
B. Editing  Ctrl + U → Underline
 Alt + Enter → Line break inside a
 Ctrl + C → Copy
cell
 Ctrl + X → Cut
 Ctrl + Shift + $ → Apply Currency
 Ctrl + V → Paste
format
 Ctrl + Z → Undo
 Ctrl + Shift + % → Apply
 Ctrl + Y → Redo
Percentage format
 Delete → Clear cell contents
 Ctrl + Shift + # → Apply Date
 F2 → Edit active cell
format
 Ctrl + D → Fill down
 Ctrl + R → Fill right
F. Search & Replace
C. Selection & Navigation  Ctrl + F → Find
 Ctrl + H → Replace
 Arrow Keys → Move one cell
 Ctrl + Arrow Keys → Jump to edge
of data region G. Useful Extras
 Shift + Arrow Keys → Extend
selection by one cell  Ctrl + T → Create table
 Ctrl + Shift + Arrow Keys →  Ctrl + ; → Insert current date
Select entire range of data  Ctrl + Shift + : → Insert current
 Ctrl + Space → Select entire time
column  F4 → Repeat last action
 Shift + Space → Select entire row  Ctrl + Shift + L → Apply/Remove
 Ctrl + A → Select all filters

✅ Tip: Learning just the top 10 shortcuts

(Ctrl + C, V, Z, A, S, F2, F4, Ctrl + Space,
Shift + Space, Alt + =) will already save you
lots of time.

66 | P a g e
 Information and Communication Technology
(COMPUTER SKILLS IN NURSING PRACTICE)
(By Nomi Waqas Gul)

7. To add all the values in a column,

MCQs – Microsoft Excel for Data which formula is used?
Entry and Analysis A. =AVG()
B. =SUM()
1. Which of the following is the C. =COUNT()
default file extension for Excel D. =ADD()
workbooks? ✅Correct Answer: B
A. .docx 8. The function =AVERAGE(A1:A5)
B. .xlsx will return:
C. .pptx A. Total of A1 to A5
D. .txt B. Number of cells in range
✅Correct Answer: B C. The middle value
2. Which of these is a spreadsheet D. Average of values in A1 to A5
application? ✅Correct Answer: D
A. Microsoft Word 9. Which chart type is best for
B. Notepad showing proportions?
C. Microsoft Excel A. Line Chart
D. Adobe Reader B. Bar Chart
✅Correct Answer: C C. Pie Chart
3. What is a worksheet in Excel? D. Column Chart
A. A document with text only ✅Correct Answer: C
B. A file extension 10. To count how many cells contain
C. A single page in an Excel file numbers, which function is used?
D. A tool for printing pages A. =SUM
✅Correct Answer: C B. =MAX
4. What does the cell reference "B2" C. =COUNT
refer to? D. =MIN
A. Column B, Row 2 ✅Correct Answer: C
B. Row B, Column 2 11. To format data as bold in Excel,
C. Box 2, Batch B which shortcut is used?
D. Button 2 A. Ctrl + I
✅Correct Answer: A B. Ctrl + U
5. Which key is used to move to the C. Ctrl + B
next cell on the right in Excel? D. Ctrl + E
A. Enter ✅Correct Answer: C
B. Tab 12. What does a formula in Excel
C. Shift always begin with?
D. Esc A. $
✅Correct Answer: B B. =
6. Which tab is used to insert charts C. *
in Excel? D. @
A. Page Layout ✅Correct Answer: B
B. Data 13. Which of the following is an
C. Insert example of good data entry
D. View practice?
✅Correct Answer: C A. Leaving empty cells randomly

67 | P a g e
 Information and Communication Technology
(COMPUTER SKILLS IN NURSING PRACTICE)
(By Nomi Waqas Gul)

B. Mixing text and numbers in the A. Pie chart

same column B. Line chart
C. Using clear labels and consistent C. Bar chart
formats D. Scatter chart
D. Using different fonts for each ✅Correct Answer: B
entry 18. In Excel, merging cells is
✅Correct Answer: C commonly used for:
14. In patient temperature logs, what A. Hiding data
is the typical unit used in Excel? B. Deleting rows
A. Kilograms C. Creating titles across columns
B. Degrees Celsius (°C) D. Entering formulas
C. Grams ✅Correct Answer: C
D. Liters 19. Which key combination allows you
✅Correct Answer: B to edit the content of a selected
15. Which function can be used to cell?
calculate average patient A. F2
temperature? B. Ctrl + E
A. =COUNT() C. Ctrl + Shift
B. =SUM() D. Esc
C. =MAX() ✅Correct Answer: A
D. =AVERAGE() 20. Medicine dosage charts in Excel
✅Correct Answer: D help nurses to:
16. The formula =SUM(A1:A3) will: A. Play games
A. Count the number of cells B. Track patient diets
B. Display the largest number C. Schedule surgeries
C. Add values from A1 to A3 D. Accurately record and monitor
D. Find the average dosage timings and amounts
✅Correct Answer: C ✅Correct Answer: D
17. Which chart is most suitable to
track patient temperature over
time?

End of Chapter 4

68 | P a g e