2.1 Excel basics and data tables

Structure the surgery workbook and check its records

An analytical task starts with a question, the available information, and a required result. In Excel, you represent records in tables and implement calculations with formulas or queries. You can inspect an intermediate result and compare it with a small calculation made by hand.

The guided example uses synthetic operating-room records in Patients_working.xlsx. This chapter calls it the surgery workbook. No healthcare knowledge is required. Our task is to report the number and average registered duration of surgeries by surgery type and gender, then check those results.

TipWhy structure comes first

Rows with different meanings, numbers stored as text, or a data range that stops before newly added records can make a report look correct while using the wrong data. A well-structured workbook makes these problems visible before they reach a formula, PivotTable, or chart.

This chapter follows the preparation of the surgery workbook from the received file to a checked starting point.

In this chapter, you will:

2.1.1 Inspect the Excel interface

Use the desktop version of Microsoft 365 Excel. Follow the University installation instructions. The local CSV and JSON exercises use Power Query. Microsoft’s source matrix lists differences between Windows, macOS, and the web application.

Locate the worksheet grid, formula bar, Ribbon, and sheet tabs in Figure 1.

Excel interface showing the worksheet grid, formula bar, Ribbon, and sheet tabs
Figure 1: Excel worksheet, formula bar, Ribbon, and sheet tabs.

Use Home for formatting, Insert for Tables and charts, Formulas for calculation tools, and Data for sorting, filtering, and importing. The Ribbon Search box can locate a named command.

2.1.2 Save the surgery workbook

  1. Create a folder named Module_2_Excel, with subfolders data/raw, data/current, data/processed, workbooks, and reports.
  2. Download Patients.xlsx into data/raw. Preserve this received file unchanged.
  3. Open it in desktop Excel. Save a working copy as workbooks/Patients_working.xlsx.
  4. Keep the workbook open as you study. Save after each exercise.

If Protected View appears for the supplied workbook, select Enable Editing. Use File > Save As to save the working copy in workbooks.

Language settings

The instructions use English command and function names. In Windows, inspect the Office language under File > Options > Language. You may keep another display language; use Excel Help or Microsoft’s function documentation to find the corresponding local name. On macOS, use the corresponding Ribbon command when a Windows keyboard shortcut differs.

WarningCheck function and number separators

Excel uses your regional settings for decimal and list separators. A localized installation may use translated function names, a decimal comma, and semicolons between function arguments. The examples use English function names, a decimal point, and commas between arguments. If a copied formula fails, check the function tooltip and replace only the argument separators required by your installation. Keep the regional convention consistent when entering formulas and importing files.

2.1.3 Recognize the workbook objects

A workbook is an Excel file. A worksheet is one tab within it. A cell has an address such as G2, and a range contains cells such as G2:G2101. The formula bar shows the selected cell’s formula or stored value.

Inspect the status bar

Press Ctrl+N to open a temporary blank workbook.

Practise the status bar with a blank workbook:

  1. Enter 10, 20, and 30 in A1:A3.
  2. Select A1:A3. Confirm that Average, Count, and Sum appear at the bottom of the window.
  3. Right-click the status bar and enable Minimum and Maximum.
  4. Confirm the five values: average 20, count 3, sum 60, minimum 10, and maximum 30.

This is a fast inspection method, not a replacement for a documented formula. Close the temporary blank workbook without saving it, then return to Patients_working.xlsx.

2.1.4 State what one row represents

The row meaning, also called the table’s grain, states what one record represents. A repeated patient identifier can be valid in a table of visits. It would require investigation in a table intended to have one row per patient.

Use Table 1 to distinguish the records on the two worksheets.

Table 1: Row meanings and fields in the supplied worksheets.
Worksheet One row represents Fields used
consultation_history One recorded surgery Patient ID, Week, Day, OR, Gender, Age, Expected duration, Order of surgery, Surgery type, Registered duration
patient_data One person listed for the teaching exercise Patient name, Gender

The surgery table contains 2,100 records in columns A to J. Column K is a separate helper list of surgery codes. Keep it outside the Table. The name list has no patient-ID column, so row position is not evidence that a name belongs to a surgery record. Do not join those worksheets by row number.

Durations are in minutes. Patient ID, OR, and Surgery type identify or classify records; adding their values does not produce a meaningful measure. The first surgery record has Patient ID 70, expected duration 62.44 minutes, and registered duration 160.61 minutes.

