Spreadsheets Overview and Components

Spreadsheets

• Spreadsheet programs are software tools used to organize and display data in tables.
• Spreadsheet programs enable easy and accurate data management.
• Spreadsheet programs are effective tools for data analysis and decision-making.
• Spreadsheets are made up of rows and columns.
• Data organization in spreadsheets is made easier with orderly and structured methods.
• Spreadsheets provide tools for data analysis using formulas and functions.
• Charts can be created in spreadsheets to illustrate trends and data effectively.
• Spreadsheets automate repetitive tasks, saving time and effort.

Basic Spreadsheet Components

• Cells: Individual boxes where data is entered. Cells have addresses based on column letter and row number (e.g., A1).
• Rows: Horizontal lines in a spreadsheet.
• Columns: Vertical lines in a spreadsheet.
• Sheets: Multiple pages in a spreadsheet file, used for organizing different data sets.
• Formulas: Expressions that perform calculations (e.g., =SUM(A1:A10)).
• Functions: Predefined formulas for specific calculations (e.g., AVERAGE, COUNT, IF).
• Filtering and Sorting: Tools to view specific data or arrange data in a particular order.
• Charts: Graphical representations of data (e.g., bar charts, line charts, pie charts).

Creating a New Worksheet

• Open Excel from the start menu.
• Create a new document by selecting 'Blank Workbook' from the main screen or File > New.

Defining Columns and Rows

• Add headers (e.g., "Seq,Material Name, Quantity, M.Date, Expiry Date") in the first row.
• Input data into subsequent rows.
• Adjust column widths and row heights in the Home tab.

Formatting Worksheets

• Font Type and Size: Choose font and font size from the Home tab.
• Text and Cell Coloring: Assign colors to text and fill cells with chosen colors.
• Borders: Add borders to cells using the Borders tool in the Home tab.
• Bold, Italic, Underline: Apply text formatting by selecting the text in the Home tab.
• Merging and Centering Cells: Combine multiple cells and center text using appropriate tools on the toolbar (e.g., Merge & Center).
• Wrap Text: Allows text longer than column width to fit in a cell evenly.

Sorting and Filtering Data

• Sort Ascending/Descending: Sort data in alphabetical or numerical order using the Sort tool.
• Sort by Specific Columns: Sort data based on a specific column and sorting order.
• Filtering Data: Select Filter from Data tab and choose the desired filtering parameters to show specific data.
• Remove Filters: Clear the filter settings to display all data.

Formulas and Functions

• Excel formulas use an equals sign (=).
• Basic calculations such as addition, subtraction, multiplication, and division.
• Common Excel functions include SUM (calculates the total), AVERAGE (calculates the average), MIN (finds the smallest value), MAX (finds the largest value), COUNT (counts the number of cells with numbers), COUNTIF (counts based on specified criteria), and PRODUCT (multiplies numbers in a range).
• Excel also has functions for dates, times, and more.

Data Validation

• Data Validation allows you to control the type of input allowed for a cell.

Error Checking

• Excel can automatically flag potential issues with data entry.

Charts and Graphs

• Organize your data into a table format before creating charts.
• Column/Bar charts are suitable for comparisons of different categories, line charts for trends over time, Pie charts for data proportions, Scatter plots for comparing two related factors, and Area charts show comparisons with shaded areas.