plus the complete book in a searchable PDF file. Excel Excel® Formulas by John of Microsoft Corporation in the United States and/or other countries. All other Microsoft MVP award every year since Excel utilities, including the Power Utility Pak, an award-winning add-in for Excel. Microsoft Excel 2 Formulas, Functions, and Formatting. Introduction. In Chapter 1, you learned how to enter data, sum values, format a worksheet to make. Appendices. 7. Section 2 The Basics. 9. Windows Concepts. Getting Help. Microsoft Excel Help. Section 3 Move Around and Enter Data. Moving.

Author: | JUNIOR OKAMURA |

Language: | English, Spanish, Japanese |

Country: | Seychelles |

Genre: | Technology |

Pages: | 629 |

Published (Last): | 27.03.2016 |

ISBN: | 152-7-18961-867-4 |

Distribution: | Free* [*Register to download] |

Uploaded by: | SHIRLENE |

A collection of useful Excel formulas for sums and counts, dates and times, text Detailed formula examples for key functions, including VLOOKUP, INDEX, MATCH, Get over Excel shortcuts for Windows and Mac in one handy PDF. Microsoft®. Excel®. Formulas & Functions. Inside Out functions have been tested in Microsoft Office Excel through Excel ALT+= Inserts a SUM formula. 2. CTRL+TAB Switches between open Excel windows. 3. CTRL+A – this has various scenarios: a. If you are in regular data range.

Subtraction 6 How can you wrap the text within a cell? You must select the text you want to wrap, and then click wrap text from the home tab and you can wrap the text within a cell. Macros are used for iterating over a group of tasks. Users can create macros for their customized repetitive functions and instructions. Macros can be either written or recorded depending on the user. Earlier versions of Excel used XLM. VBA was introduced in Excel 5 and mostly used now. Yes, it is possible. By entering a password, you can secure your sheet from getting copied by others. To enable graphical representation of the data in Excel, charts are provided. A user can use any chart type, including column, bar, line, pie, scatter, etc.

Excel formulas and functions pdf. To locate or change cells referenced in a formula. Entering a formula containing a function. Microsoft Excel Center for Teaching and Learning.

Prepared by: Ali Abdallah. Excel 3: Formulas and Functions. Excel Excel is a powerful spreadsheet application that allows users to produce tables containing calculations and ebook self help health the complete idiots guide to fitness pdf graphs. Customizing the Excel Program Window. It is possible in Excel to create formulas by using the older functions.

Data pane The area of the window that displays the result set of your query. Data points Individual values that are plotted in a chart. Related data points make up a data series. Data points are represented by bars, columns, lines, slices, dots, and other shapes. These shapes are called data markers. Data region A range of cells that contains data and that is bounded by empty cells or datasheet borders.

Data series Related data points that are plotted in a chart and originate from datasheet rows or columns. Each data series in a chart has a unique color or pattern.

You can plot one or more data series in a chart. Pie charts have only one data series. Data source A stored set of "source" information used to connect to a database. A data source can include the name and location of the database server, the name of the database driver, and information that the database needs when you log on.

Data source driver A program file used to connect to a specific database.

Each database program or management system requires a different driver. Data table A range of cells that shows the results of substituting different values in one or more formulas.

There are two types of data tables: one-input tables and two-input tables. Data table in charts A grid that can be added to some charts and contains the numeric data used to create the chart. The data table usually is attached to the horizontal axis of the chart and replaces the tick-mark labels on the horizontal axis. Data validation An Excel feature that you can use to define restrictions on what data can or should be entered in a cell, and to display messages that prompt users for correct entries and notify users about incorrect entries.

Database A collection of data related to a particular subject or purpose. Within a database, information about a particular entity, such as an employee or order, is categorized into tables, records, and fields. DDE conversation The interaction between two applications that are communicating and exchanging data through special functions and code known as dynamic data exchange DDE.

Default startup workbook The new, unsaved workbook that's displayed when you start Excel. The default startup workbook is displayed only if you haven't included other workbooks in the XLStart folder. Default workbook template The Book. Excel uses the template to create a blank workbook when you start Excel or create a new workbook without specifying a template.

Default worksheet template The Sheet. Excel uses the template to create a blank worksheet when you add a new worksheet to a workbook. Dependents Cells that contain formulas that refer to other cells. Destination area The range of cells that you select to hold the summarized data in a consolidation.

The destination area can be on the same worksheet as the source data or on a different worksheet. A worksheet can contain only one consolidation. Detail data For automatic subtotals and worksheet outlines, the subtotal rows or columns that are totaled by summary data.

