Excel formulas pdf with example 2018 download free

Extend the selection of cells by one cell. Extend the selection of cells to the last nonblank cell in the same column or row as the active cell, or if the next cell is ….

Did you just remove a name or cell used by the formula? Is the denominator zero? Adjust column Excel Formula Cheatsheets. If you need specific calculations, this is a good place to start. Below, you'll get a number of cheatsheets referencing the general formulas you need.

Discover formulas on how to do basic math calculations, compose conditional formulas, perform …. Cheat Sheet of Excel formulas and function is always a customized worksheet where we can have all those function details, shortcut keys to execute any function or formulas, a custom way to use 2 or more functions, and guideline to use them. If you want to print a copy, your PDF viewer should have an option for printing two pages on one sheet of paper, but you may need to start with page 2 to get it to print facing pages correctly.

Print this cover page separately. You can use any of these methods to convert PDF files to Excel. You can also implement these methods using VBA.. I list the keyboard shortcuts in the Tables below. File Type PDF Excel Cheat Sheet Formulas moth multiple choice answers, guided cell answer, clay mineralogy mcgraw hill series in the geological sciences, finite element analysis gokhale qidongore, silbey solutions manual, vikings history mythology norse mythology norse gods norse myths viking history, digital logic applications.

Different Excel Formulas with respective examples are explained below. Details: Excel Formula Cheat Sheet Pdf Excel Microsoft Excel The spreadhseet programs power lies in excel functions and excel formulas which allow all sorts of data analysis. Excel formula cheat sheet pdf. Type return or tab to move to the next cell when you have finished entering the formula.

Is the denominator zero. Office cheat sheets. Outlook Mail for Windows. View PDF. Uses an index to choose a value from a reference or array.

Returns a reference indicated by a text value. Looks up values in a reference or array. Returns a reference offset from a given reference. Returns the row number of a reference. Returns the number of rows in a reference. Looks in the first column of an array and moves across the row to return the value of a cell.

Chooses a value from a list of values. Returns data stored in a PivotTable report. Creates a shortcut or jump that opens a document stored on a network server, an intranet, or the Internet. Returns the transpose of an array. Returns the number of areas in a reference. Checks to see if two text values are identical. Converts text to lowercase. Capitalizes the first letter in each word of a text value.

Removes spaces from text. Converts text to uppercase. Returns the character specified by the code number. Removes all nonprintable characters from text.

Returns a numeric code for the first character in a text string. Formats a number as text with a fixed number of decimals. Extracts the phonetic furigana characters from a text string. Repeats text a given number of times. Substitutes new text for old text in a text string. Converts its arguments to text. Converts a text argument to a number.

Changes full-width double-byte English letters or katakana within a character string to half-width single-byte characters. Finds one text value within another case-sensitive. Returns the leftmost characters from a text value.

Returns the number of characters in a text string. Returns a specific number of characters from a text string starting at the position you specify. Replaces characters within text. Returns the rightmost characters from a text value. Finds one text value within another not case-sensitive. Converts a number from one measurement system to another.

Tests whether two values are equal. Returns the error function. Returns the complementary error function. Tests whether a number is greater than a threshold value. Returns the complementary ERF function integrated between x and infinity. Returns the modified Bessel function In x. Returns the Bessel function Jn x. Returns the modified Bessel function Kn x. Returns the Bessel function Yn x. Converts a binary number to decimal. Converts a binary number to hexadecimal.

Converts a decimal number to octal. Converts a hexadecimal number to binary. Converts a hexadecimal number to decimal. Converts a hexadecimal number to octal. Returns the absolute value modulus of a complex number. Returns the imaginary coefficient of a complex number. Returns the argument theta, an angle expressed in radians. Returns the complex conjugate of a complex number. Returns the cosine of a complex number. Returns the quotient of two complex numbers. Returns the exponential of a complex number.

Returns the natural logarithm of a complex number. Returns the base logarithm of a complex number. Returns the base-2 logarithm of a complex number. Returns a complex number raised to an integer power. Returns the product of complex numbers. Returns the real coefficient of a complex number. Returns the sine of a complex number. Returns the square root of a complex number. Returns the difference between two complex numbers. Returns the sum of complex numbers. Converts an octal number to binary.

Converts an octal number to decimal. Converts an octal number to hexadecimal. Returns the depreciation for each accounting period by using a depreciation coefficient. Returns the depreciation for each accounting period. Converts a dollar price, expressed as a fraction, into a dollar price, expressed as a decimal number. Converts a dollar price, expressed as a decimal number, into a dollar price, expressed as a fraction. Returns the straight-line depreciation of an asset for one period.

