Understand everything there is to know about AutoFill including text and numeric series, adjusting formulas, custom fill series and linear and exponential series. Understand everything there is to know about Flash Fill including manual and automatic Flash Fill methods, splitting de-limited text, using multiple-examples and concatenating text. In Mike realized that he could only ever teach a few thousand students in his classroom courses but could help millions of students by producing his Excel courses in book form.
His first Excel books covering Excel rapidly became best sellers and his books have topped best seller lists ever since. Would definitely recommend. I am newbie with excel. This is so wonderfully written, easy to understand and very informative.
The text in which it is written is awesome, no faff, no pink and fluffy, no waste of time. 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. Rounds a number down to the nearest integer. Returns a number that is rounded up to the nearest integer or to the nearest multiple of significance. Returns the least common multiple. Returns the remainder from division. Returns a number rounded to the desired multiple.
Rounds a number up to the nearest odd integer. Returns the value of pi. Returns the result of a number raised to a power. Returns the integer portion of a division.
Returns the sum of a power series based on the formula. Returns the sign of a number. Returns a positive square root. Returns the sum of the squares of the arguments. Truncates a number to an integer. Returns an aggregate in a list or database. Returns the number of combinations for a given number of objects.
Returns the cosine of a number. Returns the hyperbolic cosine of a number. Returns the double factorial of a number. Returns the natural logarithm of a number. Returns the logarithm of a number to a specified base. Returns the base logarithm of a number. Returns the multinomial of a set of numbers. Returns the sine of the given angle. Returns the hyperbolic sine of a number.
Returns the sum of the difference of squares of corresponding values in two arrays. Returns the sum of the sum of squares of corresponding values in two arrays. Returns the sum of squares of differences of corresponding values in two arrays. Returns the tangent of a number. Returns the hyperbolic tangent of a number.
Returns the arccosine of a number. Returns the inverse hyperbolic cosine of a number. Returns the arcsine of a number. Returns the inverse hyperbolic sine of a number. Returns the arctangent of a number. Returns the arctangent from x- and y-coordinates. Returns the inverse hyperbolic tangent of a number. Converts radians to degrees. Returns the matrix determinant of an array. Returns the matrix inverse of an array.
Returns the matrix product of two arrays. Converts degrees to radians. Converts an arabic numeral to roman, as text. Returns the average of its arguments. Returns the average arithmetic mean of all the cells in a range that meet a given criteria. Counts how many numbers are in the list of arguments.
Counts how many values are in the list of arguments. Counts the number of blank cells within a range. Counts the number of cells within a range that meet the given criteria. Counts the number of cells within a range that meet multiple criteria.
Returns the maximum value in a list of arguments. Returns the median of the given numbers. Returns the minimum value in a list of arguments. Formats a number and converts it to text. Returns the average of its arguments, including numbers, text, and logical values.
Returns the average arithmetic mean of all cells that meet multiple criteria. Returns the geometric mean. Returns the intercept of the linear regression line. Returns the k-th largest value in a data set. Returns the parameters of a linear trend. Returns the parameters of an exponential trend. Returns the maximum value in a list of arguments, including numbers, text, and logical values.
Returns the smallest value in a list of arguments, including numbers, text, and logical values. MULT number1,number2,…. Returns a vertical array of the most frequently occurring, or repetitive values in an array or range of data. SNGL number1,number2,….
Returns the probability that values in a range are between two limits. AVG number,ref,order. EQ number,ref,order. Returns the skewness of a distribution. Returns the slope of the linear regression line. Returns the k-th smallest value in a data set. Returns a normalized value. Returns values along a linear trend.
INV probability. Returns the average of the absolute deviations of data points from their mean. INV probability,alpha,beta,A,B. Returns the cumulative beta probability density function. Returns the inverse of the one-tailed probability of the chi-squared distribution. Returns the correlation coefficient between two data sets. P array1,array2. S array1,array2. Returns the sample covariance, the average of the products deviations for each data point pair in two data sets.
Returns the sum of squares of deviations. DIST x,lambda,cumulative. TEST array1,array2. Returns the result of an F-test. Returns the Fisher transformation. Returns the inverse of the Fisher transformation. Returns a frequency distribution as a vertical array. DIST x,alpha,beta,cumulative. Power View — Additional Features Advanced Excel Power View in Excel provides an interactive data exploration, visualization, and presentation experience for all skill levels as you have seen in the previous section.
You can pull your data together in Tables, Matrices, Maps, and a variety of Charts in an Interactive View that brings your Data to life. New features have been added to Power View in Excel Power View sheets can connect to different data models in one workbook. You can copy and paste a chart or other visualization from one sheet to another, but only if both sheets are based on the same Data Model. If you base your Power View sheet on the internal Data Model, you can make some changes to the Data Model while you are in the Power View sheet itself.
Step 1: Select the worksheet Salesperson. Step 2: Now select the Worksheet Sales. You have a Range of Data of Sales. Step 3: Convert the data in the worksheet Salesperson to table and name it Salesperson. Step 4: Convert the data on the Sales Worksheet to table and name it Sales. Now, you have two tables in two Worksheets in the Workbook.
Step 5: Click on the Sales Worksheet. Step 7: Click on Power View. In the Power View Fields list, you can find both the tables that are available in the Workbook. Suppose, instead you want to display the names of the salespersons.
You do not have a Data Model in the Workbook and hence no relationship exists between the two tables. Excel does not display any Data and displays messages directing you what to do. You have successfully created the internal Data Model without leaving the Power View sheet.
You can only edit Power View sheets in Excel on a client computer. You will see them when you open the workbook. Maps We have already discussed Maps in the previous chapter. The calculated field represents the current value for the item in that row of the table or matrix, for example, aggregate of sales, profit for a given period, etc. The current value is evaluated against this value. This could be a fixed number, some goal all the rows should achieve, or a calculated field, which might have a different goal for each row.
For example, budget calculated field , average number of sick-leave days absolute value. In Power View in Excel, you can edit the KPI, choosing which indicators to use and what values to trigger each indicator. Hierarchies If your data model has a hierarchy, you can use it in Power View. You can also create a new hierarchy from scratch in Power View.
Step 1: Click on the Matrix Visualization. The hierarchy is decided by the order of the fields in the ROWS box. You can put fields in any order in a hierarchy in Power View. You can drill down for details and drill up for summary. Say, we have four Levels in the hierarchy. You find an arrow on right side of the Level 1 Data item indicating Drill down. Step 5: Click on the Drill down arrow. Alternatively, you can double-click on the Data item to Drill down. That particular Data item Drills down by one Level.
You have one arrow on the left indicating Drill up and one arrow on the right indicating Drill down. You can double-click one value in a level to expand to show the Values under that one in the Hierarchy. You click the up arrow to drill back up. Stacked Bar Chart with only Level 1 Data is displayed.
Step 8: Double-click on a Bar. The Data in the next Level of that particular bar is displayed. You can Drill down one Level at a time by double-clicking on any bar.
Excel — Format Reports Advanced Excel In Excel , Power View has 39 additional themes with more varied chart palettes as well as fonts and background colors. You can also change the text size for all of your Report Elements.
Step 1: Click on the Power View tab on the ribbon. The File Browser opens. Step 4: Browse to the Image File you want to use as Background and click open. The image appears as background in the Power View. Step 6: Click on Stretch in the Drop down menu as shown in the image given below.
Step 7: Click on Transparency in the Background Image group. The higher the percentage, the more transparent less visible the image. Instead of images, you can also set different backgrounds to Power View. Step 9: Click on Power View tab on the ribbon. Now, Power View is with White Background. Step Click on Background the in Themes Group. Step Click on Dark1 Center Gradient. The background changes to Dark1 Center Gradient. As the background is darker, the text turns into white color.
Step Click on the Power View tab on the ribbon. All the available fonts will be displayed in the Drop down list. The font of the text changes to Tahoma. Step Click on Text Size in the Themes group. The changed font and font size are retained in the Table Visualization. When you change the font in one Visualization, the same font is applied to all visualizations except for the font in a Map Visualization.
You cannot have different fonts for different Visualizations. However, you can change the font size for individual visualizations. Step Click on a Cell in the Column containing Numbers. Step Click on Percentage in the Drop down menu. You can format numbers in Card and Matrix Visualizations also.
Hyperlinks You can add a Hyperlink to a text box in Power View. It can link to any URL or email address. This is how you could get the sport images in Tiles in Tiles Visualization in the previous section.
Printing You can print Power View sheets in Excel What you print is what you see on the sheet when you send it to the printer. If the sheet or view contains a region with a scroll bar, the printed page contains the part of the region that is visible on the screen. If a sheet or view contains a region with tiles, then whichever tile is selected is the one that prints. Support for right-to-left languages Power View in Excel supports right-to-left languages.
Power View takes the setting for default direction from Excel. You can change those settings. Step 1: Click on File. Step 2: Click on Options. Step 3: By default, the direction has two options Right-to-left and Left-to-right. Step 6: Change the default direction to Right-to-left.
You can see that the columns are now starting from the right side of the screen as shown in the image given below. Microsoft Office supports right-to-left functionality and features for languages that work in a right-to-left or a combined right-to-left, left-to-right environment for entering, editing, and displaying text. In this context, "right-to-left languages" refers to any writing system that is written from right to left and includes languages that require contextual shaping, such as Arabic, and languages that do not.
You can change your display to read right-to-left or change individual files so their contents read from right to left. If your computer does not have a right-to-left language version of Office installed, you will need to install the appropriate language pack. You must also be running a Microsoft Windows operating system that has right-to-left support — for example, the Arabic version of Windows Vista Service Pack 2 — and enable the keyboard language for the right-to-left language that you want to use.
Excel — Handling Integers Advanced Excel In Power View, to convert a table to a chart, at least one data column needs to be aggregated. In Excel , Power View aggregates both decimal numbers and integers by default.
A Data Model designer can still specify other default behavior, but that is the default. They are aggregates, meaning they will be summed or averaged. Step 1: Click on Stacked Bar Chart. Power View has taken the Year as aggregate, as that is the only numeric field in the selected fields. Step 2: Click on the drop-down arrow next to a text non-numeric field in the Power View Fields. Power View has taken it as Count of the Values of the Field. Power Query Power Query is a data discovery and query tool in Excel Power BI Desktop Power BI is a collection of online services and features that enables you to find and visualize data, share discoveries, and collaborate in intuitive new ways.
Power BI extends to all your mobile devices, too. Power BI introduces the Power BI Desktop, a dedicated report-authoring tool that enables you to transform data, create powerful reports and visualizations, and easily publish to the Power BI service. Power BI Desktop lets you create a collection of queries, data connections, and reports that can easily be shared with others. Power BI Desktop integrates proven Microsoft technologies — the powerful Query engine, data modeling, and visualizations — and works seamlessly with the online Power BI service.
With the combination of Power BI Desktop where analysts and others can create powerful data connections, models and reports and the Power BI service where Power BI Desktop reports can be shared so the users can view and interact with them , new insights from the world of data are easier to model, build, share, and extend.
Data analysts will find Power BI Desktop a powerful, flexible, and a highly accessible tool to connect with and shape the world of data, build robust models, and create well- structured reports. You can Connect to the Data Source so that you can retrieve the Data you want and Adjust the data to meet your needs. The process of adjusting the connected data is called shaping the data. The original data source is not affected; only this particular view of the data is shaped. You can combine two tables, or queries, into one.
There are two primary ways of combining queries — merging and appending. When you have one or more columns to add to another query, you merge the queries.
When you have additional rows of data to add to an existing query, you append the query. You can Load this data file whenever you want and you can make changes and Reload. You can create different types of interesting reports and visualizations.
There are a few ways to share your work in Power BI Desktop. Excel — Templates Advanced Excel Excel has thousands of online Templates of several categories that help you get started quickly by doing most of the set-up and design work for you. You can just focus on your data. Step 1: Open Excel You will see many templates. You also see a Search Box on the top and some Categories below that. Step 2: Click on the category- Business. Also, all the categories available will be displayed on the right side, with the selected category listed as top most.
The list of the categories gives the number of templates in each Category. The Category Sales moves up to the top most position in the Category List. The templates of the selected category Sales are displayed. Excel creates a Sample Excel Worksheet with the Template you have chosen and with the sample data. Your Excel Worksheet is ready with mere focus on data.
It has commands for all the tasks given above. Click on Options. Step 6: Click on the Go Button. You find that Inquire is not selected. Now, the Inquire Add-In is active. Compare Two Workbooks You can compare two workbooks cell by cell and see the differences, if any.
Step 1: Open two Workbooks. Step 4: Check the file names displayed. Step 5: If the file name displayed is not the one you wanted, click on the down-arrow next to the File name. Only the Workbooks that are open will be displayed. Step 6: Select the File you want. Step 8: If it has to be the other way round, click on Swap Files. The order of the Files is changed.
The results of the comparison appear in a two-pane grid. The Workbook on the left corresponds to the "Compare" file you chose and the Workbook on the right corresponds to the "To" file. The details appear in a pane below the two grids. The changes are highlighted by color, depending on the kind of change. The legend that shows what the colors mean is in the lower-left pane. The Save As Dialog Box opens. You can save the results to an Excel Workbook.
Note that only. If you need the results in another program, you can do it by copying it to the Clipboard. Step Paste in another program. Step 1: Click on the Inquire tab on the ribbon. Step 2: Click on Workbook Analysis in the Report group. The Report will be displayed after the Workbook Analysis is completed.
Selecting a Category gives you more information about that Category. Step 3: Click on the Formulas category.
Sub-Categories of the formulas will be displayed. Step 4: Click on a Sub-Category. For example, click "With numeric values". Step 7: Click on the Load Export File button. The saved Report Excel Workbook opens.
The links show the dependencies between the nodes in the diagram. You can drag the links or nodes to arrange them and align them to view whatever you are looking for. Workbook Relationship You can use the Workbook Relationship diagram to create an interactive, graphical map of Workbook dependencies created by connections links between files.
Step 2: Click on Workbook Relationship in the Diagram group. The Worksheet Relationship Diagram appears, showing the links between the Worksheets in the same Workbook and in other Workbooks. Step 2: Click on Cell Relationship in the Diagram group. The Cell Relationship Diagram options window appears.
Step 3: Select the options. Step 4: Click OK. Step 5: Click on Zoom. You can view the nodes clearly. You can use the Clean Excess Cell Formatting command to remove the excess formatting and greatly reduce the file size. This reduces file size and improves your Excel's speed. But, you may want to make a backup copy of your file before cleaning the excess cell formatting, because there are certain cases where this process may increase your file size, and there is no way to undo the change.
You will get a message about saving the changes made. Excel — Manage Passwords Advanced Excel If you are using Workbook Analysis or Compare Files commands for Workbooks that are password-protected, you can avoid having to type the password each time those files are opened. The Password Manager window opens. Add password descriptions also. Other people can then work with the data in Excel Online or open the embedded data in Excel.
Share an Excel worksheet in an online meeting You can present your Workbook online if you have the Lync installed. Step 1: Close all the Workbooks that you do not want to share and keep only the Workbook you want to share Open. Step 2: Click on the File menu. Step 3: Click on the option-Share.
Step 4: Click on Present Online. Step 5: Under the option- Present Online, click Present. Step 7: To stop sharing, click Stop Sharing at the top of the screen. You can present your Workbook online if you have a Skype Account also. Microsoft is introducing Skype for Business. Excel supports saving in other formats, but whenever you save a workbook in another file format, some of its formatting, data, and features might not be saved.
Stores VBA macro code or Excel 4. Excel Binary. Workbook The default file format for an Excel template for Excel Template. Cannot store VBA macro code or Excel 4. The macro-enabled file format for an Excel template in Template. Excel Excel Microsoft Excel 5. Workbook XML Spreadsheet. Supports the use of VBA projects and Excel 4. The Excel Add-In, a supplemental program that Excel An Excel 4. You can open a workbook in.
Text File Formats If you save a workbook in any text format, all formatting is lost. Saves only the active Space-. Saves only the active sheet. Text Saves a workbook as a tab-delimited text file for use on. Saves a workbook as Unicode text, a character encoding Unicode Text.
Saves a workbook as a comma-delimited text file for CSV comma use on another Windows operating system, and ensures.
Saves a workbook as a comma-delimited text file for CSV use on the Macintosh operating system, and ensures. OpenDocument Spreadsheet. You can save Excel files so they can be opened in spreadsheet applications that use the OpenDocument Spreadsheet format, such OpenDocument.
You can also Spreadsheet open spreadsheets in the. Formatting might be lost when saving and opening. This file format preserves document formatting and enables file sharing. Data in the file cannot be easily changed.
The PDF format is also useful for documents that will be reproduced by using commercial printing methods. XPS Document. Step 1: Select the File menu. Step 2: Click on the option Save As. You get a choice of places to save the Workbook, both on local devices e. Computer and internet e. Step 3: Click on Computer. The Save As Dialog box opens. The file formats will be listed.
The file formats displayed depend on the type of active Worksheet in your Workbook Data Worksheet, Chart Worksheet, or other type of Worksheet. Step 5: Click on the File Format you want. Microsoft Excel Binary file formats for Excel versions 5.
Text tab-. Only from Excel. Space-delimited Microsoft Excel objects, objects from properly. This file Single File Web. Web Page. File Formats not Supported in Excel Excel does not support the following File Formats anymore and you cannot open or save files in these File Formats. For example, save to an XML spreadsheet or text File Format that the other program might support as well. This command was used in earlier versions of Excel to save the current layout of all windows as a workspace.
New from Existing In the earlier versions of Excel, the New from Existing option, which you get when you click File and then click New, let you base a new file on an existing one. This option is no longer available. Instead, you can open an existing Workbook and save it with a different file name.
In the Save As dialog box give a different file name. Alternatively, check if the workbook you want to use is in the Recent Workbooks folder. Excel creates a copy of the file by adding a number to the file name. You can save the Workbook with a different file name as needed. In Excel , Save as Template is no longer available on the Ribbon.
To save a chart as a template — Step 1: Right-click on the Chart. Step 2: Click on the Save as Template option. You can use it to create a Chart or change a Chart Type. Step 1: Select a Data Table. Step 3: Click on Recommended Charts in the Charts group.
The Insert chart window appears. Step 4: Click on the All Charts tab. Step 5: Click on Templates. Step 2: Click on Change Chart Type. Step 3: Click on the All Charts tab. Step 4: Click on Templates. In Excel , Split Box Control is removed. Instead, you can use the Split Command on the ribbon. Step 2: Select the cell where you want to place the Split.
Click on Split in the Window Group. The Split appears. As earlier, you can drag a split to reposition it, and double-click a split to remove it. This template would open automatically when you created a new blank Workbook. When you start Excel , the Start screen appears and Excel does not open a new Workbook automatically.
The blank Workbook, which you click on the start screen is not associated with Book. You can set up Excel to open a new Workbook automatically that uses Book. Step 3: Click on General. Step 4: Uncheck the Show the Start screen when this application starts box under the Start up options. The next time you start Excel, it opens a Workbook that uses Book. In Excel , when you save a Workbook as a template, it will not automatically appear as a personal template on the new page.
Step 1: Click on the File tab. Step 3: Click on Save. In the default personal templates location box, enter the path to the templates folder you created. This new Insert Online Pictures feature lets you find and insert content from the Office. In Excel , Microsoft Clip Organizer is not included. Step 2: Click on the Online Pictures button in the Illustrations group. An Insert Pictures dialog box opens. Step 3: Select the picture from any of the sources. Exit option In the earlier versions of Excel, you can exit Excel and close all the open workbooks at once.
This was causing confusion among the different close and exit commands in Backstage View. Hence, it is removed. Clicking on the File menu and then the Close option or the Close button in the upper-right corner of the application window closes the workbooks one at a time.
If there are many open workbooks and you want to close all at once, it is time consuming because you can close only one workbook at a time. If you want the Exit command available to you, you can add it to Quick Access Toolbar.
Step 4: In Choose commands from: select All Commands. Step 5: Select Exit. Step 6: Click on Add. The Exit command is added to the list on the right side. Step 7: Click on OK. All the open workbooks close at once. Browser View Options Earlier, when saving a workbook to the web, you used to set how users will see your workbook when they view it.
These options used to be in the Save As dialog box when you saved a workbook to SharePoint. In Excel , first you need to set the Browser View options. Step 2: Click on Info. Step 5: Save the workbook to any web location.
Individual Data Series In the earlier versions of Excel, you could change the Chart type of an individual data series to a different Chart type by selecting each series at a time. Excel would change the Chart type of the selected data series only. In Excel , Excel will automatically change the Chart type for all data series in the Chart.
Step 1: Right-click on the 3-D Column chart.
0コメント