Ms excel basics pdf free download

While your mouse is over the selected text, right-click and then select "Format Cells" from the popup menu. When the Format Cells window appears, select the Font tab. Check the Strikethrough checkbox. Click on the OK button. Now when you return to your spreadsheet, you should see a strike through your selected text.

Create a subscript value in a cell This Excel tutorial explains how to create a subscript value in a cell in Excel with screenshots and step-by-step instructions. Question: How do I create a subscript value in a cell in Microsoft Excel ?

Answer: Select the text that you wish to convert to subscript. Check the Subscript checkbox. Now when you return to the spreadsheet, you should see the subscript value.

Create a superscript value in a cell This Excel tutorial explains how to create a superscript value in a cell in Excel with screenshots and step-by-step instructions. Question: How do I create a superscript value in a cell in Microsoft Excel ? Answer: Select the text that you wish to convert to superscript.

Check the Superscript checkbox. Now when you return to your spreadsheet, you should see the selected text as a superscript value. Rotate text in a cell This Excel tutorial explains how to rotate text in a cell in Excel with screenshots and step-by-step instructions.

Question: How do I rotate text in a cell in Microsoft Excel ? Answer: Select the cell s that you wish to rotate the text for. Then set the number of degrees that you wish to rotate the text. This value ranges from 90 degrees to degrees for Orientation. In this example, we've selected 90 Degrees for the Orientation. Now when you return to your spreadsheet, the text should be rotated. Format Painter This Excel tutorial explains how to use the Format Painter in Excel with screenshots and step-by-step instructions.

The Format Painter allows you to copy formatting from a cell or group of cells to another section of the Excel spreadsheet. It is a quick and easy way to apply your existing formatting without having to manually set up the the number formatting, alignment, font, border, and patterns for the cells.

Let's look at an example of how to use the Format Painter in Excel First, select the cell that has the formatting that you wish to copy. Then select the Home tab in the toolbar at the top of the screen and click on the Format Painter button in the Clipboard group.

Cell should become highlighted with a green dashed border around it and you will see the following message appear in the status bar at the bottom of the Excel window: "Use the mouse to apply the copied format to another object". Now, select the cells that you wish to apply the copied format to. When you let go of the left mouse button, you will see that the formatting from old cell has been copied to the selected range.

The formatting that has been copied will include number formatting, alignment, font, border, and patterns. Display comment indicator next to cells with comments This Excel tutorial explains how to display the comment indicator next to cells with comments in Excel with screenshots and step-by-step instructions.

Question: When a comment has been inserted in a cell, the little red triangle is not being displayed to indicate a comment in a cell. What can I do to remedy this in Microsoft Excel ? Answer: As you can see, the little red triangle is not being displayed in cell A3 even though there is a comment in that cell. To turn on the comment indicator, select the File tab in the toolbar at the top of the screen and then click on Options at the bottom of the menu.

When the Excel Options window appears, click on the Advanced option on the left. Then scroll down to the Display section in the right side of the window and select the option called "Indicators only, and comments on hover". Now when you return to the Excel spreadsheet, you should see the comment indicator. The comment indicator is the red triangle positioned in the top right corner of the cells that contain comments. If you hover over the cell, the comment will appear. Hide formulas from appearing in the edit bar This Excel tutorial explains how to hide formulas from appearing in the edit bar in Excel with screenshots and step-by-step instructions.

Question: In Microsoft Excel , I have formulas in a worksheet that I don't want to be visible when that cell is selected. Is there a way I can click on the cell and not see the formula up top in the edit bar? Answer: As you can see, currently the formulas are visible. When you select a cell, you can see the formula in the formula bar. To hide the formulas, first you'll need to un-protect all of the cells on your sheet. Download MS Excel Notes. Learn everything about excel formulas function and excel shortcuts and download it in PDF format.

Instructions to download and unzip the file needed to perform this practice exam. This Practical Test consists of four questions that are given equal marks. Learn Excel in just five hours with our Free Excel tutorial. Excel Details: Creating Basic Excel Formulas Formulas are equations that perform calculations on values in your worksheet.

Depending on how you build a formula in Excel will determine if the answer to your formula automatically updates, as changes are made. This is known as Absolute ell Referencing [. Learn how to use VBA to work with ranges, sheets, and workbooks. Learn More.

Using the Autofill handle, drag the formula down to F Figure 9 Data Validation Data validation is an Excel feature that you can use to define restrictions on what data can or should be entered in a cell. You can configure data validation to prevent users from entering data that is not valid. Your contribution will go a long way in helping us. Excel Details: 5. Go back to the original formula. Click between the E and the 2. Press the F4 button. This tells Excel that as the formula is ….

Excel Details: Excel will automatically select the data for the pivot table. Excel will also automatically select New Worksheet as the destination for the pivot table. Click Ok. A new worksheet will be added for the pivot table. 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-. Printing Understand Excel's vast array of printing options. Author spotlight.

Mike Smart. Readers say. Our books provide a route to become a true Excel guru. 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. INV probability,alpha,beta. Returns the natural logarithm of the gamma function, G x.