Workbook contents and field types

The workbook contains these two worksheets:

Check the worksheet names and record counts against Table 2.

Table 2: Contents of the required workbook.
Worksheet Rows of data One row represents Use in this module
consultation_history 2,100 one recorded surgery Tables, formulas, charts, and PivotTables
patient_data 2,100 one person listed for the teaching exercise lookups and text functions

In consultation_history, columns A–J form the surgery dataset. Column K is a helper list containing the 27 surgery-type codes. The fields mean:

Use Table 3 to identify the meaning and expected type of each surgery field.

Table 3: Data dictionary for the main table.
Field Meaning Type
Patient ID identifier of the surgery record’s patient whole number
Week, Day teaching-period time fields whole number
OR operating-room code whole number
Gender category recorded for the exercise text
Age age in years whole number
Expected duration planned surgery duration in minutes decimal number
Order of surgery position in the operating-room sequence whole number
Surgery type coded procedure category text
Registered duration observed duration in minutes decimal number

The patient workbook contains two source tables used for different exercises:

Compare the grains and identifiers in Table 4 before considering a relationship between these tables.

Table 4: The row meaning and relationship of the patient workbook tables.
Worksheet Grain Identifier Purpose
patient_data One row per listed person No reliable identifier is supplied; names must be checked Practises lookups, ambiguity checks, and text functions
consultation_history One row per recorded surgery Patient ID in this teaching file Records surgery attributes and durations

The 2,100 Patient ID values in this teaching file are unique. Do not infer from the label alone that an identifier must be unique in every dataset: a genuine consultation-history table could contain several consultations for the same patient. The stated row meaning determines the duplicate rule.

NoteCheck the workbook before you continue

Before continuing, confirm that the workbook has the two original worksheet names, that cell A2 on consultation_history contains 70, and that the status bar reports 2,100 numeric cells when you select A2:A2101. Save.

2.1.5 Inspect the workbook and its data

The workbook contains two worksheets:

  • consultation_history: Patient consultation history (see Table 5).
  • patient_data: Name and gender of patients (see Table 6).
Table 5: Patient consultation history (Sheet consultation_history).
A B C D E F G H I J K
1 Patient ID Week Day OR Gender Age Expected duration Order of surgery Surgery type Registered duration Surgery types
2 70 0 1 1 male 83 62.44 13 WP174 160.61 AF241
3 1215 5 3 2 male 48 58.0 10 ID810 158.21 AX939
4 1439 6 4 0 male 61 58.0 10 ID810 151.68 BP285
5 502 2 1 2 male 43 58.0 11 ID810 142.66 CP913
6 1120 5 1 1 male 65 62.44 13 WP174 142.47 GV023
Table 6: Patient names and genders (Sheet patient_data).
A B
1 Patient name Gender
2 Leslie Fields male
3 Arnold Ross male
4 Craig Vega male
5 Doyle Banks Junior male
6 Herbert Freeman male

2.1.6 Practice cell entry and selection

Before changing the source order or adding Tables, make a practice copy of consultation_history. Right-click its worksheet tab, choose Move or Copy, select Create a copy, and keep the copy in Patients_working.xlsx. Rename the copied worksheet reference_practice. Confirm that A2 is 70 and that columns A to K match the received worksheet. Use this copy for the cell-entry, navigation, range-selection and reference-copy experiments. Leave the original worksheets unchanged during these experiments.

The sums of identifiers below demonstrate reference behavior. They are not measures used in the surgery report. Use P4 for the first AVERAGE formula.

Find the Freeze Panes command on the View Ribbon in Figure 2.

Excel View Ribbon with Freeze Panes
Figure 2: The View Ribbon contains the Freeze Panes commands.
TipKeep the active row and column visible

On current Microsoft 365 desktop and web builds, View > Focus Cell can highlight the active cell’s row and column. Use it while tracing a wide Table, then turn it off before making a screenshot or checking color-based formatting. Figure 2 shows its position beside the other viewing controls.

See Microsoft’s Focus Cell availability and controls.

2.1.7 Move quickly through a worksheet

Start on the sheet reference_practice unless an exercise explicitly tells you to use patient_data.

