Spreadsheets

Question 1

What is a Spreadsheet?

Answer 1

A spreadsheet is a grid of cells organized in rows and columns. Each column is assigned a letter and each row is given a number.

Question 2

Types of Spreadsheets

Answer 2

Excel, Apple Numbers, Quip, Smartsheet, Google Sheets

Question 3

What is a row?

Answer 3

A row is a group of cells that runs from the left of the page to the right. In Excel, rows are identified by numbers. HORIZONTALLY

Question 4

What is a Column?

Answer 4

A column is a group of cells that runs from the top of the page to the bottom. In Excel, columns are identified by letters. VERTICALLY

Question 5

What is a Cell?

Answer 5

A cell is the intersection of a row and a column.

Question 6

What is a Cell Address?

Answer 6

A cell address, or cell reference, is an alphanumeric value used to identify a specific cell in a spreadsheet.

Question 7

What is a Cell Range?

Answer 7

A cell range is a group of cells that form a rectangle on the screen. A range is represented by a cell address with a colon in between. Eg:- (B2:B6)

Question 8

What are Values?

Answer 8

Values are numbers that can be used in calculations. Values have an automatic right alignment

Question 9

What are Labels?

Answer 9

Labels are other types of data, entered as titles and headings. Text automatically align to the left

Question 10

What is a Formula?

Answer 10

A FORMULA is an expression which calculates the value(s) of a cell. All formulae must start with an equal (=) sign.

Question 11

What are Functions?

Answer 11

FUNCTIONS are predefined formulas and are already available in Excel.

Question 12

What is the purpose of the IF Function?

Answer 12

Return one value if a condition is true and another value if it’s false. For example: =IF(A2>B2,”Over Budget”,”OK”) =IF(A2=B2,B4-A4,””)

Question 13

What is the purpose of the COUNTIF function?

Answer 13

Counts the number of cells in a range that meet a given condition. This function counts labels, values or both. The format for formulating is : =COUNTA(range, “condition”) example =COUNTIF(A4 : A10, “red”) or =COUNTIF(A4:A10, “<10”)

Question 14

What is the purpose of the IF function?

Answer 14

IF -This function allows you to decide whether a condition is met or not and returns one of the two values. The syntax for the IF function is =IF(test, “true result”,”false result”) eg. =IF(A2>B2,”True”,”False”).

Question 15

What is Cell Referencing?

Answer 15

CELL REFERENCING is a method used to refer to a specific cell or group of cells within a spreadsheet.

Question 16

What is Absolute Cell Referencing?

Answer 16

Absolute Cell Referencing refers to cells by their fixed position in the spreadsheet, regardless of where the formula is located. Absolute references, on the other hand, remain constant no matter where they are copied

Question 17

Relative Cell Referencing?

Answer 17

Relative references change when a formula is copied to another cell.

When a formula or function uses relative cell referencing, it refers to cells relative to the position of the formula itself.

For example, if you have a formula in cell B2 that references cell A1, copying that formula to cell C3 would automatically adjust the reference to cell B2.

Question 18

What is Merge and Center?

Answer 18

Merge and Center is a formatting option in Excel that allows you to combine multiple cells into a single cell and center the contents of the merged cell.

Question 19

What is VLOOKUP?

Answer 19

VLOOKUP stands for “Vertical Lookup”. The primary purpose of the VLOOKUP function is to search for a specific value in a column of data and return a corresponding value from another column.

Question 20

What is Advanced Filtering?

Answer 20

Advanced Filtering/Filter allows you to generate a unique list of items and extract those items to another place in your worksheet or workbook

Question 21

List the steps for advanced filtering?

Answer 21

Create a criteria range
Go to data tab
Click advanced filter
In dialogue box; list range criteria, criteria range, select copy to a new location

Question 22

Steps to generate a pivot table

Answer 22

Select Data:
Ensure your data is organized in a tabular format with column headers. Click anywhere within your dataset.

Insert PivotTable:
Go to the “Insert” tab on the Excel ribbon. Click on “PivotTable” in the Tables group.

Choose Data Range:
Excel will automatically detect the range of your data. Confirm that the range is correct or manually select the range if necessary. Click “OK”.

Design PivotTable:
Excel will open a new worksheet with a blank PivotTable and the PivotTable Field List pane. Drag and drop fields from your dataset into the “Rows”, “Columns”, and “Values” areas to organize and summarize your data.

Customize PivotTable:
You can further customize your PivotTable by applying filters, sorting data, changing summary functions, and formatting cells.

Question 23

Steps to create a Pivot Chart

Answer 23

Create PivotTable:
Follow the steps above to create a PivotTable.

Select PivotTable:
Click anywhere within your PivotTable.

Insert PivotChart:
With the PivotTable selected, go to the “Insert” tab on the Excel ribbon. Click on “PivotChart” in the Charts group.

Choose Chart Type:
Select the chart type you want to use (e.g., column chart, line chart, pie chart) from the list of available chart types. Click “OK”.

Design PivotChart:
Excel will insert a new chart linked to your PivotTable. Customize the chart as needed by adding chart elements, formatting axes, applying styles, and modifying data series.