Informatics
Spreadsheet calculator - I
Petr Suchánek
Informatics

Outline of the lecture
•Data file
•Data sources – external data
•Cells
•Sheets
•Table and list
•Formulas and functions
•Charts

Spreadsheet calculator
•A spreadsheet is a computer application or program that enables the user to tabulate and collate
data.*
•This data can then be used to make calculations, show graphical representations or analysis.*
• A spreadsheet comprises of a grid of ‘cells’ arranged in rows and columns and information can be
inserted into each cell.*
•Each cell can contain text, numbers and formulas.*
•A formula is a calculation based from the contents of cells or a total of a combination of cells.
The total in that formula can change if the content of the combination cells used is also amended.*
•
*
https://www.digitalunite.com/technology-guides/creating-documents/microsoft-excel/what-spreadsheet

Spreadsheet calculator
•Spreadsheets can be useful for home budgeting or accounting and they make it easy to display
information.*
•Spreadsheets can consist of a number of different worksheets that can include different data as
well as enabling cells on one worksheet to be used and referenced on other different worksheets.*
•Information can also be sorted and filtered by a spreadsheet.*
•The Microsoft Office spreadsheet is called Excel but there are open office spreadsheets available,
including internet based web apps such as Google spreadsheets.*
•Spreadsheets have a number of tools included to support many different types of calculations and
can include graphical presentations such as graphs, pie charts etc.*
•
*
https://www.digitalunite.com/technology-guides/creating-documents/microsoft-excel/what-spreadsheet

Microsoft Office Excel
* https://www.webopedia.com/TERM/M/microsoft_excel.html
•Microsoft Excel is a spreadsheet program included in the Microsoft Office suite of applications.*
•Microsoft Excel usually comes bundled with Microsoft Office and is compatible with other
applications offered in the suite of products.*
•In addition to its standard spreadsheet features, Excel also offers programming support via
Microsoft's Visual Basic for Applications (VBA), the ability to access data from external sources
via Microsoft’s Dynamic Data Exchange (DDE), and extensive graphing and charting capabilities.*
•
•

Data file
*https://support.office.com/en-us/article/file-formats-that-are-supported-in-excel-0943ff2c-6014-4e
8d-aaea-b83d51d46247
**https://www.excel-exercise.com/xls-xlsx-file/
•You can save an Excel file in another file format by clicking the File / Save As.*
•The file formats that are available in the Save As dialog box vary, depending on what type of
sheet is active (a worksheet, chart sheet, or other type of sheet).*
•https://support.office.com/en-us/article/file-formats-that-are-supported-in-excel-0943ff2c-6014-4e
8d-aaea-b83d51d46247
•If you have on your computer an xls file and you save it in xlsx, you will see that the size has
been significantly reduced.**
•The XML code compress your file. Have a look at this tip.**
•

Microsoft Excel 2016
*https://techhelpday.com/microsoft-office-2019/
**

Data file
*https://support.office.com/en-us/article/file-formats-that-are-supported-in-excel-0943ff2c-6014-4e
8d-aaea-b83d51d46247
**https://www.excel-exercise.com/xls-xlsx-file/
•You can save an Excel file in another file format by clicking the File / Save As.*
•The file formats that are available in the Save As dialog box vary, depending on what type of
sheet is active (a worksheet, chart sheet, or other type of sheet).*
•https://support.office.com/en-us/article/file-formats-that-are-supported-in-excel-0943ff2c-6014-4e
8d-aaea-b83d51d46247
•If you have on your computer an xls file and you save it in xlsx, you will see that the size has
been significantly reduced.**
•The XML code compress your file. Have a look at this tip.**
•

Data sources – external data
•The main benefit of connecting to external data is that you can periodically analyze this data in
Microsoft Office Excel without repeatedly copying the data, which is an operation that can be
time-consuming and error-prone.*
•After connecting to external data, you can also automatically refresh (or update) your Excel
workbooks from the original data source whenever the data source is updated with new information.*
•https://support.office.com/en-us/article/tutorial-import-data-into-excel-and-create-a-data-model-4
b4e5ab4-60ee-465e-8195-09ebba060bf0
•
*https://support.office.com/en-us/article/connect-to-import-external-data-9967afd8-85ee-4df3-aa06-7
53bcc1a2724

Cells
•The number of cells, columns, rows, and sheets supported by a spreadsheet depend on the software
being used, and the data contained in the spreadsheet.*
* https://www.computerhope.com/issues/ch000357.htm

Cells
•There are two types of cell references: relative and absolute. Relative and absolute references
behave differently when copied and filled to other cells.*
•Relative references change when a formula is copied to another cell.*
•Absolute references, on the other hand, remain constant no matter where they are copied.*
•By default, all cell references are relative references.*
* https://edu.gcfglobal.org/en/excel2016/relative-and-absolute-cell-references/1/

Cells
•When copied across multiple cells, they change based on the relative position of rows and
columns.*
•For example, if you copy the formula =A1+B1 from row 1 to row 2, the formula will become =A2+B2.*
•Relative references are especially convenient whenever you need to repeat the same calculation
across multiple rows or columns.*
*https://edu.gcfglobal.org/en/excel2016/relative-and-absolute-cell-references/1/

Cells
•There may be a time when you don't want a cell reference to change when copied to other cells.*
•Unlike relative references, absolute references do not change when copied or filled.*
•You can use an absolute reference to keep a row and/or column constant.*
•An absolute reference is designated in a formula by the addition of a dollar sign ($).*
•It can precede the column reference, the row reference, or both.*
*https://edu.gcfglobal.org/en/excel2016/relative-and-absolute-cell-references/1/