Detail data is typically adjacent to and either above or to the left of the summary data. Drop lines In line and area charts, lines that extend from a data point to the category x axis. Useful in area charts to clarify where one data marker ends and the next begins.

Drop-down list box A control on a menu, toolbar, or dialog box that displays a list of options when you click the small arrow next to the list box.

E Top of Page Embedded chart A chart that is placed on a worksheet rather than on a separate chart sheet. Embedded charts are beneficial when you want to view or print a chart or a PivotChart with its source data or other information in a worksheet. Error bars Usually used in statistical or scientific data, error bars show potential error or degree of uncertainty relative to each data marker in a series. Excel add-in Components that can be installed on your computer to add commands and functions to Excel.

These add-in programs are specific to Excel. Excel table Formerly known as an Excel list, you can create, format, and expand an Excel table to organize the data on your worksheet. Expression A combination of operators, field names, functions, literals, and constants that evaluates to a single value.

External data Data that is stored outside of Excel. External data range A range of data that is brought into a worksheet but that originates outside of Excel, such as in a database or text file. In Excel, you can format the data or use it in calculations as you would any other data. External reference A reference to a cell or range on a sheet in another Excel workbook, or a reference to a defined name in another workbook.

F Top of Page Field database A category of information, such as last name or order amount, that is stored in a table. When Query displays a result set in its Data pane, a field is represented as a column. PivotTables have row, column, page, and data fields.

PivotCharts have series, category, page, and data fields. Fill handle The small black square in the lower-right corner of the selection. When you point to the fill handle, the pointer changes to a black cross. Filter To display only the rows in a list that satisfy the conditions you specify. You use the AutoFilter command to display rows that match one or more specific values, calculated values, or conditions.

Font A graphic design applied to all numerals, symbols, and alphabetic characters. Also called type or typeface. Arial and Courier New are examples of fonts. As regards services which use rainbow tables e.

Password-Find , it takes up to several seconds to remove protection. In addition, password-cracking programs can brute-force attack passwords at a rate of hundreds of thousands of passwords a second, which not only lets them decrypt a document, but also find the original password. Due to the CSP, an Excel file can't be decrypted, and thus the password to open can't be removed, though the brute-force attack speed remains quite high. The situation changed fundamentally in Excel , where the modern AES algorithm with a key of bits started being used for decryption, and a 50,fold use of the hash function SHA1 reduced the speed of brute-force attacks down to hundreds of passwords per second.

In Excel , the strength of the protection by the default was increased two times due to the use of a ,fold SHA1 to convert a password to a key. Microsoft Excel Viewer Microsoft Excel Viewer was a freeware program for viewing and printing spreadsheet documents created by Excel.

Excel Viewer is similar to Microsoft Word Viewer in functionality. There is not a current version for the Mac. Numeric precision Main article: Numeric precision in Microsoft Excel Excel maintains 15 figures in its numbers, but they are not always accurate: the bottom line should be the same as the top line.

Despite the use of figure precision, Excel can display many more figures up to thirty upon user request. But the displayed figures are not those actually used in its computations, and so, for example, the difference of two numbers may differ from the difference of their displayed values.

Although such departures are usually beyond the 15th decimal, exceptions do occur, especially for very large or very small numbers. Serious errors can occur if decisions are made based upon automated comparisons of numbers for example, using the Excel If function , as equality of two numbers can be unpredictable.

Although this number has a decimal representation that is an infinite string of ones, Excel displays only the leading 15 figures. In the second line, the number one is added to the fraction, and again Excel displays only 15 figures. In the third line, one is subtracted from the sum using Excel. Because the sum in the second line has only eleven 1's after the decimal, the difference when 1 is subtracted from this displayed value is three 0's followed by a string of eleven 1's.

However, the difference reported by Excel in the third line is three 0's followed by a string of thirteen 1's and two extra erroneous digits.

This is because Excel calculates with about half a digit more than it displays. Excel works with a modified version of the IEEE specification. See the main article for details. Besides accuracy in user computations, the question of accuracy in Excel-provided functions may be raised. Particularly in the arena of statistical functions, Excel has been criticized for sacrificing accuracy for speed of calculation.

Microsoft has announced some of these issues are addressed in Excel In the case of excessively large results, Excel will return the error warning NUM!

Date range Excel supports dates with years in the range , except that December 31, can be entered as 0 and is displayed as 0-jan Converting a fraction of a day into hours, minutes and days by treating it as a moment on the day January 1, , does not work for a negative fraction.

A similar problem occurs when a text happens to be in the form of a floating point notation of a number. In these cases the original exact text cannot be recovered from the result. This issue has caused a well known problem in the analysis of DNA , for example in bioinformatics.