Spreadsheets for Accounting Introduction to Excel Spreadsheets 1

Spreadsheets for Accounting Introduction to Excel Spreadsheets 1 VIII Środowiskowe Warsztaty Doktorantów Pokrzywna 05. 06. 2014

Association of Accounting Technicians (AAT) Qualifications Izabela Drabik MAAT email: teacher@seperacollege. com 2 VIII Środowiskowe Warsztaty Doktorantów Pokrzywna 05. 06. 2014

Course Content: • Introduction to Excel Spreadsheets • Mathematical Operators • Order of Operations • Basic Terms in Excel • Mathematical Operators and Order of Operations • Five Time-saving Ways to Insert Data into Excel • Seven Basic Excel Formulas For Your Workflow • Charts • Pivot Tables • Summary 3

Introduction to Excel Spreadsheets • An Excel spreadsheet is helpful for displaying information in an organised way but Excel can be used for so much more. • Its powerful calculation ability enables you to enter, manipulate, and analyse numbers. • To take advantage of this function, you need to learn about formulas, which are essentially math equations. • Here in Sepera College we describe how to create basic and slightly more complex formulas in Excel. 4

5

Mathematical Operators • • Addition – plus sign ( + ) Subtraction – minus sign ( - ) Division – forward-slash ( / ) Multiplication – asterisk ( * )( on a computer/laptop keyboard- above number 8) • Exponentiation – caret ( ^ ) (on a computer/laptop keyboard above number 6) 6

Order of Operations If more than one operator is used in a formula, Excel follows a specific order to perform the mathematical operations. An easy way to remember the order of operations is to use the acronym BEDMAS. Brackets Exponents Division Multiplication Addition Subtraction 7

Basic Terms in Excel There are two basic ways to perform calculations in Excel: • Formulas - in Excel, a formula is an expression that operates on values in a range of cells or a cell. For example, =A 1+A 2+A 3, which finds the sum of the range of values from cell A 1 to cell A 3. The most notable difference is that Excel formulas start with the equal sign (=) instead of ending with it. Excel formulas look like =3+2 instead of 3 + 2 =. • Functions- are predefined formulas in Excel. They eliminate manual entry of formulas while giving them human-friendly names. For example: =SUM(A 1: A 3). The function sums all the values from A 1 to A 3. Where do we find functions in Excel Spreadsheets? 8

Five Time-saving Ways to Insert Data into Excel 1. Simple insertion: Typing a formula inside the cell - typing a formula in a cell or the formula bar is the most straightforward method of inserting basic Excel formulas. The process usually starts by typing an equal sign, followed by the name of an Excel function. 9

Five Time-saving Ways to Insert Data into Excel 2. Using Insert Function Option from Formulas Tab If you want full control of your functions insertion, using the Excel Insert Function dialogue box is all you ever need. 10

Five Time-saving Ways to Insert Data into Excel 3. Selecting a Formula from One of the Groups in Formula Tab This option is for those who want to delve into their favourite functions quickly. To find this menu, navigate to the Formulas tab and select your preferred group. 11

Five Time-saving Ways to Insert Data into Excel 4. Using Auto. Sum Option For quick and everyday tasks, the Auto. Sum function is your go-to option. So, navigate to the Home tab, in the far-right corner, and click the Auto. Sum option. 12

Five Time-saving Ways to Insert Data into Excel 5. Quick Insert: Use Recently Used Tabs If you find re-typing your most recent formula a monotonous task, then use the Recently Used menu. It’s on the Formulas tab, a third menu option just next to Auto. Sum. 13

Seven Basic Excel Formulas For Your Workflow 1. SUM The SUM function is the first must-know formula in Excel. It usually aggregates values from a selection of columns or rows from your selected range. =SUM(number 1, [number 2], …) Example: =SUM(B 2: G 2) – A simple selection that sums the values of a row. =SUM(A 2: A 8) – A simple selection that sums the values of a column. =SUM(A 2: A 7, A 9, A 12: A 15) – A sophisticated collection that sums values from range A 2 to A 7, skips A 8, adds A 9, jumps A 10 and A 11, then finally adds from A 12 to A 15. =SUM(A 2: A 8)/20 – Shows you can also turn your function into a formula. 14

SUM - Example 15