Cell addresses combine a column letter and row number. For example, B3 is the cell in column B and row 3.

  1. Select View > Freeze Panes > Freeze Top Row. A dark line appears below the first row. Scroll down and confirm that the column headings remain visible.

  2. To construct the first formula, select reference_practice!P4 and type =. When you continue typing, for example AVE, Excel displays a current list of matching functions beneath the cell. Use the function tooltip to inspect the expected arguments before completing the formula.

    Choose AVERAGE, and press tab. The remainder of the word will be completed by Excel, including the first parenthesis. Select numeric cells A2:A3, type ) to close the formula, and press Enter. The completed formula in reference_practice!P4 is =AVERAGE(A2:A3). Confirm 642.5.

    HINT: if you forgot to type the closing parenthesis ), Excel will autocomplete it for you, without asking. If parentheses are missing in more complex formulas, Excel will ask you how to complete the missing parentheses.

    WarningCase Sensitivity in Excel Formulas

    Excel formulas are not case sensitive (i.e., SUM is the same as sum). Excel will automatically format formula names with capitals.

  3. Practice selecting formula inputs with the keyboard. Follow the steps and inspect the active address after each move.

    • Select cell A2 (i.e., the first cell with data).

    • Press CTRL+ARROW DOWN (to jump to the last cell in the column).

    • Press CTRL+ARROW RIGHT (to jump to the last cell in the row).

    • Press CTRL+HOME to return to the beginning of the worksheet. Freezing the top row keeps the heading visible; check the active address before selecting the data.

    • Check the active cell before selecting the data. If it is A1, click A2.

    • Press CTRL+SHIFT+ARROW DOWN (to select the entire column A2:A2101).

    • Press CTRL+SHIFT+ARROW RIGHT (to select all data cells).

    • Press F5, enter L2, and choose OK to go to the formula destination without depending on intervening blank cells.

    • Here, type: =SUM(.

    • Now press CTRL+HOME. If Excel selects A1, click A2 before continuing. The cell content of cell L2 is now =SUM(A2.

    • Now press CTRL+SHIFT+ARROW DOWN. The cell content of cell L2 now is: =SUM(A2:A2101.

    • Close the formula: ), and press Enter. Cell L2 has now summed up A2:A2101.

    HINT: You can quickly jump to a cell with the “Go To” functionality. Press F5 to use this. For example, jump to cell A1000.

    If you were not familiar with these navigation key combinations, train yourself to use them “blindly” by maneuvering through the data as fast as you can (with or without selecting data). Exercise this, as you will experience later that this makes you a lot more efficient.

  4. You can use the mouse to select rows, columns, or ranges of cells.

    • Try clicking on a column letter to select that column.
    • Try clicking on a row number to select that row.
    • Hold CTRL while clicking on column A and then column D. Now you have selected two non-contiguous columns. Of course, this also works on rows.
    • Hold SHIFT while clicking on column B and then column E. Now you have selected all (contiguous) columns from B to E.
    • Click cell B2. Hold the mouse down, and drag it towards cell E5. Now you have selected all cells {B2,...B5,C2,...,C5,D2,...,D5,E2,...,E5}. In formulas, this is written as B2:E5.

2.1.8 Select ranges

A Range is a collection of cells, for example, A1:A10 (the first 10 cells of column A), or A1:E1 (the first 5 cells in row 1). A range is used in formulas such as: =SUM(A1:A100). When you reference a range on a different worksheet (for example, patient_data), the range is denoted as follows: patient_data!A1:A100.

Return to consultation_history before creating the source Tables below.

ImportantConfirm the supplied row meanings

Record these two statements before building a Table:

  • In consultation_history, one row represents one recorded surgery.
  • In patient_data, one row represents one person listed for the teaching exercise.

Use these supplied row meanings throughout the module. Do not combine the two record types into one Table.

2.1.9 Create an Excel Table

An Excel Table is a named object with headers, filters, and calculated columns. Its references can expand when records are added.

An Excel Table applies its formatting and calculated-column formulas to added records. Use its named columns to keep the records and calculations together as the dataset grows.

NoteOfficial Microsoft tutorial

Use Microsoft’s Create and format tables tutorial to see how Excel turns a selected range into a Table with headers and filters. The page includes an official video and written steps. Then return here to use the exact patient range, Table name, and checks below.

Figure 3 shows the named Consultations Table after the records have been converted.

Excel worksheet showing the named Consultations Table and the Table Design tab
Figure 3: The named Consultations Table with its Table Design controls visible.
  1. In consultation_history, select A1:J2101.
  2. Select Insert > Table (or press Ctrl+T), confirm My table has headers, and name it Consultations under Table Design > Table Name.
  3. Open the Gender header dropdown. Clear Select All, select female, and choose OK. Inspect the visible records, then reopen the dropdown and choose Clear Filter From Gender.
  4. Open the Registered duration dropdown and choose Sort Largest to Smallest. Sort the whole Table so each record stays together. Confirm that the largest value is 160.61 and belongs to Patient ID 70.
  5. Add a worksheet named control. Enter Surgery records in A1 and =ROWS(Consultations[Patient ID]) in B1. Confirm 2,100.

Test Table expansion without changing the received dataset. Copy the final data row and paste it into the first blank row directly below Consultations. Confirm that the Table expands and that the control count changes to 2,101. Right-click the added row, select Delete > Table Rows, and confirm that the Table and control count return to 2,100.

Keep a single header row, one field per column, and one record per row. Place titles, notes, helper lists, and report totals outside the source Table. Avoid merged cells and blank separator rows inside the records.

Create the patient-name Table

  1. On patient_data, select A1:B2101, create a second Table, and name it Patients.
  2. Freeze the top row on both worksheets.

2.1.10 Check types and data quality

Formatting changes how a value appears. It does not necessarily change its stored type. A number imported as text may be omitted from a numerical summary. An identifier such as 0017 may need to remain text to preserve its leading zeros. Dates need a stated interpretation when the source uses an ambiguous order such as 03/04/2026.

Before calculating, inspect missing values, unexpected categories, invalid numbers, and repeated records. State a rule before deleting anything. For example, two surgeries for one patient are different records when their event details differ. Import and clean data with Power Query develops a repeatable cleaning procedure.

A durable table follows a few strict rules:

  • use one header row with unique, descriptive field names;
  • store one value in each cell;
  • keep one meaning and one data type within each column;
  • avoid blank rows and columns inside the data;
  • do not merge cells inside a data table;
  • keep units in the header, such as duration_min, rather than in each cell;
  • use a stable identifier, such as patient_id, when records must be matched; and
  • separate source data from summaries and presentation.

Excel stores values as numbers, text, dates, times, Boolean values, errors, or blanks. Formatting changes how a value looks; it does not necessarily change what Excel stores.

For example, a date is stored as a number and displayed with a date format. An identifier such as 00127 should often remain text because arithmetic on it has no meaning and a numeric conversion may remove leading zeros.

Check each field before analysis:

  • Text: names, categories, codes, and identifiers.
  • Whole number: counts and integer quantities.
  • Decimal number: measurements and rates.
  • Date/time: calendar dates, timestamps, and durations represented consistently.
  • Logical: fields stored as TRUE or FALSE.

Categories such as male, female, and the surgery-type codes are text values. Control spelling with data validation when users enter categories manually.

WarningFormatting is not cleaning

Displaying 12 as 12.00 changes its appearance. Displaying the text "12 minutes" as a number does not make it numeric. Inspect and correct the stored value before calculating with it.

Inspect the records before building formulas. At minimum, check:

  • required identifiers, dates, categories, and measures for unexpected blanks;
  • identifiers that should be unique for duplicates;
  • categories against their allowed values;
  • dates and durations against plausible limits; and
  • related identifiers for unmatched records.

A blank can be valid only when the data rule permits it. The supplied surgery table contains no blanks in columns A–J; any blank there would therefore require investigation. Import and clean data with Power Query uses a cancelled record whose actual duration is deliberately blank. State the rule before counting blanks as errors, and keep received source data unchanged until every correction is recorded.

TipQuick Data Quality Checks

Before you start analyzing, do a few quick checks:

  • Are IDs unique? (Conditional Formatting → Highlight Cells Rules → Duplicate Values)
  • Are there missing values in key columns? (Filter for blanks, or use COUNTBLANK)
  • Are numbers stored as text? (look for green triangles / unexpected alignment)

Run the checks on the complete, unfiltered Consultations Table. In unused cells on baseline, label D6 Blank Patient IDs and enter =COUNTBLANK(Consultations[Patient ID]) in E6; expect 0. Label D7 Numeric registered durations and enter =COUNT(Consultations[Registered duration]) in E7; expect 2,100. Label D8 Blank received fields and enter =COUNTBLANK(Consultations[[Patient ID]:[Registered duration]]) in E8; expect 0.

Select the Patient ID data cells and choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. No Patient ID should be marked in this supplied file. A green triangle or unexpected alignment is a reason to inspect a stored value; the numeric count provides a separate check. Use Data > Clear to remove any inspection filters before recording the totals.

2.1.11 Format and navigate the worksheet

Use View > Freeze Panes to keep headers visible. Adjust column widths so headers remain readable, and show duration values with two decimal places. Retain the original precision for calculations. A displayed value of 62.4 can still be stored as 62.44.

Use Ctrl+Home to return to the start of a sheet and Ctrl+Arrow to move to a data boundary. Blank cells affect navigation and double-click AutoFill. Inspect the final populated row after filling a formula.

Conditional formatting draws attention to values that meet a rule. It does not remove or correct those values. Use labels as well as color so the meaning remains clear in a printed or grayscale view.

For new manual inputs, Data > Data Validation can restrict a field to a list or numeric range. Test a permitted value, an invalid value, and a blank. Validation does not replace checking imported data.

2.1.12 Practice number formats and worksheet layout

Create a blank worksheet named format_practice for the number-format experiments. Use unused cells on this worksheet for each example so that the received records and the baseline formulas remain unchanged.

2.1.13 Format cells without changing values

In Excel, the number format determines how a stored value is displayed. Open Format Cells by right-clicking the cell and choosing the command, or press Ctrl+1.

Type the number 3 in a cell and open Format Cells. If you select Currency, Excel displays the value using the currency symbol and decimal precision configured for your locale. A stored value of 0.3 can likewise be displayed as 30% by applying Percentage format. Formatting changes how a value is displayed; it does not change the underlying value.

2.1.14 Extend values and formulas with AutoFill

AutoFill copies values, formulas, or a recognized sequence. Drag the fill handle to choose the destination. Double-clicking it fills downward along the adjacent populated data and may stop at a gap.

  1. On a new worksheet, enter 1 in A1 and 2 in A2. Select both cells and drag their fill handle down to A50. Confirm that A50 contains 50. Enter =A1/2 in B1 and double-click its fill handle. Confirm that Excel fills the formula through B50.

  2. Delete the contents of cell A4 and B4. Put the formula =B1*2 in cell C1. Double-click on the black cross of cell C1. Now you can see that Excel stops to autofill cells when the previous column has an empty cell.

Autofill can also be used for months, years, dates, number series (like 1, 2, 3… or 2, 4, 6…. Or even Patient1, Patient2, ….).

2.1.15 AutoFit columns

Double-click the boundary between 2 column letters to AutoFit the selected column to its widest visible value. Select several columns first when they should all be fitted. Check the longest header and value after AutoFit.

Return to reference_practice for the dropdown and sorting practice below. Its original surgery-code helper list is still in K2:K28.

2.1.16 Restrict input with data validation

With data validation, you can set conditions for the values allowed in a cell. For example, you can create a dropdown list in each cell containing the appropriate value for that cell.

NoteOfficial Microsoft tutorials

Use Microsoft’s Apply data validation to cells tutorial to locate the List, Source, In-cell dropdown, and Error Alert settings. The page includes an official video and written steps. Microsoft’s Sort data in a range or table and Filter data in a range or table tutorials demonstrate the other commands used below.

  1. Create a dropdown cell for selecting a surgery type without overwriting the source data. Use the existing unique surgery-type list in K2:K28, select an unused input cell such as P2, and open Data → Data Validation. Under Allow, choose List; for Source, enter =$K$2:$K$28. Keep In-cell dropdown selected. On Error Alert, enable the error message and choose Stop. For this practice input, a blank means that no surgery type has been selected, so keep Ignore blank selected. Click OK. Select a valid code from the dropdown, then type NOT-A-CODE and confirm that the Stop alert rejects it. Cancel the invalid entry. Clear P2 to test the permitted blank, then select a valid code again.

Use the filtering method from Microsoft’s tutorial, then use the current Ribbon state shown in Figure 4 to locate the Table header controls.

Current Excel worksheet showing Table controls for sorting and filtering
Figure 4: The current Excel worksheet with Table controls ready for sorting and filtering.

Select A1:J2101 on reference_practice, then use Data > Filter to enable the header dropdowns. Keep the helper list in K and the practice inputs in P outside the selected range. Use the Registered duration header dropdown for the sort below. Clear the Age filter after the check.

  1. Sort Registered duration in ascending order, inspect the first five rows, and press Ctrl+Z once to restore the pre-sort row order. Then filter Age to show only values of 65 or higher, inspect the visible row count on the status bar, and clear the filter.

  2. On reference_practice, sort Registered duration from largest to smallest. Confirm that the first value is 160.61, then press Ctrl+Z once to restore the received row order.

  3. Open the Age dropdown and choose Number Filters > Greater Than Or Equal To. Enter 65 and choose OK. Select the visible Patient ID cells and confirm 591 records on the status bar. Reopen the Age dropdown and choose Clear Filter From Age. Confirm 2,100 records again.

  4. Select F2:F2101, choose Home > Conditional Formatting > New Rule > Format only cells that contain. Set Cell Value to greater than or equal to, enter 65, choose Format, and set the font to red and bold.

Return to consultation_history. Compare a one-decimal display with the two-decimal display used for the report.

  1. Apply a one-decimal number format to both duration columns. Formatting must change the display, not the stored values.

On format_practice, enter 2026-09-28 in an empty cell. Apply Short Date and then Long Date. The stored date remains the same. Use a date format for dates and a numeric format with units in the heading for durations.

Paste calculated results as values

Paste Values keeps the displayed results and removes the formulas that produced them. Use it only when the receiving range should hold a fixed snapshot.

Enter 10 and 20 in format_practice!A1:A2. Enter =A1*2 in B1 and fill the formula into B2. Copy B1:B2 and use Home > Paste > Values in C1:C2. Change A1 to 11. B1 changes to 22, while C1 remains 20 because C1 contains the pasted result rather than a formula. Restore A1 to 10.

Find and replace within a selected range

Enter Follow up, Follow up, and Intake in an unused 3-cell range on format_practice. Select only those cells and press Ctrl+H. Find Follow up, replace it with Follow-up, open Options, and select Match entire cell contents. Choose Replace All and confirm that Excel reports 2 replacements. Find and Replace changes stored values, so restrict the selected range and inspect the result before saving.

2.1.17 Record the baseline and final checks

Keep the existing control worksheet for analysis parameters and checks. Create a separate baseline worksheet for the original-workbook totals below; control!B3 will hold the duration allowance in Formulas, functions, and lookups.

  1. Create a worksheet named baseline. Enter the labels Consultation rows, Patient rows, Total registered duration, and Average registered duration in A2:A5. Enter these formulas in B2:B5: =ROWS(Consultations[Patient ID]), =ROWS(Patients[Patient name]), =SUM(Consultations[Registered duration]), and =AVERAGE(Consultations[Registered duration]).
  2. Check the results: 2,100 consultation rows, 2,100 patient rows, 138,376.27 total minutes, and 65.8935 average minutes. Format the 2 duration checks to 2 decimal places.
  3. Clear every filter. Confirm that both Tables contain 2,100 rows, the 27 surgery codes remain outside Consultations, and the received worksheets retain their original records.

2.1.18 Exercises

2.1.Q1 Add and remove a Table record

Copy the final data row of Consultations into the first row immediately below the Table. Record the new Table range and the result of =ROWS(Consultations[Patient ID]). Delete the added Table row and confirm that the range and count return to their original values. Explain why typing below a plain formatted range would not provide the same structural guarantee.

Confirm that Patients_working.xlsx contains both original worksheets with Tables named Consultations and Patients, plus reference_practice, control, baseline, and the practice worksheets you created. Keep the original A to K fields on reference_practice in their received order, with no active filters. Preserve reference_practice!P4 as the 642.5 average and keep the validation input in P2 separate. Restore the duration columns in consultation_history to two decimal places for the report. Check the 2,100-record total after clearing filters. Explain the difference between a worksheet and a Table, and between a repeated identifier and a duplicated record.

2.1.M1 Choose a durable table design

Which design is most suitable for a refreshable consultation table?

  • One worksheet per patient, with merged title cells
  • One row per consultation, one field per column, and one header row
  • One row per procedure, with all consultation dates in one cell
  • A visually formatted range containing blank separator rows

B. The design has a stated grain and rectangular fields. The other designs mix presentation with data structure or place several values in one cell.

2.1.M2 Interpret a repeated identifier

In a separate workbook, the identifier P014 occurs five times in Consultations and once in Patients. What is the best initial interpretation?

  • Every occurrence is a duplicate error.
  • P014 has five consultations and one patient record.
  • Excel has copied the identifier accidentally.
  • The two Tables must be merged into one row.

B. Repetition is expected in a one-to-many relationship. The grain of each Table determines whether repetition is valid.

Continue with checked calculations.