[PDF] [PDF] advanced spreadsheets, handbook, Microsoft Excel 2010 - ITdeskinfo

[PDF] Excel 2010 Forumlas

4 déc 2010 · plus the complete book in a searchable PDF file Chapter 25: VBA Custom Function Examples Examples files for Excel 2010 Formulas

[PDF] Advanced Excel Formulas : Functions

The MAX function is nested within the AVERAGE formula in this example IF Formulas IF formulas are set up to provide a true or false statement after a calculation 

[PDF] Formulas, Functions, and Formatting

You may need to ask your instructor how to start Excel for your computer For a detailed example of the procedure summarized below, refer to the Office 2010 and 

[PDF] advanced spreadsheets, handbook, Microsoft Excel 2010 - ITdeskinfo

Dates must be entered by using the DATE function or as results of other formulas or functions For 14 november 2008, for example, use =DATE(2008,11,14) If you  

[PDF] Excel(R) 2010 Formulas & Functions Inside Out - Pearsoncmgcom

26 avr 2013 · aspx are trademarks of the Microsoft group of companies All other marks are property of their respective owners The example companies, 

[PDF] Advanced Formulas and Functions in Microsoft Excel

For example, the most used function in Excel is the SUM function, which is used to add together the data in selected cells The SUM function is written as -

[PDF] Excel 2010 Advanced - Welcome to download ebooks

Excel 2010 is a powerful spreadsheet application that allows users to These can range from simple formulae through to complex functions and In the example, column E has been used to hold the upper and lower sales thresholds

[PDF] Excel 2010 Advanced - STL Training

Contents Quick reference: Excel 2010 shortcuts What's New in Excel 2010 The best way to learn how lookup functions work is to look at an example

[PDF] Excel 2010 Introduction: Part I - Formulas, Functions and Formatting

Within each tab, the available icons are seperated into diffierent 'Groups' The name of the group is shown under the icons, at the bottom of the ribbon e g Font,  

[PDF] excel formulas tutorial

[PDF] excel macro examples for beginners

[PDF] excel macro examples free download

[PDF] excel macro examples pdf

[PDF] excel macro examples vba

[PDF] excel macro examples with coding

[PDF] excel macro examples xls

[PDF] excel macro examples youtube

[PDF] excel macro tutorial for beginners free

[PDF] excel macro tutorial for beginners in hindi

[PDF] excel macro tutorial for beginners in tamil

[PDF] excel macro tutorial for beginners with examples

[PDF] excel macro tutorial for beginners youtube

[PDF] excel macro tutorial for dummies

[PDF] excel macro vba tutorial for beginners

ITdesk.info ²

project of computer e-education with open access - Manual for digital literacy computer literacy movement building a modern society e - learning e - inclusion open access human rights to education and information

Advanced spreadsheets -

Microsoft Excel 2010

Handbook

Author: Mariza Maini

Author:

Mariza Maini

Translation:

Florentina Simion

Expert review:

Sharon Roy

The main title:

ITdesk.info - project of computer e-education with open access - Handbook for digital literacy

Subtitle:

Advanced spreadsheets - Microsoft Excel 2010, handbook

Title page:

Silǀija Bunić

Expert review - Croatian version:

Frane aesnić, mag.ing.electrotehnics, EdukaCentar, education institution , Croatia

Publisher:

Open society for idea exchange (ODRAZI), Zagreb

Place and year of publication:

Zagreb, 2015.

Teacher and Training Agency of the Republic of Croatia has approved the use of of this additional teaching resource in primary schools (Croatian version) class: 602-09/14-01/0419 reg. no. : 561-03-03/10-15-4

Zagreb, april 2015.

Copyright:

Feel free to copy, print and further distribute the whole or part of this, including the purpose of organized education, whether in public or private educational organizations, but only for noncommercial purposes (free of charge to end users of this publication) and with quoting the source (source: www.ITdesk.info - project of computer e-education with free access). Derived works without prior approval of the copyright holder (NGO Open society for idea exchange -

ODRAZI). Contact for permission: info@ITdesk.info