2. AVERAGE • The AVERAGE function should remind you of simple averages of data such as the average number of shareholders in a given shareholding pool. • =AVERAGE(number 1, [number 2], …) • Example: • =AVERAGE(B 2: B 11) – Shows a simple average, also similar to (SUM(B 2: B 11)/10) 16

2. AVERAGE 17

3. COUNT The COUNT function counts all cells in a given range that contain only numeric values. =COUNT(value 1, [value 2], …) Example: COUNT(A: A) – Counts all values that are numerical in A column. However, you must adjust the range inside the formula to count rows. COUNT(A 1: C 1) – Now it can count rows. 18

3. COUNT - Example 19

4. COUNTA • Like the COUNT function, COUNTA counts all cells in a given rage. However, it counts all cells regardless of type. That is, unlike COUNT that only counts all numerics, it also counts dates, times, strings, logical values, errors, empty string, or text. =COUNTA(value 1, [value 2], …) • Example: • COUNTA(C 2: C 13) – Counts rows 2 to 13 in column C regardless of type. However, like COUNT, you can’t use the same formula to count rows. You must make an adjustment to the selection inside the brackets – for example, COUNTA(C 2: H 2) will count columns C to H • 20

4. COUNTA 21

5. IF The IF function is often used when you want to sort your data according to a given logic. The best part of the IF formula is that you can embed formulas and function in it. =IF(logical_test, [value_if_true], [value_if_false]) Example: =IF(C 2<D 3, ‘TRUE, ’ ‘FALSE’) – Checks if the value at C 3 is less than the value at D 3. If the logic is true, let the cell value be TRUE, else, FALSE =IF(SUM(C 1: C 10) > SUM(D 1: D 10), SUM(C 1: C 10), SUM(D 1: D 10)) – An example of a complex IF logic. First, it sums C 1 to C 10 and D 1 to D 10, then it compares the sum. If the sum of C 1 to C 10 is greater than the sum of D 1 to D 10, then it makes the value of a cell equal to the sum of C 1 to C 10. Otherwise, it makes it the SUM of C 1 to C 10. 22

5. IF - Example 23

6. TRIM The TRIM function makes sure your functions do not return errors due to unruly spaces. It ensures that all empty spaces are eliminated. Unlike other functions that can operate on a range of cells, TRIM only operates on a single cell. Therefore, it comes with the downside of adding duplicated data in your spreadsheet. =TRIM(text) Example: TRIM(A 2) – Removes empty spaces in the value in cell A 2. 24

6. TRIM - Example 25

7. MAX & MIN The MAX and MIN functions help in finding the maximum number and the minimum number in a range of values. =MIN(number 1, [number 2], …) Example: =MIN(B 2: C 11) – Finds the minimum number between column B from B 2 and column C from C 2 to row 11 in both columns B and C. =MAX(number 1, [number 2], …) Example: =MAX(B 2: C 11) – Similarly, it finds the maximum number between column B from B 2 and column C from C 2 to row 11 in both columns B and C. 26

7. MAX & MIN 27

Window – Freeze Panes We can scroll up, down and across our worksheet using the scroll bars position around the working area of the worksheet. However, sometimes if we have a large amount of data, we want still to be able to see our raw or column text headings so that we know what data we are looking at. In this situation we can freeze top row, first column or freeze panes. How do we freeze panes in Excel? 28

Charts in Excel Within spreadsheet package there a variety of different chart types available for the visual representation of data. What are the types of charts? • Bar or column • Line • Pie • Scatter • Doughnut • Bubble How to create a chart? 29

Pivot Tables What is a Pivot Table? • It is a very powerful reporting tool fund in Spreadsheet Packages • It allows as to generate and extract meaningful information from a large table of information within a matter of minutes! • It creates an active summary from a worksheet containing numerous rows of data. How do we create a Pivot table in Excel Spreadsheets? 30

SUMMARY- Which Statistical Functions do we explain at Sepera College? Activities include the following functions: • COUNTA • MAX Explained • MIN • AVERAGE • COUNT IF = COUNTIF( range, criteria) • SUMIF = SUMIF( range, criteria, [sum, range]) • ROUNDUP = ROUNDUP(number, num_digits) • ROUNDDOWN =ROUNDOWN(number, num_digits) • FORECAST =FORECAST( x, y-range, associated x-range) 31

COUNT IF =COUNTIF(B 4: B 11, "*r") 32