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. Returns the number of days in the coupon period that contains the settlement date.
Returns the number of days from the settlement date to the next coupon date. Returns the next coupon date after the settlement date. Returns the number of coupons payable between the settlement date and maturity date. Returns the previous coupon date before the settlement date. Returns the cumulative interest paid between two periods. Returns the cumulative principal paid on a loan between two periods. Returns the discount rate for a security.
Returns the annual duration of a security with periodic interest payments. Returns the future value of an initial principal after applying a series of compound interest rates. Returns the interest rate for a fully invested security.
Calculates the interest paid during a specific period of an investment. Returns the yield of a security with an odd first period. Returns the yield of a security with an odd last period. Returns the periodic payment for an annuity. Returns the payment on the principal for an investment for a given period. Returns the amount received at maturity for a fully invested security.
Returns the bond-equivalent yield for a Treasury bill. Returns the yield for a Treasury bill. Returns the depreciation of an asset for a specified or partial period by using a declining balance method. Returns the internal rate of return for a schedule of cash flows that is not necessarily periodic. Returns the net present value for a schedule of cash flows that is not necessarily periodic. Returns the annual yield for a discounted security; for example, a Treasury bill.
Returns the annual yield of a security that pays interest at maturity. Returns the absolute value of a number. Multiplies its arguments. Returns a random number between 0 and 1. Returns a random number between the numbers you specify. Rounds a number to a specified number of digits.
Rounds a number up, away from zero. Returns a subtotal in a list or database. Adds its arguments. Adds the cells specified by a given criteria.
Adds the cells in a range that meet multiple criteria. Returns the sum of the products of corresponding array components. Rounds a number to the nearest integer or to the nearest multiple of significance. Rounds a number the nearest integer or to the nearest multiple of significance.
Rounds a number up to the nearest even integer. Returns e raised to the power of a given number. Returns the factorial of a number. Returns the greatest common divisor. Get healthy. Support for Excel has ended. Learn what end of Excel support means for you and find out how you can upgrade to Microsoft Get the details. Work with stocks and geography data. Turn your data into insights.
List of all Excel functions available. IF function. Create a drop-down list. Advanced excel tutorial. Adeel Zaidi. A short summary of this paper. Download Download PDF. Translate PDF.
Advanced Excel About the Tutorial Advanced Excel is a comprehensive tutorial that provides a good insight into the latest and advanced features available in Microsoft Excel It has plenty of screenshots that explain how to use a particular feature, in a step-by-step manner. Audience This tutorial has been designed for all those readers who depend heavily on MS-Excel to prepare charts, tables, and professional reports that involve complex data.
It will help all those readers who use MS-Excel regularly to analyze data. Prerequisites The readers of this tutorial are expected to have a good prior understanding of the basic features available in Microsoft Excel.
All the content and graphics published in this e-book are the property of Tutorials Point I Pvt. The user of this e-book is prohibited to reuse, retain, copy, distribute or republish any contents or a part of contents of this e-book in any manner without written consent of the publisher.
We strive to update the contents of our website and tutorials as timely and as precisely as possible, however, the contents may contain inaccuracies or errors. Tutorials Point I Pvt. If you discover any errors on our website or in this tutorial, please notify us at contact tutorialspoint.
Excel — Chart Recommendations Excel — Format Charts Excel — Chart Design Excel — Richer Data Labels Excel — Leader Lines Excel — New Functions Instant Data Analysis Excel — Sorting Data by Color Excel — Slicers Excel — Flash Fill Excel — PivotTable Recommendations Excel — Data Model in Excel Excel — Power Pivot Excel — External Data Connection Excel — Pivot Table Tools Excel — Power View Excel — Visualizations Excel — Pie Charts Power View — Additional Features Excel — Power View in Services Excel — Format Reports Excel — Handling Integers Excel — Templates Excel — Inquire Excel — Workbook Analysis Excel — Manage Passwords Excel — File Formats Excel — Discontinued Features Let us create a chart.
Follow the steps given below. Step 1: Select the data for which you want to create a chart. Step 2: Click on the Insert Column Chart icon as shown below.
You can also see the option of More Column Charts. Step 3: If you are sure of which chart you have to use, you can choose a Chart and proceed. If you find that the one you pick is not working well for your data, the new Recommended Charts command on the Insert tab helps you to create a chart quickly that is just right for your data.
Chart Recommendations Let us see the options available under this heading. Step 2: Click on Recommended Charts. Step 3: As you browse through the Recommended Charts, you will see the preview on the right side.
Step 4: If you find the chart you like, click on it. Step 5: Click on the OK button. If you do not see a chart you like, click on All Charts to see all the available chart types. Step 6: The chart will be displayed in your worksheet. Step 7: Give a Title to the chart. Three Buttons appear next to the upper-right corner of the chart. Three Buttons will appear at the upper-right corner of the chart. Step 2: Click on the first button Chart Elements. A list of chart elements will be displayed under the Chart Elements option.
Only the selected chart elements will be displayed on the Chart. Step 2: Click on the second button Chart Styles. Different options of Style will be displayed. Step 4: Scroll down the gallery. The live preview will show you how your chart data will look with the currently selected style. Step 5: Choose the Style option you want.
The Chart will be displayed with the selected Style as shown in the image given below. Step 2: Click on Chart Styles. Different Color Schemes will be displayed. Step 4: Scroll down the options.
The live preview will show you how your chart data will look with the currently selected color scheme. Step 5: Pick the color scheme you want. Your Chart will be displayed with the selected Style and Color scheme as shown in the image given below. Step 1: Click the tab Page Layout.
Step 2: Click on the Colors button. Step 3: Pick the color scheme you like. You can also customize the Colors and have your own color scheme. Filter Data being displayed on the Chart Chart Filters are used to edit the data points and names that are visible on the chart being displayed, dynamically.
Step 1: Click on the Chart. Step 2: Click on the third button Chart Filters as shown in the image. The chart changes dynamically. Step 5: After, you decide on the final Series and Categories, click on Apply. You can see that the chart is displayed with the selected data. It provides advanced formatting options in clean, shiny, new task panes and it is quite handy too. Step 2: Select the chart element e. Step 3: Right-click the chart element.
The new Format pane appears with options that are tailored for the selected chart element. 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. Excel Inside Out. The eBook is in PDF format. Course material on Microsoft Excel free course material and training PDF file 41 pages to download for free as a pdf file. A PDF document is filled with all kinds of content, including text or images, that you might want to import elsewhere.
PDFs are very useful on their own, but sometimes it's desirable to convert them into another type of document file. This is easy to do with the right soft.
0コメント