Foreword

Today's society is characterized by rapid growth and development of information technology (IT), which resulted in a great dependence of society, in a broad sense, of the knowledge and competence in the IT field. And although that dependence is growing from day to day, human rights to education and information is not extended to the IT area. There were problems that affect society as a whole, creating gaps and distancing people from the main reason and motivation for advancement, from opportunity. Being a computer illiterate, today it means to be a person unable to participate in the modern society, to be a person without opportunity. Despite the recognition of the European Commission, UNESCO, OECD and other relevant institutions on the necessity and benefits of literacy, there are still groups of people with hindered access to basic computer education (eg. persons with disabilities, people with learning difficulties, workers / and migrants, the unemployed, people living in remote places where education is not accessible). This manual, together with other materials published on ITdesk.info, is our contribution to the realization and promotion of human rights to education and information in the field of IT. We hope that this education will help you in mastering basic computer skills and we wish you to learn as much as you can and thus becoming an active member of the modern IT society.

Sincerely yours,

ITdesk.info team

ITdesk.info - project of computer e-education with free access ITdesk.info is licensed under a Creative Commons Attribution- Noncommercial-No Derivative Works 3.0 Croatia License ii/vi

CONTENT

FOREWORD .............................................................................................................................i

1. DESIGN ............................................................................................................................1

1.1 CELLS ...............................................................................................................................1

Apply automatic formatting/table style to a range of cells ........................................................1

Apply conditional fortmatting based on cell content .................................................................1

Create and apply custom number formats ................................................................................2

1.2 WORKSHEET ....................................................................................................................2

Copy, move worksheets between spreadsheets ........................................................................2

Divide window. Move, remove split bars ...................................................................................3

Hide, show rows, columns, worksheets .....................................................................................3

2. FUNCTIONS AND FORMULAS...........................................................................................5

The use of functions and formulas TODAY, NOW, DAY, MONTH, YEAR ......................................5

Use mathematical functions: ROUNDDOWN, ROUNDUP, SUMIF ...............................................6

Use statistical functions: COUNTIF, COUNTBLANK, RANK. .........................................................7

Use text functions: LEFT, RIGHT, MID, TRIM, CONCATENATE .....................................................8

Use financial function: FV, PV, PMT ...........................................................................................9

Use functions for references and search: VLOOKUP, HLOOKUP. .............................................. 10

Use function database: DSUM, DMIN, DMAX, DCOUNT, DAVERAGE ........................................ 10

Use level nested function ........................................................................................................ 11

Use 3-D references in the function sum................................................................................... 11

Use mixed references in formulas ........................................................................................... 12

3. CHARTS ..........................................................................................................................13

3.1 CREATING CHARTS .........................................................................................................13

Create a combined line-bar chart ............................................................................................ 13

Add a secondary axis to a chart ............................................................................................... 14

Change the chart type for a defined data series ...................................................................... 15

Add, delete a data series in the chart ...................................................................................... 15

3.2 DESIGN OF FIGURES .......................................................................................................16

Change the position of the chart title, legend, data title .......................................................... 16

Change the value axis: minimum, maximum number to display, major interval ....................... 16 Change display units on axis value without changing data source: hundreds, thousands,

millions ................................................................................................................................... 17

Format columns, bars, plot area, chart area to display an image ............................................. 17

4. ANALYSIS .......................................................................................................................18

4.1 USE OF TABLES ..............................................................................................................18

Create, modify a pivot table/datapilot..................................................................................... 18

ITdesk.info - project of computer e-education with free access ITdesk.info is licensed under a Creative Commons Attribution- Noncommercial-No Derivative Works 3.0 Croatia License iii/vi

Modify the data source and refresh the pivot table/datapilot ................................................. 21

Filter, sort the data in a pivot table / swivel chart .................................................................... 21

Automatically, manually group data in a pivot table / chart and rename groups ..................... 21

