Chapter 2 — Spreadsheet – Functions and Charts

What is a spreadsheet?
A spreadsheet is a digital worksheet made of rows and columns that divide the page into many small boxes called cells. Each cell holds an item: a number, text, a date, or a formula that calculates a value. The position of a cell is written as a column letter plus a row number, for example A1 or C5. Worksheets are useful for organising lists, performing calculations automatically and showing results clearly.

Parts you will use:
The main parts include the grid of cells, column letters, row numbers, the formula bar (which shows the content of the current cell), and sheet tabs at the bottom for multiple pages. There are toolbars and menus to insert functions, format cells, and create charts. When you click a cell its address and content appear in the formula bar, so you can edit easily.

How spreadsheets store data:
Spreadsheets classify entries into types: numbers (for calculations), text or labels (names, headings), dates (which can be used in date calculations), and boolean values (TRUE/FALSE). Knowing the type is important because many functions only work on numbers, while text functions treat text differently. If you type a number but it is stored as text, calculations such as SUM will ignore it.

Basic operations:
You can enter values directly, edit by double-clicking, copy and paste cells, fill sequences using the fill handle and undo mistakes using the Undo command. Formulas always begin with an equals sign = and can include arithmetic, references to other cells, or functions. Learning to use formulas correctly helps avoid manual calculation errors and saves time.

Good practices:
Keep headings in the top row, avoid merging cells for data you will calculate, and use clear labels. Save your work often and give meaningful sheet names. These small habits make the sheet easier to read, share and check for mistakes.

Definition of a range and why it matters
A range is a group of cells chosen together so you can perform the same operation on all of them at once. Ranges are written using the start and end cells separated by a colon, for example A1:A10 or B2:E5. Using ranges saves time: instead of typing a formula for every cell one by one, you refer to the range in a function such as SUM or AVERAGE.

Ways to select cells
To select a single cell, click it. To select a block, click the first cell, hold the mouse button and drag to the last cell, or click the first cell and Shift+click the last cell. For non-adjacent cells or ranges, hold Ctrl (or Command on Mac) and click each area you need. You can also use the keyboard: Shift+arrow keys extend the selection; Ctrl+Shift+arrow keys jump to the end of a data block.

Select entire rows and columns
Click the row number to highlight a full row or the column letter to highlight a full column. This is useful when you want to hide, delete or format a whole row/column. Selecting entire columns is also handy when you create charts or apply functions to columns of numbers.

Using ranges in formulas
Functions expect ranges as inputs: =SUM(B2:B12) adds values in B2 through B12. Ranges can be vertical (A1:A10) or horizontal (A1:F1). When creating formulas, check that the range covers only the intended cells; including headings or blank rows may give wrong results. Mixed ranges like A1:B10 include multiple columns.

Practical tips and pitfalls
Use named ranges in larger sheets to make formulas readable (for example, name A2:A31 as "Sales"). Be cautious when copying formulas: relative references adjust to new positions and may point to the wrong range if you did not lock them. Always preview the highlighted range the application selects for AutoSum or chart creation and correct it if necessary.

Entering values and editing
Type directly into a cell and press Enter to move down or Tab to move right. To edit an existing entry, double-click the cell, press F2, or edit the formula bar. Use clear labels for columns and rows so others understand your data. For repeated items, drag the fill handle (small square at the bottom-right of the selected cell) to copy or continue a sequence such as days or numbers.

Number formats and their effect
Formatting changes how a value looks without changing the underlying value. Common formats are Number (with decimals), Currency (adds a symbol), Percentage (multiplies by 100 and shows %), and Date. For example 0.25 formatted as Percentage shows 25%. If you want to calculate with a value, ensure it is stored as a number and not accidentally as text.

Text formatting and alignment
Use font settings, bold and italics for headings, and alignment options to place text left, centre or right. Wrap Text makes long entries appear in multiple lines inside one cell. Avoid merging many cells across a table header because merged cells can complicate sorting and formulas; use centered headings across selection only when necessary.

Borders, fills and styles
Apply borders to separate columns or rows visually and use fill colours sparingly to highlight headings or totals. Cell styles group common formatting so you can apply consistent looks across the sheet. Consistency helps when printing or presenting data.

Copying, pasting and validation
Paste Options let you choose to paste values only, formats only, or formulas. Data Validation restricts entries to certain types like whole numbers between 0 and 100; this reduces errors when many people enter data. Finally, always check for stray spaces or hidden characters and use TRIM to clean text so comparisons and lookups work correctly.

Writing simple formulas
Every formula begins with =. You can perform arithmetic using +, -, * and /. For example, =A1+A2 adds two cells, while =(A1+A2)*A3 adds A1 and A2 first, then multiplies the result by A3 because parentheses control order of operations. Use parentheses to make complex calculations clear and to ensure correct results.