Returns the depreciation of an asset for a specified period by using the fixed-declining balance method. Returns the depreciation of an asset for a specified period by using the double-declining balance method or some other method that you specify.

Returns the effective annual interest rate. Returns the future value of an investment. Returns the interest payment for an investment for a given period. Returns the internal rate of return for a series of cash flows. Returns the internal rate of return where positive and negative cash flows are financed at different rates. Returns the annual nominal interest rate. Returns the number of periods for an investment.

Returns the net present value of an investment based on a series of periodic cash flows and a discount rate. Returns the present value of an investment. Returns the interest rate per period of an annuity. Returns the yield on a security that pays periodic interest. Returns the accrued interest for a security that pays periodic interest.

Returns the accrued interest for a security that pays interest at maturity. Returns the number of days from the beginning of the coupon period to the settlement date. Format Axis Step 1: Select the chart axis. Step 2: Right-click the chart axis. Step 3: Click Format Axis. The Format Axis task pane appears as shown in the image below. The small icons at the top of the pane are for more options. Step 5: Select the required Axis Options. If you click on a different chart element, you will see that the task pane automatically updates to the new chart element.

Step 6: Select the Chart Title. Step 7: Select the required options for the Title. The following steps will show how to make a combo chart. Step 1: Select the Data.

Step 2: Click on Combo Charts. As you scroll on the available Combo Charts, you will see the live preview of the chart. In addition, Excel displays guidance on the usage of that particular type of Combo Chart as shown in the image given below. The Combo Chart will be displayed. Step 1: Click on the chart. The Ribbon now displays all the options of Chart Design. Step 1: Click on Quick Layout. Different possible layouts will be displayed. Step 2: As you move on the layout options, the chart layout changes to that particular option.

A preview of how your chart will look is shown. Step 3: Click on the layout you like. The chart will be displayed with the chosen layout. Follow the steps given below to understand this. You can see that the data will be swapped between X-Axis and Y-Axis. Step 1: Click on Select Data. The Select Data Source window appears as shown in the image given below. Step 2: Select the Chart Data Range.

The Change Chart Type window appears. Step 2: Select the Chart Type you want. The Chart will be displayed with the type chosen. The Move Chart window appears.

You can also connect them to their data points with Leader Lines on all charts and not just pie charts, which was the case in earlier versions of Excel. Step 1: Select your data. Step 3: Click on the 3-D Bubble Chart. The 3-D Bubble Chart will appear as shown in the image given below. Step 4: Click on the chart and then click on Chart Elements. Step 5: Select Data Labels from the options. Select the small symbol given on the right of Data Labels. Different options for the placement of the Data Labels appear.

A list of option appears as shown in the image given below. Make sure that only one Data Label is selected while formatting. This will enable you to apply your custom Data Label formatting quickly to the other data points in the series.

Step 1: Click on the Data Label, whose Fill color you want to change. Double click to change the Fill color for just one Data Label. Choose the Color you want and then make the changes. Step 4: Click Effects and choose the required effects.

For example, you can make the label pop by adding an effect. Just be careful not to go overboard adding effects. All the other data labels will acquire the same effect. Step 1: Right-click the Data Label you want to change. Resize a Data Label Step 1: Click on the data label. Step 2: Drag it to the size you want. Add a Field to a Data Label Excel has a powerful feature of adding a cell reference with explanatory text or a calculated value to a data label.

Let us see how to add a field to the data label. Step 2: Right-click on a data label. A list of options will appear. A Data Label Reference window appears. The explanatory text appears in the data label. Step 6: Resize the data label to view the entire text. It is helpful when you have placed a data label away from a data point. In earlier versions of Excel, only the pie charts had this functionality. Now, all the chart types with data label have this feature. Add a Leader Line Step 1: Click on the data label.

Step 2: Drag it after you see the four-headed arrow. Step 3: Move the data label. The Leader Line automatically adjusts and follows it. Step 2: Click on Format Leader Lines. The Format Leader Lines task pane appears.

Now you can format the leader lines as you require. Step 5: Make the changes that you want. The leader lines will be formatted as per your choices. Excel — New Functions Advanced Excel Several new functions are added in the math and trigonometry, statistical, engineering, date and time, lookup and reference, logical, and text function categories. Also, Web category is introduced with few Web service functions. Functions by Category Excel functions are categorized by their functionality.