Cells
•When writing a formula, you can press the F4 key on your keyboard to switch between relative and
absolute cell references, as shown in the video below.*
•This is an easy way to quickly insert an absolute reference.*
•https://edu.gcfglobal.org/en/excel2016/relative-and-absolute-cell-references/1/
*https://edu.gcfglobal.org/en/excel2016/relative-and-absolute-cell-references/1/

Sheets
*https://edu.gcfglobal.org/en/excel2016/relative-and-absolute-cell-references/1/


Table and list
•Once you've entered information into your worksheet, you may want to format your data as a table.*
•Just like regular formatting, tables can improve the look and feel of your workbook, and they'll
also help you organize your content and make your data easier to use.*
•Excel includes several tools and predefined table styles, allowing you to create tables quickly
and easily.*
•https://edu.gcfglobal.org/en/excel2016/tables/1/
*https://edu.gcfglobal.org/en/excel2016/tables/1/

Table and list
*https://edu.gcfglobal.org/en/excel2016/tables/1/


Table and list
•https://edu.gcfglobal.org/en/excel2016/tables/1/
•https://support.office.com/en-us/article/create-and-format-tables-e81aa349-b006-4f8a-9806-5af9df0a
c664
•https://support.office.com/en-us/article/overview-of-excel-tables-7ab0bb7d-3a9e-4b56-a3c9-6c94334e
492c
•https://www.contextures.com/xlExcelTable01.html

Formulas and functions
*https://edu.gcfglobal.org/en/excel2016/intro-to-formulas/1/
•One of the most powerful features in Excel is the ability to calculate numerical information using
formulas.*
•Just like a calculator, Excel can add, subtract, multiply, and divide.*
•Excel uses standard operators for formulas, such as:
•a plus sign for addition (+);
•a minus sign for subtraction (-);
•an asterisk for multiplication (*);
•a forward slash for division (/);
•a caret (^) for exponents.
•All formulas in Excel must begin with an equals sign (=).

Formulas and functions
*https://edu.gcfglobal.org/en/excel2016/intro-to-formulas/1/


Formulas and functions
*https://www.universalclass.com/articles/computers/excel/excel-2016-how-to-use-formulas-and-functio
ns.htm
•Functions, just like formulas, always begin with an equal sign (=). After the equal sign, you
enter in the name of the function.*
•It doesn't matter if you enter it in uppercase or lowercase. Following the name of the function,
you provide the arguments of the function.*
•Arguments are always enclosed parentheses.*
•There are just a few things you need to remember before starting to insert functions into your
spreadsheets:
•When typing a function into a cell, don't insert spaces between the equal sign, function name, and
arguments.*
•If you're adding more than one value, separate each function with a comma.*

Formulas and functions
*https://www.universalclass.com/articles/computers/excel/excel-2016-how-to-use-formulas-and-functio
ns.htm
•To insert a function directly into a cell, click the cell where you want to insert the function.
Next, go to the Formulas tab, then click Insert Function.*
•When you click Insert Function, you'll see this dialogue box:

Formulas and functions
•https://support.office.com/en-us/article/overview-of-formulas-in-excel-ecfdc708-9162-49e8-b993-c31
1f47ca173
•https://www.universalclass.com/articles/computers/excel/excel-2016-how-to-use-formulas-and-functio
ns.htm
•https://www.managementboek.nl/code/inkijkexemplaar/9780789755643/excel-2016-formulas-and-functions
-engels-paul-mcfedries.pdf
•https://www.excel-easy.com/introduction/formulas-functions.html
•https://edu.gcfglobal.org/en/excel2016/intro-to-formulas/1/
•

Charts
*https://www.universalclass.com/articles/computers/excel/excel-2016-creating-charts-and-diagrams.ht
m
•In Excel 2016, charts and diagrams can show trends, averages, high and low points, and more.*
•Not only do they make your worksheets more visually appealing, they also serve a definite
function.*
•They make it easier for your intended audience to sort out and understand the information you are
presenting to them.*
•This is especially true when dealing with data.*

Charts
*https://edu.gcfglobal.org/en/excel2016/charts/1/
•Excel has several different types of charts, allowing you to choose the one that best fits your
data.*
•In order to use charts effectively, you'll need to understand how different charts are used.*
•Select the cells you want to chart, including the column titles and row labels. These cells will
be the source data for the chart.*

Charts
*https://edu.gcfglobal.org/en/excel2016/charts/1/
•From the Insert tab, click the desired Chart command. In our example, we'll select Column.*
•Choose the desired chart type from the drop-down menu.*

Charts
*https://edu.gcfglobal.org/en/excel2016/charts/1/
•The Selected chart will be inserted into the worksheet.*

Charts
*https://edu.gcfglobal.org/en/excel2016/charts/1/
•Excel allows you to add chart elements—such as chart titles, legends, and data labels - to make
your chart easier to read.*
•To add a chart element, click the Add Chart Element command on the Design tab, then choose the
desired element from the drop-down menu.*

Charts
•https://edu.gcfglobal.org/en/excel2016/charts/1/
•https://www.techonthenet.com/excel/charts/line_chart2016.php
•https://support.office.com/en-us/article/create-a-chart-with-recommended-charts-cd131b77-79c7-4537
-a438-8db20cea84c0
•https://trumpexcel.com/advanced-charts/
•https://www.smartsheet.com/how-to-make-charts-in-excel
•https://www.microsoft.com/en-us/microsoft-365/blog/2015/07/02/introducing-new-and-modern-chart-typ
es-now-available-in-office-2016-preview/
•

The end