Spreadsheet
- Spreadsheet means excel is a software program from Microsoft that is part of the Microsoft Office suite of software programs.
The concept of electronic spreadsheet started with software called VisiCalc, which appeared in the early 1980s. It offered a major advantage over traditional pen and paper calculation.
- Excel is capable of creating and editing spreadsheets that are saved with an .xls or .xlsx file extension.
- General uses of Excel include cell-based calculation, pivot tables, and various graphing tools etc.
Workbook or Worksheet
- Excel Workbook comprises number of worksheets or Excel Workbook is the complete book and Excel
- Worksheet means a single sheet in Excel workbook. or Worksheet is like a single page in a book. that contains cells organized by rows and columns. Each cell can contain a number, text or formula.
- A cell can also reference another cell in the same worksheet, the same workbook or a different workbook.
Following Terms used in Spreadsheet:
Active cell: An active cell is a rectangular box, highlighting the cell in a spreadsheet. It helps identify what cell is being working with and where data will be entered.
Record: The combination of cell data in one row known as Record.
Row & Column: MS Excel is in tabular format consisting of rows and columns.
- Row runs horizontally while Column runs vertically. Each row is identified by row number, which runs vertically at the left side of the sheet.
- Each column is identified by column header, which runs horizontally at the top of the sheet.
Field: The column in the spreadsheet is known as Field.
Range: A range in Excel is a collection of two or more cells.
Workbook: A Workbook is a collection of multiple Worksheet or Spreadsheet.
How many rows and columns are there in MS Excel above 2007 version?
- Row numbers ranges from 1 to 1048576, in total 1048576 rows,
- Columns ranges from A to XFD, in total 16384 columns.
Features of Spreadsheet
1. Add Header and Footer: MS Excel allows us to keep header and footer in our spreadsheet document.
2. Find and Replace Command: MS Excel allows us to find the needed data (text and numbers) in the workbook and also replace the existing data with a new one.
3. Password Protection: It allows user to protect their workbooks by using password from unauthorized access to their information.
4. Data Filtering: Filtering is a quick and easy way to find and work with a subset of data in a range. A filtered range displays only the rows that meet the criteria you specify for a column. MS Excel provides two commands for filtering ranges:
- AutoFilter, which includes filter by selection, for simple criteria
- Advanced Filter, for more complex criteria
5. Data Sorting: Data sorting is the process of arranging data in some logical order. MS Excel allows us to sort data either in ascending or descending order.
6. Built in formula: MS Excel has got many built- in formulae for sum, average, minimum, etc. We can use those formula as per our needs.
7. Create different charts: MS Excel allows us to create different charts such as bar graph, pie- charts, line graphs, etc. This helps us to analyze and compare data very easily.
8. Automatically edits the result: MS Excel automatically edits the result if any changes are made in any of the cell.
How to create reference of cell from one sheet to other sheet in excel?
- There are two worksheets in one workbook
- One worksheet name is DCS and second worksheet name is AMPICS
- In DCS worksheet first column (A1) write Hello
- To view Hello in second worksheet AMPICS using following formula
- =DCS!A1
Conditional
Formatting of Spreadsheet
- You can use Conditional Formatting functionality of spreadsheets to highlight the data with the help of various rules and styles.
- Conditional formatting in spreadsheets allows you to highlight cells based on specific criteria which can further help you to glance at your data in a better way and make it more realistic for humans to read it.
- Conditional Formatting allows you to change the characteristics of the cell, like background color, style of text based on rules you set, automatically.
- You do not have to set the
conditions manually which can save a lot of your time. The conditions are based
on an
if/thenstatement.
1. Select the cells you want to apply format rules to.
2. Click Format. Conditional formatting.
3. Create a rule. Single color: Under "Format cells if," choose the condition that you want to trigger the rule....
4. Click Done.
Define
Terms: Freeze Pane and Auto Filter.
- On your computer, open a spreadsheet. Select a row or column you want to freeze or unfreeze.
- At the top, click View > Freeze.
- Select how many rows or columns to freeze.
Use
AutoFilter to filter your data
- Select the data you want to filter.
- Click Data > Filter.
- Click the arrow....
- Choose specific values: Uncheck (Select All) to clear all of the check boxes, and then check the boxes for the specific value(s) you want to see.
What is a Chart? List out various charts.
A chart is a graphic representation of data. Charts allow users to see what the results of data to better understand and predict current and future data.
Types of Chart:
Column Chart
Line Chart
Pie Chart
Bar Chart
Area Chart
Scatter Chart
To create a column chart, follow these steps:
1. Enter data in a spreadsheet.
2. Select the data.
3. Click Insert > Column, and select a column chart option of your choice.
4. You can optionally format the chart a little further. See the list below for a few options:
To apply a different chart layout, click Design > Charts Layout, and select a layout.
To apply a different chart style, click Design > Chart Styles, and pick a style.
To apply a different shape style, click Format > Shape Styles, and pick a style.
To apply different shape effects, click Format > Shape Effects, and pick an option such
as Bevel or Glow, and then a sub-option.
To apply a theme, click Page Layout > Themes, and select a theme.
Example of Column Chart
Pie Chart:
Pie charts are used to display the contribution of each value (slice) to a total (pie).
Pie charts always use one data series. For e.g. Pie charts are a popular way to show how much individual amounts-such as quarterly sales figures-contribute to a total amount- such as annual sales.
To create a pie chart, follow these steps:
1. Enter data in a spreadsheet.
2. Select the data.
3. Click Insert > Pie, and select a pie chart option of your choice.
4. You can optionally format the chart a little further. See the list below for a few options:
To apply a different chart layout, click Design > Charts Layout, and select a layout.
To apply a different chart style, click Design > Chart Styles, and pick a style.
To apply a different shape style, click Format > Shape Styles, and pick a style.
To apply different shape effects, click Format > Shape Effects, and pick an option such
as Bevel or Glow, and then a sub-option.
To apply a theme, click Page Layout > Themes, and select a theme.
Example of Pie Chart
What is the Naming Range?
In Excel, you can give a name to a cell or a range of cells. Now, instead of using the cell reference (such as A1 or A1:A10), you can simply use the name that you assigned to it.
Go to Formulas –> Define Name.
In the New Name dialogue box, type the Name you wish to assign to the selected data range. ...
Click OK
List out the below functions with an example.
Average
To calculate the average of a range of cells, use the AVERAGE function.
For e.g. Average(A1:O1)
Min
To find the minimum value, use the MIN function.
For e.g. Min(A1:O1)
Max
To find the maximum value, use the MAX function.
For e.g. Max(A1:O1)
Sum
To calculate the sum of a range of cells, use the SUM function.
For e.g. SUM(A1:O1)
Date-Time Function with example.
Or follow my blog from the below link
Also, Join my Telegram channel with the below link
Also, join my Whatsapp group with the below link
0 Comments