If you know the category of the function that you are looking for, you can click that category. The Function Library group appears. The group contains the function categories. Step 2: Click on More Functions. Some more function categories will be displayed.

Step 3: Click on a function category. All the functions in that category will be displayed. As you scroll on the functions, the syntax of the function and the use of the function will be displayed as shown in the image given below. MATH: Rounds a number up, to the nearest integer or to the nearest multiple of significance. MATH: Rounds a number down, to the nearest integer or to the nearest multiple of significance. P: Returns the skewness of a distribution based on a population: a characterization of the degree of asymmetry of a distribution around its mean.

These add-in or automation functions will be available in the User Defined category in the Insert Function dialog box. REQUEST: Connects with an external data source and runs a query from a worksheet, then returns the result as an array without the need for macro programming.

Web Functions The following web functions are introduced in Excel Further, different analysis features are readily available. This is through the Quick Analysis tool.

Quick Analysis Features Excel provides the following analysis features for instant data analysis. Formatting Formatting allows you to highlight the parts of your data by adding things like data bars and colors. This lets you quickly see high and low values, among other things. Charts Charts are used to depict the data pictorially. There are several types of charts to suit different types of data. Totals Totals can be used to calculate the numbers in columns and rows.

You have functions such as Sum, Average, Count, etc. Tables Tables help you to filter, sort and summarize your data. The Table and PivotTable are a couple of examples. Sparklines Sparklines are like tiny charts that you can show alongside your data in the cells.

They provide a quick way to see the trends. Quick Analysis of Data Follow the steps given below for quickly analyzing the data. Step 1: Select the cells that contain the data you want to analyze. Conditional Formatting Conditional formatting uses the rules to highlight the data. This option is available on the Home tab also, but with quick analysis it is handy and quick to use.

Also, you can have a preview of the data by applying different options, before selecting the one you want. Step 2: Click on Data Bars. Step 3: Click on Color Scale. The cells will be colored to the relative values as per the data they contain. Step 4: Click on the Icon Set. The icons assigned to the cell values will be displayed. Values greater than a value set by Excel will be colored.

You can set your own value in the Dialog Box that appears. Step 7: Click on Clear Formatting. Whatever formatting is applied will be cleared. You will have a preview of all the formatting for your Data. You can choose whatever best suits your data. Recommended Charts for your data will be displayed.

Step 2: Move over the charts recommended. You can see the Previews of the Charts. More Recommended Charts are displayed. The little black arrows on the right and left are to see additional options. Step 2: Click on the Sum icon. This option is used to sum the numbers in the columns.

This option is used to calculate the average of the numbers in the columns. Step 4: Click on Count. This option is used to count the number of values in the column. This option is to compute the percent of the column that represents the total sum of the data values selected.

Step 6: Click on Running Total. This option displays the Running Total of each column. This option is to sum the numbers in the rows. Step 8: Click on the symbol. This displays more options to the right. This option is to calculate the average of the numbers in the rows. Step Click on Count. This option is to count the number of values in the rows.

This option is to compute the percent of the row that represents the total sum of the data values selected. Step Click on Running Total. This option displays the Running Total of each row. Step 2: Hover on the Table icon. A preview of the Table appears. The Table is displayed. You can sort and filter the data using this feature. Step 4: Click on the Pivot Table to create a pivot table. Pivot Table helps you to summarize your data. They provide a quick way to show the trends of your data.

The chart options displayed are based on the data and may vary. Step 2: Click on Line. A line chart for each row is displayed. Excel — Sorting Data by Color Advanced Excel If you have formatted a table column, manually or conditionally, with the cell color or font color, you can also sort by these colors. The Sort dialog box appears. Step 3: Under the Column option, in the Sort by box, select the column that you want to sort. For example, click on Exam 2 as shown in the image given below. To sort by cell color, select Cell Color.

To sort by font color, select Font Color. Step 5: Click on the option Cell Color. Step 6: Under Order, click the arrow next to the button. The colors in that column are displayed. To move the cell color to the top or to the left, select On Top for column sorting and On Left for row sorting. To move the cell color to the bottom or to the right, select On Bottom for column sorting and On Right for row sorting.

Excel — Slicers Advanced Excel Slicers were introduced in Excel to filter the data of pivot table. In Excel , you can create Slicers to filter your table data also.

A Slicer is useful because it clearly indicates what data is shown in your table after you filter your data. Step 1: Click in the Table. Step 3: Click on Insert Slicer. A Insert Slicers dialog box appears. Step 4: Check the boxes for which you want the slicers. Click on Genre. Step 5: Click OK. Slicer tools appear on the ribbon. To choose more than one item, hold down CTRL, and then pick the items you want to show. Excel — Flash Fill Advanced Excel Flash Fill helps you to separate first and last names or part names and numbers, or any other data into separate columns.