Use the table with one or two inputs / multiple operations ..................................................... 22

4.2 SORTING AND FILTERING...............................................................................................26

Sort data by multiple columns at the same time...................................................................... 26

Create a customized list and perform a custom sort ................................................................ 26

Automatic filter ....................................................................................................................... 27

Change the advanced filter options in the list .......................................................................... 28

Use an automatic calculation of subtotals ............................................................................... 29

4.3 SCENARIOS ....................................................................................................................30

Create scenario ....................................................................................................................... 30

View, edit, delete scenarios .................................................................................................... 30

Create a scenario summary report .......................................................................................... 30

5. EVALUATION AND SUPERVISION ...................................................................................32

5.1 EVALUATION .................................................................................................................32

Set, edit validation criteria for data entry into a range of cells such as integer, decimal, list, date

and time ................................................................................................................................. 32

Enter input message and the error message............................................................................ 32

5.2 SUPERVISION .................................................................................................................32

Subscribe precedent, dependent cells. Tracking errors. ........................................................... 32

Show all formulas in a worksheet, rather than the value of the results .................................... 34

Insert, edit, delete, show, hide comments/notes .................................................................... 34

6. IMPROVE PRODUCTIVITY ..............................................................................................36

6.1 CELLS APPOINTMENT ....................................................................................................36

Appoint cell ranges, delete names for cell ranges .................................................................... 36

Use named cell ranges in functions ......................................................................................... 36

6.2 PASTE SPECIAL ...............................................................................................................36

Use the paste special options: add, subtract, multiply, divide .................................................. 36

Use the Paste special options: values/numbers, transpose ...................................................... 37

6.3 TEMPLATES ....................................................................................................................37

Make a workbook based on an existing template .................................................................... 37

Modify template ..................................................................................................................... 38

6.4 CONNECT, IMPORT AND EXPORT ..................................................................................38

Edit, insert, remove a hyperlink ............................................................................................... 38

Link data in the workbook, between spreadsheets, between applications ............................... 39

Import delimited data from a text file ..................................................................................... 40

ITdesk.info - project of computer e-education with free access ITdesk.info is licensed under a Creative Commons Attribution- Noncommercial-No Derivative Works 3.0 Croatia License iv/vi

6.5 AUTOMATION ...............................................................................................................40

Create macros such as change page setup, apply a custom number format, apply the automatic

formatting to a cell range, insert fields in the header, footer worksheet ................................. 40

Run a macro ............................................................................................................................ 41

Assign a macro to a button in the toolbar................................................................................ 41

7. COLLABORATIVE EDITING ..............................................................................................42

7.1 TRACKING AND REVIEWING ..........................................................................................42

Turn off, track off changes. Tracking changes in a worksheet using the preview pane. ............ 42

Accept, reject the changes to te worksheet ............................................................................. 42

Compare and merge workbooks ............................................................................................. 42

7.2 PROTECTION ..................................................................................................................43

Add, remove password protection for workbooks: opening, modification ............................... 43

Protection, removal of the protection cell, worksheet with a password .................................. 44

Hide, unhide formulas ............................................................................................................. 44

8. TERMS OF SERVICES ......................................................................................................47

ITdesk.info - project of computer e-education with free access ITdesk.info is licensed under a Creative Commons Attribution- Noncommercial-No Derivative Works 3.0 Croatia License 1/47

1. DESIGN

1.1 CELLS

A cell is an intersection between a column and row. In order to increase the transparency, ease of use and for a better visual presentation Excel offers the ability to create cells at predefined templates, which a user can format, when desired. Apply automatic formatting/table style to a range of cells

Select the cells you want to shape

Go to Home -> Styles -> Format as table

Select the desired style

Apply conditional fortmatting based on cell content Sometimes it is necessary to create the data in such a way that the design is changed depending on a condition which is given before. This allows,, a different color to highlight certain numbers for you to have a special meaning (eg. temperature more than the average). In order to change the conditional formatting you need to: Select a cell or a range of cells over which you wish to apply conditional formatting