Use of SUM function
SUM is a built-in function designed to add many numbers quickly and safely. Its syntax is SUM(range) or SUM(value1, value2, ...). For instance, =SUM(B2:B10) adds all values from B2 to B10. SUM ignores text and blank cells, so it will return the sum of only numeric entries. SUM is preferred over typing many + signs because it is shorter and less likely to contain mistakes.

AutoSum and selecting ranges
Most spreadsheet programs include an AutoSum button which guesses the correct range to sum when you click it. Always check the highlighted range before accepting. If the program guesses wrongly because of blank rows or headings, select the correct range manually and press Enter.

Copying formulas and relative changes
If you write a formula in one cell and copy it down, cell references normally change relative to each new row; this behaviour allows you to create a set of calculations for many rows quickly. For example if B2 contains =A2+5 and you copy it to B3, it changes to =A3+5 automatically. This reduces retyping and keeps formulas consistent across rows.

Common errors and checks
Watch for numbers stored as text — they will be ignored by SUM. Also avoid including headers or totals inside the summed range. If results look wrong, test the formula on a few known values and check cell formats and ranges. Using a small, checked example often helps find mistakes quickly.

Purpose and basic use
These functions help you summarise a set of numbers quickly. AVERAGE gives the arithmetic mean, MIN finds the smallest value, MAX finds the largest, and COUNT counts how many numeric entries exist in a range. They are useful in tests, experiments and small business records to describe data at a glance.

How AVERAGE works
AVERAGE(range) adds all numeric values in the range and divides by the count of numeric entries. For example, =AVERAGE(B2:B11) calculates the mean of eleven values. It ignores blank cells and text; if you need blanks treated as zero, enter 0 explicitly or use other techniques.

MIN and MAX details
MIN(range) returns the lowest number in the range while MAX(range) returns the highest. These functions help find best and weakest results quickly. They also work when some values repeat or when there are negative numbers; the functions still return the correct extreme values.

COUNT and COUNTA differences
COUNT(range) counts only cells that contain numbers; COUNTA(range) counts all non-empty cells including text. Use COUNT when you need the number of numeric entries, for instance to check how many pupils submitted scores. Use COUNTA when you want to count filled rows even if some entries are text.

Combining functions
You can combine these functions to analyse data: =MAX(B2:B20)-MIN(B2:B20) gives the spread of values, while =AVERAGE(B2:B20) alongside COUNT shows both the mean and the number of values used. These combinations allow a quick statistical overview without complex formulas.

Practical caution
If some cells contain errors or are text disguised as numbers (for example, a leading space or apostrophe), functions may ignore them or return error messages. Clean the data using TRIM, check formats, and use small test ranges to confirm that functions behave as expected.

What IF does
The IF function makes decisions in a spreadsheet. It checks a condition and returns one value if that condition is TRUE and another if it is FALSE. This allows the sheet to show text labels, numerical results, or trigger different calculations based on data.

Syntax and comparisons
IF(condition, value_if_true, value_if_false). Conditions use comparison operators such as =, >, <, >=, <= and <> (not equal). For example, =IF(B2>=35, "Pass", "Fail") checks if B2 is at least 35 and returns the correct label.

Using IF for numbers and text
IF can return numbers (for instance, bonus amounts), text (labels like "Eligible"), or references to other cells. When returning text, place it in quotes. You can use IF to compute discounts: =IF(A2>1000, A2*0.9, A2) applies a 10% discount only when A2 exceeds 1000.

Nesting IFs
For multiple branches you can nest IF inside another IF: =IF(A2>=90, "A", IF(A2>=75, "B", IF(A2>=50, "C", "D"))). While useful, many nested IFs become hard to read. Alternatives like lookup tables with VLOOKUP (not required at this level) or separate helper columns may be clearer.

Combining with AND/OR
To test multiple conditions use AND and OR inside IF. Example: =IF(AND(B2>=35,C2>=35), "Pass", "Fail") checks two subjects. Use OR when any one of the conditions is enough: IF(OR(condition1, condition2), ...).

Tips and common pitfalls
Always provide a value for the false result to avoid blanks. Ensure logical tests are simple so they evaluate correctly, and test formulas with sample data. Remember that text returns need quotes and that the IF function can be used for both display labels and numeric calculations.

Why text functions are useful
Text functions help format, clean and combine labels and codes. They are essential when you build full names from parts, extract IDs from codes, or measure the length of entries for validation. Good use of text functions prevents manual copying and speeds up data preparation.

Joining text: CONCATENATE and &
CONCATENATE(text1, text2, ...) joins different pieces of text. A simpler approach in many spreadsheets is using the & operator: =A2 & " " & B2 joins first and last names with a space. CONCATENATE is explicit, while & is shorter.