Step 1: Consider a data column containing full names. Step 2: Enter the first name in the column next to your data and press Enter.

Flash Fill will show you a list of suggested names. Step 4: Press Enter to accept the list. Step 6: Start typing the next name and press Enter. The column will be filled with the relevant last names. Flash Fill works with any data you need to split into more than one column, or you can simply use it to fill out data based on an example.

Flash Fill typically starts working when it recognizes a pattern in your data. This command helps you to create PivotTables automatically. Step 1: Your data should have column headers. If you have data in the form of a table, the table should have Table Header. Make sure of the Headers. Step 2: There should not be blank rows in the Data.

Make sure No Rows are blank. Step 3: Click on the Table. Step 4: Click on Insert tab. Step 5: Click on Recommended PivotTables. The Recommended PivotTables dialog box appears. Step 6: Click on a PivotTable Layout that is recommended. A preview of that pivot table appears on the right—side. The PivotTable is created automatically for you on a new worksheet. Step 2: Click on the Insert tab. Step 3: Click on the PivotTable button. A Create PivotTable dialog box appears.

Step 5: Select the Choose Connection option. A window appears showing all the Existing Connections. All the available data connections can be used to obtain the data for analysis. The option Connections in this Workbook option in the Show Box is to reuse or share an existing connection. Connect to a new external data source You can create a new external data connection to the SQL Server and import the data into Excel as a table or PivotTable.

Step 1: Click on the Data tab. Step 4: Establish the connection in three steps given below. Enter the database server and specify how you want to log on to the server. Enter the database, table, or query that contains the data you want. Enter the connection file you want to create. Step 1: Select the data table. Step 2: Click the Insert Tab. The Create PivotTable dialog box opens. Step 4: Fill the data and then click OK. The PivotTable appears on a New Worksheet. Step 5: Choose the PivotTable Fields from the field list.

The fields are added to the default areas. The Filters area fields are shown as top-level report filters above the PivotTable. The order in which the Fields are placed in the Rows area, defines the hierarchy of the Row Fields.

Depending on the hierarchy of the fields, rows will be nested inside rows that are higher in position. This is because in the Rows area, the field Salesperson appears first and the field Month appears next, defining the hierarchy.

You have changed the hierarchy, putting Month in the highest position. Now, in the PivotTable, the field - Salesperson will nest under Month fields. In a similar way, you can drag Fields in the Columns area also. Step 1: Select the Sales sheet from the worksheet tabs. Step 2: Click the Insert tab. Step 3: Click on the PivotTable button on the ribbon. You can find all available Excel functions in the Function Library on the Formulas tab:.

Of course, it's next to impossible to memorize all of them, and you actually don't need to. The Function Wizard will help you find the function best suited for a particular task, while the Excel Formula Intellisense will prompt the function's syntax and arguments as soon as you type the function's name preceded by an equal sign in a cell:. Clicking the function's name will turn it into a blue hyperlink, which will open the Help topic for that function.

What follows below is a list of 10 simple yet really helpful functions that are a necessary skill for everyone who wishes to turn from an Excel novice to an Excel professional. The first Excel function you should be familiar with is the one that performs the basic arithmetic operation of addition:.

In the syntax of all Excel functions, an argument enclosed in [square brackets] is optional, other arguments are required. Meaning, your Sum formula should include at least 1 number, reference to a cell or a range of cells. For example:. If necessary, you can perform other calculations within a single formula, for example, add up values in cells B2 through B6, and then divide the sum by To sum with conditions, use the SUMIF function: in the 1st argument, you enter the range of cells to be tested against the criteria A2:A6 , in the 2nd argument - the criteria itself D2 , and in the last argument - the cells to sum B2:B6 :.

In your Excel worksheets, the formulas may look something similar to this:. Its syntax is similar to SUM's:. Sums values in cells B2 through B6, and then divides the result by 5. And what do you call adding up a group of numbers and then dividing the sum by the count of those numbers? Yep, an average! So, instead of dividing sum by count, you can simply put this formula in a cell:. For our sample data set, the formulas will be as simple as:.

If you are curious to know how many cells in a given range contain numeric values numbers or dates , don't waste your time counting them by hand. In both formulas, you use the so-called "whole column reference" B:B that refers to all the cells within column B.