Go to Home -> Styles -> Conditional formatting

Select the desired conditional formatting.

Let's say you want to highlight the temperature more than the average, then to highlight the make comparisons, so you can choose the color you want the cells to be painted or assigned with. ITdesk.info - project of computer e-education with free access ITdesk.info is licensed under a Creative Commons Attribution- Noncommercial-No Derivative Works 3.0 Croatia License 2/47

Create and apply custom number formats

By applying different formats of the numbers they can be displayed as a percentage, date, currency, etc.

Select the cells you want to shape

Go to Home -> Number

Click Dialog Box Launcher next to number (or press CTRL+1). In the Category list, click the format you want to use and then adjust the settings as needed.

1.2 WORKSHEET

An Excel worksheet is the grid of cells where you can type the data. The grid divides your worksheet into rows and columns.

Copy, move worksheets between spreadsheets

First select sheets you want to move or copy.

One sheet is selected by clicking on the a sheet tab. Select two or more adjacent sheets: click on a tab the first list then hold down the SHIFT key while clicking the last tab lists you want to choose. Select two or more sheets of non-adjacent: click on a tab the first list then hold down the Ctrl key and simultaneously click on other sheets you want to select All sheets in a workbook: right-click a sheet tab and then click on the shortcut menu on Select all sheets. - Go to Home -> Cells -> Formatting, and then under Organize sheets select Move or

Copy sheet.

- A shortcut for this option is to right click the mouse after selecting the sheets to open the shortcut menu. - In the dialog box to Move or copy the sheet in The Book, you can click on an existing workbook in which you want to move or copy the selected sheets, or you can click on a new book that you selected, and move or copy sheets in a new workbook. ITdesk.info - project of computer e-education with free access ITdesk.info is licensed under a Creative Commons Attribution- Noncommercial-No Derivative Works 3.0 Croatia License 3/47 - In the Before sheet field you can choose exactely to which location in another workbook you want to move or copy worksheets (before the selected sheet, at the end). To copy sheets rather than move them, the dialog box, tick the check box in front of the

Create a copy.

NOTE: When you create a copy of a worksheet, a duplicate of a worksheet is created in the destination workbook and appears only in the destination workbook. When you move the worksheet, it is removed from the original workbook and appears only in the destination workbook.

Split window. Move, remove split bars

Standard division implemented by Excel is a division of the window into 4 sections. It is achieved by selecting View -> Window -> Split. Click again on the Split and the division is removed. For an arbitrary horizontal division into two parts is necessary to select the row below the row on which you want to split the sheet, and click Split. The division is removed by clicking

Split.

The window can also be divided by columns. Select the column to the right of the column where you want to split and then use Split. Worksheet can be split both per rows and columns. Select the cell below and on the right of place where you want to split, then click

Split.

Another way to split a window is to select a cell and then quickly click on the bar two times for horizontal or vertical division. The horizontal division tape is located above the slider for vertical movement and the vertical division tape is on the right of the slider for horizontal shift. Bar division can be moved as desired so with a left click on the mouse you can drag it to the desired location.

Hide, show rows, columns, worksheets

- To hide one or more rows or columns: select the rows or columns you want to hide. Home -> Cells -> Format. For hiding there are 2 available options: - In the section Visibility click on the Hide rows or Hide columns. - In the section Size click on the Height row or Column width, and then enter 0 in the frame Height row or Column width. Shortcut to hide columns/rows is right clicking the mouse on the row or column (or more) and then click on Hide. - To show a hidden row select the line above and below the hidden row you want to view and for column select adjacent columns. Home -> Cells -> Format. For detection there are available 2 options: - In the section Visibility click on Unhide rows or Unhide columns. ITdesk.info - project of computer e-education with free access ITdesk.info is licensed under a Creative Commons Attribution- Noncommercial-No Derivative Works 3.0 Croatia License 4/47 - In the section Cell size click on Height rows or Column width, and then insert the desired value. Shortcut to detect rows / columns is to click the selection of visible rows and columns that surround the hidden rows and columns, right-click and then click Unhide. - A special case is the first detection of a hidden row or column. To select the first hidden rows or columns in a worksheet, you can follow the below steps:

In the name box next to the formula bar enter A1

Home -> Edit -> Find and select -> Go to. In the frame Reference enter A1 and then click OK. For detection the previously listed options are available. In hiding the worksheets you should keep in mind that while you can hide multiple worksheets, you can only discover each sheet separately. For hiding the worksheets select the desired sheet and then in the Home tab -> Cells ->

Format. Under Visibility click on the Hide sheet.

For detection go to Home tab -> Cells -> Format. Under Visibility click on the Hide sheet. Within Unhide double-click the name of the hidden sheet you want to display. ITdesk.info - project of computer e-education with free access ITdesk.info is licensed under a Creative Commons Attribution- Noncommercial-No Derivative Works 3.0 Croatia License 5/47

2. FUNCTIONS AND FORMULAS

Functions are predefined formulas that perform calculations by using specific values, called arguments, in a precisely defined order or structure. The functions can be performed by various calculations. Formulas and functions are available in the Formulas tab. In most cases, selecting the desired formula opens additional window with which you can specify arguments.

For example, help window for SUM function:

The use of functions and formulas TODAY, NOW, DAY, MONTH, YEAR

Use date and time: TODAY, NOW, DAY, MONTH, YEAR.

- TODAY() -> returns the current date. If you select a cell in the formula bar and enter =TODAY() the cell will display the current date. - NOW() -> returns the current date and time. If you select a cell in the formula bar and enter =NOW() in the cell it will display the current time. - DAY(ordinal number) -> Ordinal number is the date of the day your are trying to find. Dates must be entered by using the DATE function or as results of other formulas or functions. For 14 november 2008, for example, use =DATE(2008,11,14). If you select in cell A1 using the tool TODAY() store the current date, and then highlight the cell A2 and enter the formula bar =DAY(A1) you will get the day of the month (ranging from

1 to 31).

ITdesk.info - project of computer e-education with free access ITdesk.info is licensed under a Creative Commons Attribution- Noncommercial-No Derivative Works 3.0 Croatia License 6/47 - MONTH(ordinal number) and YEAR(ordinal number) work on the same principle as DAY(ordinal number) except that MONTH returns a number that represents the month, and YEAR returns the year. Use mathematical functions: ROUNDDOWN, ROUNDUP, SUMIF - ROUNDDOWN(number; number of digits) -> number rounded to the lower number closer to zero. Receives 2 arguments, namely the number of which is desired to encircle the lower value and the number of digits in the code that wants to encircle. If the number of digits is greater than 0 (zero), the number is rounded down to the specified number of decimal places. If the sum of digits equal to 0, the number is rounded to the nearest whole number. If the number of digits is less than 0, the number is rounded down to the specified number of places to the left of the decimal point.

Examples:

=ROUNDDOWN(5,8; 0) -> rounded to a smaller number so that there are no decimal places and the result will be 5. =ROUNDDOWN(5,123456; 3) -> completes the given number 3 to decimal places and the result will be 5,123 - ROUNDUP(number; number of digits) -> rounded up to the number further from 0. Receives 2 arguments, which are the number of which is desired to round up to a higher value and the number of digits in the code that wants to encircle. If the number of digits is greater than 0 (zero), the number is rounded up to the specified number of decimal places. If the sum of digits is equal to 0, the number is rounded to the nearest whole number. If the number of digits is less than 0, the number is rounded up to the left of the decimalpoint.

Examples:

=ROUNDUP(52,4; 0) -> the larger the number is rounded with no decimal places and the result is 53 =ROUNDUP(3,14182; 3) -> number rounded to three decimal places and the result is

3,142.

- SUMIF(range;criteria[range_sum]) -> is used for adding value in a range that satisfyquotesdbs_dbs17.pdfusesText_23