In times past business records have been known to be stored in ledgers. These are books with detailed transaction history, sales and inventory. In modern times the evolving technology has improved the practice from ledgers to spreadsheets. So what is a spreadsheet?
A spreadsheet is a software that captures data, stores data and can be used to analyse that data depending on a user’s requirements. There are different applications that can be used as spreadsheet namely: Microsoft Excel, Google Sheets, Apple Numbers, LibreOffice Calc etc
This tutorial will focus on Microsoft Excel.
Excel is a program that is made by Microsoft and is widely used to store, analyse and present data. Data is organized into rows and columns and can be in various formats.
Here are fundamental concepts to note as you deal with Excel:
1. Excel Interface:
- Menubar:This is the section that gives you options on what you want to do within the spreadsheet. That includes formatting the details, headers, values and the general look of the sheet.
- Formula Bar: Displays the current cell you are currently on, and can be used to insert or edit a formula. Formula Bar: Displays the current cell you are currently on, and can be used to insert or edit a formula.
- Column, Row and Address: Excel arranges data in the form of rows(run horizontally), columns(run vertically) and cells (intersection of a row and column) as shown below. The cells are assigned addresses to make it easy to reference the data as shown above. For instance the current active cell is G10.
- Workbook and Worksheets: When one opens a new excel file, the spreadsheet opened is a new workbook which can contain more than one worksheet as shown below. This makes data management efficient for related data.
2. Data Types
Excel handles data of different data types such as Text(String), Numbers(integers and floats), Date/Time, Currency etc. This helps to validate data and work format data to the required format.
3. Formula & Functions
Formula are user defined mathematical operations that a user performs on a cell or range of cells. Functions are inbuilt(come with excel software) mathematical and statistical formulae that help in calculations, and analysing the data. A few Examples include;
- Formula: Multiplication, Division, Subtraction, Addition etc
- Functions: SUM(), AVERAGE(), COUNT(), MOD(), MIN() etc.
4. Data Analysis
- Filtering and Sorting: Excel enables you to work on a subset of data depending on a defined condition. This is what a filter does. For example, you can filter class data depending on the age of students. Sorting means you can arrange data in a certain order either ascending and descending. This can help in glancing at the upper and lower data limits.
- Conditional Formatting: You may want to highlight data that fits a certain criteria. E.g Highlight in red all the dates that fall on national holidays in a year.
- Tables: excel enables you to convert data within a specified range to a table. This makes filtering and sorting operations easier and structured.Example of a table:
Charts: Charts are used to visualize data which is used for data presentation. This makes it easier to see trends within the data. There are different charts that are suitable for different scenarios, e.g bar charts, line charts, circle charts, etc
Dashboard: This is a view that represents key KPI metrics. With a quick view you can analyze and see the data trends and talking points from the view. The view gives a summary and insights can be drawn from the view from the metrics shown. An example is shown below;
There are more excel features and functions that enhance data analysis and statistical analysis further. You can explore more on this link .
More on MS Excel.
Top comments (0)