Extracting parts: LEFT, RIGHT and MID
LEFT(text, n) returns the first n characters; RIGHT(text, n) returns the last n characters. Use MID(text, start, n) when you need characters from the middle. These functions are useful for extracting area codes, product codes, or date parts stored as text.

Length and cleaning: LEN and TRIM
LEN(text) returns the number of characters including spaces. It helps validate fields such as phone numbers. TRIM(text) removes extra spaces at the start, end or between words so text comparisons work correctly. Clean data avoids lookup failures and mismatches.

Changing case
UPPER(text), LOWER(text) and PROPER(text) change case: UPPER makes all letters capital, LOWER makes them small, and PROPER capitalises the first letter of each word. Use these to standardise names and make lists consistent.

Practical combination
Combine functions: =UPPER(LEFT(A2,3)) returns the first three letters in uppercase. Or =TRIM(A2) & " " & TRIM(B2) ensures combined names have no extra spaces. Using these small tools together makes your spreadsheet reliable and easier to use by others.

How spreadsheets treat dates and times
Internally spreadsheets store dates as serial numbers (days since a base date) and times as fractions of a day. This lets you perform arithmetic with dates and times. Enter dates in a recognised format such as DD/MM/YYYY so the application recognises them as dates instead of plain text.

Common functions and their use
TODAY() returns the current date and updates when the sheet recalculates. NOW() returns both date and time. Use DATE(year, month, day) to assemble a date from separate parts; this is useful when day, month and year are in different columns. DAY(date), MONTH(date) and YEAR(date) extract the parts from a date for further analysis.

Calculating durations
Subtract one date from another to find the number of days between them: =B2-A2. For months or years difference, some applications offer DATEDIF(start, end, unit) to return months or years; if not available, use combinations of YEAR and MONTH to approximate differences. When calculating ages, take care around birthdays and leap years.

Formatting dates and times
Change how a date is shown without changing its value: dd-mm-yyyy, d mmm yyyy, or weekday names like Monday. Time formats show hours and minutes (hh:mm) or include seconds. Use custom formats to combine date and time display if needed.

Practical examples and cautions
Use dates for schedules, due dates, and attendance. Add days to a date to get a due date: =A2+30 gives the date 30 days after A2. Be careful about regional settings (DD/MM/YYYY vs MM/DD/YYYY) because a date may be interpreted differently on other computers. Always check the stored value if results seem incorrect.

Relative references and how they help
By default a cell reference like A2 is relative. When you copy a formula from one cell to another, the relative references shift to refer to the cells in the new position. For example, if B2 contains =A2*2 and you copy B2 to B3 it becomes =A3*2. This behaviour makes it easy to perform the same calculation down a column without rewriting the formula each time.

Absolute references to fix a cell
Absolute references use the $ sign to lock a column, a row, or both. $A$1 fixes both column and row so the reference does not change when copied. $A1 fixes the column only, while A$1 fixes the row only. Use absolute references for fixed values such as tax rates, constants or the location of a single cell you want all formulas to use.

Mixed references for combined control
Mixed references allow part of the reference to change and part to stay fixed. For example, if you have a table where each row multiplies by a common rate across columns, a mixed reference can lock the row or column you need. Understanding which part to lock prevents errors when filling formulas across both rows and columns.

Practical examples
Put a sales tax percentage in $D$1 and use =A2*(1+$D$1) in column B to compute tax-inclusive prices for many rows; copying this formula keeps D1 fixed. For a multiplication table, use A$1 to fix the top row of multipliers while allowing the column to change when copying horizontally.

Tips to avoid mistakes
When creating a formula that will be copied many times, decide which references must remain pointing to the same cell. The F4 key toggles reference styles in many applications—press it while editing a reference to cycle between relative and absolute forms. Test the first few copied cells to ensure references behave as expected before filling the whole area.

Why we use charts
Large tables of numbers are hard to read quickly. Charts convert rows and columns into pictures that show differences, trends and proportions at a glance. A good chart helps the viewer understand patterns — for example whether sales are rising, which product is most popular, or how marks compare across students.

Common chart types and when to use them
Column and bar charts compare categories — use them for marks by student or sales by product. Line charts show trends over time, such as sales across months; they connect data points to show increase or decrease. Pie charts show how a whole is divided among parts (market share percentages) and work well when there are a few categories. Scatter plots show relationships between two numeric variables, for example hours studied versus exam score.

Preparing data for charts
Arrange your data so labels are in the first column or row and numeric values are adjacent. For a time series, put dates or months in one column and the numbers to plot in the next column. Charts use the label row/column for axis names and the numeric columns for the data series. Clean, consistent labels make legends and axis descriptions clearer.

