~~NOTOC~~ ===== Week 04 — Number processing ===== /*** [[https://www.cpacanada.ca/en/news/atwork/2017-11-17-excel-secrets-for-accountants]] [[https://www.lifehack.org/articles/technology/20-excel-spreadsheet-secrets-youll-never-know-you-dont-read-this.html]] [[https://www.pcmag.com/how-to/22-excel-tips-for-becoming-a-spreadsheet-pro]] ***/ ==== Evaluation ==== Up to 10 points can be gained towards your final score by completing the **in-class assignment** on Friday. ==== Preparation ==== == 1. Complete the self-preparation assignment at home before next class == Watch at least videos 1 to 11 (inclusive). If any of the topics are not familiar to you, open a blank Excel workbook and try to reproduce the examples shown in the videos for yourself. (These videos are also embedded at the [[#videos|end of this page]], in case you prefer to watch them without leaving your browser.) |{{ :class:il:il-04-01.mp4?linkonly | IL-04-01 }} \_ |data entry and basic editing \_ | |{{ :class:il:il-04-02.mp4?linkonly | IL-04-02 }} |rows and columns | |{{ :class:il:il-04-03.mp4?linkonly | IL-04-03 }} |autofill | |{{ :class:il:il-04-04.mp4?linkonly | IL-04-04 }} |tables | |{{ :class:il:il-04-05.mp4?linkonly | IL-04-05 }} |formulas, cell references | |{{ :class:il:il-04-06.mp4?linkonly | IL-04-06 }} |ranges, functions | |{{ :class:il:il-04-07.mp4?linkonly | IL-04-07 }} |conditional functions/formatting | |{{ :class:il:il-04-08.mp4?linkonly | IL-04-08 }} |filtering | |{{ :class:il:il-04-09.mp4?linkonly | IL-04-09 }} |visualisation | |{{ :class:il:il-04-10.mp4?linkonly | IL-04-10 }} |freezing rows and columns | |{{ :class:il:il-04-11.mp4?linkonly | IL-04-11 }} |import and export, CSV | |{{ :class:il:il-04-13.mp4?linkonly | IL-04-13 }} |extended example | == 2. Check your proficiency with Excel using the self-assessment questionnaire == - Answer each question in the self-assessment questionnaire as honestly as you can. (Do **not** press 'submit' when you are finished.) - Check your scores. - Revise those topics having the lowest scores. - When you have made progress with understanding or skill, update your scores. - Repeat from step 2 until you feel comfortable with most (or all) of the topics. On Thursday evening, press 'submit'. In class on Friday we will check which topics were difficult for everyone. To succeed at the in-class assignment for this class you should know how to * enter data and perform basic editing and formatting of cell content * change the data type of a row or column * use conditional formatting to change the appearance of a cell * create charts * use functions such as ''SUM()'' and conditional functions such as ''SUMIF()'' * identify when an absolute reference is necessary, and use one * import CSV files You can also use Internet search engines to find online tutorials and other educational materials relating to PPT, or even check our library to see if they have a book on the subject. In other words, use any resources you can to achieve your learning goals. ==== What you will learn from this class ==== * how to create spreadsheets and use them to manage numeric data * how to quickly enter series of data such as dates * how to perform computations with the data in a spreadsheet * how to visualise the data in the spreadsheet * how to sort and filter the data in a spreadsheet * how to manage scrolling in a large spreadsheet * how to import and export numeric data * how to export a chart from a spreadsheet to another application * how to perform an engineering simulation using a spreadsheet /* ++++ Glossary of Excel terms | ; zoom : A slide-changing effect that is achieved by dragging a thumbnail into a slide to create a link which zooms to that slide when clicked. ++++ */ /* ==== Further reading ==== */ /****************************************************************/ ==== Notes ==== Excel is a program for creating tables of data, and performing computation and analysis on that data. It is based on the very old idea of a spreadsheet, which was a large piece of paper used by accountants. Known data was entered into spaces on the spreadsheet and then calculations were performed to calculate new values. The process continued for as many iterations as was required to calculate the final, useful information. Excel's principles are exactly the same. You enter the data you know into cells, and use formulas to compute data you don't know in other cells. Excel takes care of figuring out the order in which the computations should be performed and the unknown data generated. | | **A** | **B** | **C** | ... | | **1** | A1 | B1 | C1 | | | **2** | A2 | B2 | C2 | | | **3** | A3 | B3 | C3 | | | ... | | | | | Excel calls its spreadsheets //workbooks//. A workbook consists of a number of rows and columns. At the intersection of every row and column there is a cell that can contain data. The cells inside a spreadsheet are therefore laid out in a square pattern. The columns are given letters and the rows are given numbers. Every cell therefore has a 'name' or 'coordinate' defining where it is located; the official term is //reference//. The first column is called ''A'' and the first row is called ''1'', so the top-left cell in the spreadsheet has the reference ''A1''. The cell to its right is ''B1'' and the cell below it is ''A2''. How many cells are in a spreadsheet? 17,179,869,184 arranged in 1,048,576 rows of 16,384 columns. Excel is very good at hiding the empty ones from you and so you'll never even see them unless you go looking. What happens after the column names run out of letters? Like in a cinema, after column ''Z'' come columns ''AA'', ''AB'', and ''AC''. After column ''AZ'' come columns ''BA'', ''BB'', and ''BC''. After ''ZZ'' come columns ''AAA'', ''AAB'', and ''AAC''. (There are not enough columns to ever reach ''ZZZ''.) === The interface === The UI should be very recognisable to anyone having experience with MS Word and PowerPoint. Excel has the same search feature as PowerPoint and Word, so it is easy to look up tools by name or description. I shall dare even to not reproduce its ribbons here. The least familiar part of Excel might be the way references work. I shall therefore use the space to explain them instead of describing pretty pictures of the user interface. === References === References work like map coordinates, with a letter for horizontal position and a number for vertical position. They come in two types: relative and absolute. == Relative references == Relative references are what most people (and almost all beginners) use almost all of the time. One or more letters (naming a column) and one or more digits (naming a row) make up a reference. Even though they are called "relative", they still identify a cell by its absolute position in the array of cells. So what makes them relative? The relativity comes from their behaviour when they are used in a formula inside a cell. Formulae can move, either because they are copied and pasted or because rows and cells are inserted or deleted. When a formula moves, Excel looks at the relative positions of (distance between) the original position and the new position. The difference is added to the column letter and row number in the reference. The effect is that the name of the referenced cell changes, so that the formula continues to reference a value stored at the same position relative to wherever the formula happens to be. Copying the formula ''B3+D3'' and pasting it two rows below the original position causes the references within it to change to ''B5+D5''. Moving that new formula one column to the right causes the references within it to change to ''C5+E5''. This is bad when many formulae need to refer to a single cell, such as an interest rate, no matter where they may be moved. == Absolute references == By placing a ''$'' in front of any letter or digit in a reference, it becomes //absolute//. This does not change how it refers to a cell, only how it behaves when the formula that it is part of is moved. In the case of absolute parts of references, they do not have the "distance" between the original and new position of the formula added to them. No matter where the formula is moved to, the absolute parts of the reference will always remain the same. Take our formula containing ''B3+D3'' and change it to ''$B$3+D3'', then perform the same two moves on it. Moving it down two rows changes it to ''$B$3+D5'', and moving that new formula right one column changes it to ''$B$3+E5''. The second relative cell referenced has moved to remain in the same relative position as the formula, whereas the first absolute reference has not. Change our formula to ''$B3+D$3'' and perform the same two moves on it. Moving it down two rows changes it to ''$B5+D$3'', and moving that new formula right one column changes it to ''$B5+E$3''. /****************************************************************/ ==== Videos ==== These 12 videos cover most of the essentials of Excel (assuming you already know how to use MS Word and PowerPoint). Use them to understand what features are there, and then explore the full capabilities of the interesting or useful features in more depth on your own. | {{ :class:il:il-04-01.mp4 | IL-04-01 data entry and basic editing }} | | 01. Basic editing | | {{ :class:il:il-04-02.mp4 | IL-04-02 rows and columns }} | | 02. Rows and columns | | {{ :class:il:il-04-03.mp4 | IL-04-03 autofill }} | | 03. Autofill | | {{ :class:il:il-04-04.mp4 | IL-04-04 tables }} | | 04. Tables | | {{ :class:il:il-04-05.mp4 | IL-04-05 formulas, cell references }} | | 05. Formulas, references | | {{ :class:il:il-04-06.mp4 | IL-04-06 ranges, functions }} | | 06. Ranges, functions | | {{ :class:il:il-04-07.mp4 | IL-04-07 conditional functions/formatting }} | | 07. Conditional functions, formatting | | {{ :class:il:il-04-08.mp4 | IL-04-08 filtering }} | | 08. Filtering | | {{ :class:il:il-04-09.mp4 | IL-04-09 visualisation }} | | 09. Visualisation | | {{ :class:il:il-04-10.mp4 | IL-04-10 freezing rows and columns }} | | 10. Freezing rows and columns | | {{ :class:il:il-04-11.mp4 | IL-04-11 import and export, CSV }} | | 11. Import and export, CSV | | {{ :class:il:il-04-13.mp4 | IL-04-13 extended example }} | | 13. Extended example | /* inline */ /* syllabus */ /* * Local Variables: * eval: (flyspell-mode) * eval: (ispell-change-dictionary "british") * eval: (flyspell-buffer) * End: */