Chart components and readability
Most charts have a title, axes (for column, bar and line charts), a legend for multiple series, and gridlines to aid reading. Use a meaningful title and label axes, including units such as "Marks" or "Sales (₹)". Avoid overloading a chart with too many series or colours — simpler charts are easier to understand.

Choosing the right chart
Ask what you want to show: comparison (column/bar), trend (line), composition (pie), or correlation (scatter). A wrong chart can hide the message. Keep charts uncluttered, avoid unnecessary 3D effects, and ensure colour choices are distinguishable if printed in greyscale.

Steps to create a basic chart
First select the data range including labels and numbers. Then use Insert > Chart (or the chart button) to create a chart. The chart wizard or dialog will often suggest a chart type and preview the result. Always check that the selected data range and the axis labels are correct before inserting the chart.

Editing chart elements
After creating a chart, click it to reveal editing tools. You can change the title by clicking it and typing a clearer heading. Edit axis labels to include units and adjust the legend position for clarity. Use formatting options to change colours, line styles and marker shapes for better visibility. Resize and move the chart so it sits near the data table or on a separate sheet.

Adding or removing data series
To compare multiple sets of numbers, add extra series by selecting more columns or by using the chart data selection dialog. Each series gets a separate colour and legend entry. If a series is not needed, remove it from the data selection. For long-term use, consider naming series clearly in the sheet so legends make immediate sense.

Switching chart types and combining charts
If the initial type does not communicate the message well, change it (for example, from Column to Line to show trends). Some spreadsheets allow combining chart types — such as columns with a line series — useful when scales differ and you want to emphasise one dataset while showing another for comparison.

Linking and copying charts
Charts can be copied into presentations and documents. If pasted as linked objects they update when the spreadsheet changes; if pasted as images they remain static. Choose the method depending on whether you need dynamic updates. Before sharing, check the chart legend, title and axis labels so the recipient can understand the chart without the data table.

Design principles for clear charts
Good charts present the message quickly. Keep titles short but descriptive, use legible fonts and label axes with units. Use consistent colours for the same series across charts and choose contrasting colours for different series. Avoid heavy 3D effects that can distort perception and make values harder to compare accurately.

Setting axis scales and intervals
Choose sensible minimum and maximum values for axes so the data is not misleading. For percentage data set the vertical axis from 0 to 100. Set major tick intervals to consistent steps (for example 10, 20, 30) so reading values is straightforward. If the values vary widely, consider a secondary axis for a second data series and explain this clearly in the chart legend.

Legends, data labels and annotations
Place the legend where it does not overlap the data. Add data labels when exact values are important, but avoid adding labels for every point on a dense series because this clutters the chart. Use callouts or text boxes to annotate key points such as the highest or lowest values, sudden changes, or interesting trends to guide the reader’s attention.

Gridlines, backgrounds and printing
Keep gridlines light and minimal; strong gridlines distract from the data. Use a plain white background or a subtle tint that does not reduce contrast. For print, test charts in greyscale to ensure they remain understandable and consider patterns or hatch fills if colours may not reproduce accurately.

Accessibility and simplicity
Use chart elements consistently and avoid overloading a single chart with too many series. If necessary, split the information across multiple charts. Make sure axis text sizes are readable when charts are scaled down for reports and that titles and labels explain what the viewer is seeing without needing to read the data table.

Preparing a worksheet for printing
Before printing, decide what parts of the sheet need to appear on paper and set the print area accordingly. Use Page Layout options to choose orientation (Portrait or Landscape), paper size, and margins so tables and charts fit neatly. Scaling options let you fit a sheet to one page wide or a specified number of pages; use them cautiously because too much scaling reduces legibility.

Headers, footers and page numbers
Add headers to show the document title and footers to show page numbers and dates. These make printed sheets easier to follow, especially when multiple pages are used. Include the sheet name or a brief description in the header to help readers identify the content quickly.

Repeating rows and columns
For long tables set rows to repeat at the top of each printed page (for example the header row). This ensures column headings are visible on every page and avoids confusion when someone reads a page in isolation. The same idea applies to repeating columns for wide tables printed across multiple pages.

Print preview and page breaks
Use Print Preview to check where page breaks occur and whether charts or rows are split across pages. Adjust column widths, font sizes or page breaks so content does not break awkwardly. Insert manual page breaks where appropriate to keep related items together.

Exporting, sharing and protection
Export the worksheet to PDF to preserve layout and ensure recipients see the sheet as intended. When sharing online, set permissions carefully: give edit rights only to those who must change the sheet. Use simple protection to lock cells that contain formulas to prevent accidental changes, and keep a backup copy with full editing access for later updates.