Looking for:
Add alternative text to a shape, picture, chart, SmartArt graphic, or other object.Excel Tutorial - |
Excel Advanced download free tutorial in pdf.
This option will also save the changes. If your sheet is new and it was never saved so far, then with either of the three options, word would display you a dialogue box to let you select a folder, and enter sheet name as explained in case of saving new sheet.
Below steps explain you how to create a new worksheet if you want to start another new worksheet while you are working on a worksheet, or you closed an already opened worksheet and want to start a new worksheet. Step 2: Now you'll see the Insert dialog with select Worksheet option as selected from the general tab. Click the Ok button.
You can use a short cut to create a blank sheet anytime. Press the Ok Button. Now you should have your copied sheet as shown below. On double click, the sheet name becomes editable. Enter any name say Sheet5 and press Tab or Enter Key. Sheet will get hidden. Unhiding Worksheet Here are the steps to unhide a worksheet. Now you will have your hidden sheet back. Sheet will get deleted if it is empty, otherwise you'll see a confirmation message. Now your worksheet will get deleted.
Step 1: Click the Close Button as shown below. You'll see a confirmation message to save the workbook. Now your worksheet will get closed. Step 1: Click the File Menu as shown below. You can see the Open option in File Menu. There are two more columns - Recent workbooks and Recent places, where you can see the recently opened workbooks and the recent places from where workbooks are opened. Step 2: Clicking the Open Option will open the browse dialog as shown below.
Browse the directory and find the file you need to open. To see context sensitive help for a particular Menu option, hover the mouse over the option for some time. Then you can see the context sensitive Help as shown below. MS Excel cell can have Text, Numeric value or formulas.
An MS Excel cell can have maximum of characters. Inserting Data For inserting data in MS Excel, just activate the cell type text or number and press enter or Navigation keys. Inserting Formula For inserting formula in MS Excel go to the formula bar, enter the formula and then press enter or navigation key. See the screen-shot below to understand it. Let us see those ways. Select with Mouse Drag the mouse over the data you want to select. It will select those cells as shown below.
Select with Special If you want to select specific region, select any cell Pressing F5 will show the below dialogue box. Select current region from the radio buttons. Click on ok to see the current region selected. Delete with Mouse Select the data you want to delete. Right Click on the sheet. Select the delete option, to delete the data. Delete with Delete Key Select the data you want to delete. Press on the Delete Button from the keyboardto delete the data.
Then right click to show the various options. Select the Delete option to delete the selected rows. Step 1: Select the data you want to Move. Right Click and select the cut option. Right click on it and paste the data. You can see the data is moved now. For MS Excel , total rows, total columns. Row and numbers Columns ranges ranges from 1 to ; in from A to XFD; in Navigation with Rows and Columns Let us see how to move to the last row or the last column.
Cell is identified with Combination of column header and row number. For example: A1, A2 49 The simplest method of copy paste is as below.
In this case, MS Excel will copy everything such as values, formulas, Formats, Comments and validation. MS Excel will overwrite the content with paste. View the clipboard content. Select the cell where you need to paste. Click on paste, to paste the content. For example, you want to copy only Values or you want to copy only the formatting of cells.
Select the paste special option as shown below. When clicked, it merges the copied conditional formatting with any conditional formatting in the destination range. Rows become columns, and columns become rows. Any formulas in the copied range are adjusted so that they work properly when transposed.
See the image below. You can see the Find and Replace dialogue as below. We can get rid of the spelling mistakes with the help of spelling check feature. Spell Check Basis Let us see how to access the spell check. You can view the zoom slider at the right bottom of the workbook as shown below. Zoom In You can zoom in the workbook by moving the slider to the right. It will change the only view of the workbook.
See the below screen-shot. You can see many symbols available there like Pi, alpha, beta, etc. Select the symbol you want to add and click insert to use the symbol. You can see many special characters available there like Copyright, Registered etc. Select the special character you want to add and click insert, to use the special character. It helps in proper documentation. To add comment to a cell, select the cell and perform any of the actions mentioned below.
Initially, a comment consists of Computer's user name. You have to modify it with text for the cell comment. For formatting a comment, Right click on cell » Edit comment » Select comment » Right click on it » Format comment. With formatting of comment you can change the color, font, size, etc. Text boxes and cell comments are similar in displaying the text in rectangular box.
But text boxes are always visible, while cell comments become visible after selecting the cell. Adding Text Boxes To add a text box, perform the below actions. Initially, the comment consists of Computer's user name. Let us see some of the important options of formatting a text box.
Also specifying the transparency of text box fill. We can undo changes in following two ways. You can reverse the effects of the past actions that you performed by executing Undo more than once. If you click the arrow on the right side of the Undo button, you see a list of the actions that you can reverse.
Click an item in that list to undo that action and all the subsequent actions you performed. We can redo changes in following two ways. Various Cell Formats Below are the various cell formats. Setting Font from Home You can set the font of the selected text from Home » Font group » select the font. Text-Decoration Various options are available in Home tab of the ribbon as mentioned below.
Choose Format cells » Alignment » Set the degree for rotation. You can change it as per your need from Home tab » Font group » Background color. You can change it as per your need from Home tab » Font group » Foreground color. Below are the various ways of doing it.
By default, Excel aligns numbers to the right and text to the left. Click on the available option in the Alignment group in Home tab to change alignment. Change Alignment from Format Cells Right click on the cell and choose format cell. In format cells dialogue, choose Alignment Tab. Select the available options from the Vertical alignment and Horizontal alignment options.
This option is applicable only if the cell is formatted as wrapped text and uses more than one line. Vertical Alignment: You can set Vertical alignment to top, Middle, bottom, etc.
Rather, you combine a group of cells into a single cell that occupies the same space. You can merge cells by various ways as mentioned below. Various tabs are available as shown below. Page Order: o Down, then Over: It prints the down pages first and then the right pages. All printed pages in MS Excel have the same margins. You can set margins by various ways as explained below. If you want the output to be centered vertically or horizontally, select the appropriate check box in the Center on Page section of the Margins tab as shown in the above screenshot.
If you change the orientation, the onscreen page breaks adjust automatically to accommodate the new paper orientation. By default, new workbooks do not have headers or footers.
You can choose the predefined header and footer or create your custom ones. Accordingly if the document is scaled when printed. This option is enabled, by default. MS Excel gives you precise control over page breaks. MS Excel handles page breaks automatically, but sometimes you may want to force a page break either a vertical or a horizontal one, so that the report prints the way you want. For example, if your worksheet consists of several distinct sections, you may want to print each section on a separate sheet of paper.
Insert vertical Page break: In this case, make sure to place the pointer in row 1. This button displays a dialogue box that lets you select an image to display as a background. Placing this control among the other print-related commands is very misleading. Background images placed on a worksheet are never printed. Then copy the image to all printed pages. MS Excel provides a handy solution to this problem with freezing panes.
Using Freeze Panes Follow the steps mentioned below to freeze panes. Suppose you want to find cell with Amount 0 and Mark them as red. After Clicking ok, the cells with value zero are marked as red. With this conditional Formatting, data Bars will appear in each cell. See the below screenshot with Color Scales, conditional formatting applied.
See the below screenshot with Icon Sets, conditional formatting applied. Without formula, worksheet will be just simple tabular representation of data. A formula consists of special code, which is entered into a cell. It performs some calculations and returns a result, which is displayed in the cell. Formulas use a variety of operators and worksheet functions to work with values and text.
The values and text used in formulas can be located in other cells, which makes changing data easy and gives worksheets their dynamic nature. For example, you can quickly change the data in a worksheet and formulas works.
This formula uses only values, and it always returns the same result as When building formulas manually, you can either type in the cell addresses or you can point to them in the worksheet. Using the Pointing method to supply the cell addresses for formulas is often easier and more powerful method of formula building. See the below screen shot. As soon as you complete a formula entry, Excel calculates the result, which is then displayed inside the cell within the worksheet the contents of the formula, however, continue to be visible on the Formula bar anytime the cell is active.
If you make an error in the formula that prevents Excel from being able to calculate the formula at all, Excel displays an Alert dialog box suggesting how to fix the problem.
When a formula uses cell than constant values, Excel makes the task of copying an every place that requires a similar formula. Relative Cell Addresses MS Excel does it automatically adjusting the cell references in the original formula to suit the position of the copies that you make.
It does this through a system known as relative cell addresses, where by the column references in the cell address in the formula change to suit their new column position and the row references change to suit their new row position. Let us see this with the help of example. Suppose we want the sum of all the rows at last, then we will write a formula for first column i. We want sum of the rows from 3 to 8 in the 9th row. After dragging we can see the formula in the remaining columns as below.
These references enable your formulas to work dynamically with the data contained in those cells or ranges. For example, if your formula refers to cell C2 and you change the value contained in C2, the formula result reflects new value automatically.
When you use a cell or range reference in a formula, you can use three types of references: relative, absolute, and mixed references.
Relative Cell References The row and column references can change when you copy the formula to another cell because the references are actually offsets from the current row and column. By default, Excel creates relative cell references in formulas.
Absolute Cell References The row and column references do not change when you copy the formula because the reference is to an actual cell address.
Mixed Cell References Both the row or column reference is relative and the other is absolute. These functions enable you to greatly enhance the power of your formulas and perform calculations that are difficult if you use only the operators.
Suppose you need to determine the largest value in a range. Suppose you want to find if the cell of month is greater than then we can give Bonus to Sales representative. The information inside the parentheses is the list of arguments. Functions vary in how they use arguments. Depending on what it has to do, a function may use. To see all the functions by category, choose Formulas Tab » Insert Function. Then Insert function Dialog appears from which we can choose the function. Statistical o MAX: Returns the largest value from a list of supplied numbers.
The other rows gets hidden. Using the store data, if you are interested in seeing data where Shoe Size is 36, then you can set filter to do this. Follow the below mentioned steps to do this.
Suppose after size 36 is filtered, you need to have the filter where color is equal to Coffee. After setting filter for Shoe Size, choose Color column and then set filter for color. You may want to sort a table to put names in alphabetical order. Or, maybe you want to sort data by Amount from smallest to largest or largest to smallest.
To Sort the data follow the steps mentioned below. Clicking Ok will sort the data. You can see the same dialog to sort records. A cell is identified by its address, which consists of its column letter and row number.
For example, cell B1 is the cell in the second column and the first row. A group of cells is called a range. Then release the mouse button. If you drag to the end of the screen, the worksheet will scroll. Press F8 again to return the navigation keys to normal movement. Excel selects the cell or range that you specified. For example, you may want to limit data entry in a particular cell to whole numbers between 0 and If the user makes an invalid entry, you can display a custom message as shown below.
Validation Criteria To specify the type of data allowable in a cell or range, follow the steps below, which shows all the three tabs of the Data Validation dialog box. Choose an option from the Allow drop-down list.
The contents of the Data Validation dialog box will change, displaying controls based on your choice. For example, you can specify that the entry must be a whole number greater than or equal to For example, you can specify that the entry must be greater than or equal to 10 and less than or equal to You will create drop-down list with this validation.
You have to give input ranges then those values will appear in the drop-down. You specify a valid date range from choices in the Data drop-down list. For example, you can specify that the entered data must be greater than or equal to January 1, , and less than or equal to December 31, You specify a valid time range from choices in the Data drop-down list. For example, you can specify that the entered data must be later than p.
You specify a valid length by using the Data drop-down list. For example, you can specify that the length of the entered data be 1 a single alphanumeric character. Fill the title and Input message of the Input message tab and the input message will appear when the cell is selected.
Fill the title and error message. Course material to download for free on Excel Advanced category Excel. This course is intended for a strictly personal use, the file is of format pdf level Advanced , the size of this file is 5. You have to come and see our Excel. You will find your happiness without problem!
You can animate the text, pictures, shapes, tables, SmartArt graphics, and other objects in your Microsoft PowerPoint presentation to give them visual effects, including entrances, exits, changes in size or color, and even movement. This booklet is the companion document to the Excel Advanced Excel Tools workshop.
[PDF] Microsoft Excel Level 1 free tutorial for Beginners
Read the Zapier blog for tips on productivity, automation, and growing your business. Hire a Zapier Expert to help you improve processes and automate workflows. Get help with Zapier from our tutorials, FAQs, and troubleshooting articles. Ask questions, share your knowledge, and get inspired by other Zapier users. Video courses designed to help you become a better Zapier user.
Learn about automation anytime, anywhere with our on-demand webinar library. Excel is synonymous with spreadsheets, and for good reason. Decades after its introduction on the original Macintosh, Excel continues to be the most powerful spreadsheet app. It's not just a desktop app anymore, either. That latter version is perhaps the most interesting. Microsoft Excel Online is a free, online version of Excel that includes the functions, chart tools, and more that you'd expect from a spreadsheet.
Similar to Google Sheets, Microsoft Excel Online lets you collaborate with others in real-time, gather answers in an online survey, and crunch numbers with Excel functions. Most importantly, though, Excel Online now lets you connect your spreadsheet with other web apps to gather data and start automated workflows—and since it syncs with traditional Microsoft Excel, you can use the Online version to add automations to any spreadsheet.
If you've used Excel for years, switching to a web app may sound scary. Don't worry. In this guide, we'll walk you through everything you need to get your spreadsheets in the cloud, so you can gather data and collaborate—and still use Excel on your desktop.
Microsoft Excel Online Basics. Gather Data with an Excel Survey. Share and Collaborate in Microsoft Excel Online. Move Older Excel Files to the Cloud. Extend Microsoft Excel with Add-ins and Integrations. Microsoft Excel Online—along with the rest of Microsoft's Office Online apps —are a new take on the traditional Office suite you've used for years.
Typically, you'd buy a copy of Office for a few hundred dollars and install it on your computer. That's how Office has been sold for years, starting with the first version of Excel for Macintosh in Microsoft Office is so ubiquitous, its bundled apps including Word, Excel, and PowerPoint are synonymous with documents, spreadsheets, and presentations—they're the standard business apps across most industries.
Microsoft Excel Online is the free version of Excel that works in your browser. It's a companion app to Excel, designed to let you view and edit your Excel spreadsheets online.
And yet, it's still a full spreadsheet app that supports nearly every Excel function and includes charting and survey tools to help you gather and visualize data.
It's better than Google Sheets for working with existing Excel files or sharing spreadsheets with other Excel users, and at the low cost of free, is the cheapest way to use Excel. You can use all of Excel Online's features for free, as long as you don't have more than 5GB of files in OneDrive, Microsoft's online file storage. Or, if you still want a copy of Office installed on your computer as well, the best option today is subscribing to Office , which gives you the most recent versions of Office for your computer along with file sharing in OneDrive.
You can store all of your files in OneDrive, then edit any of the Office files online with Office Online. And—with the Business versions of Office , for now—you can use Microsoft Excel Online integrations to connect your spreadsheets to your favorite apps. No matter which account you have, you can use the all of Office Online—including Excel Online.
So let's dive in. Need to make presentations? Check out our companion guide to PowerPoint Online to learn how to make presentations, collaborate with your team, and present them from your browser all for free.
There are two easy ways to open Microsoft Excel Online: Go to products. If you open Excel Online from Office. Click a template or the blank spreadsheet option to start making a new spreadsheet. Tip Want a quicker way to open Excel Online? Just bookmark office. Alternately, if you open a spreadsheet from OneDrive or the Recent list, Excel will open it in an online viewer first.
If you have Excel installed on your computer, you can select Edit in Excel to open the spreadsheet in traditional Excel and save the changes back to the web when you're done if you'd like, instead. Microsoft Excel Online looks much like you'd expect. Your data is organized in rows and columns, with tools and features included in an Office ribbon toolbar. On the bottom right, the standard Excel status bar is included, with quick sums and averages of the cells you've selected.
Just click in a cell and type to start adding data to your spreadsheet. Or, paste in data from your computer or other spreadsheets, with the standard options to paste the formulas, values, or formatting. To change the formatting, just click the toolbar buttons at top. You can tweak fonts, colors, text alignment, and number formats, or choose to wrap text and merge cells.
Right-click to insert rows—or click the Insert button at the top for more detailed options. There's also Sort and Find tools to keep your data organized.
You should be able to find the tools you need quickly just by looking through the toolbars. Or, just click the search box at the top and start typing—Excel Online includes a handy tool to help you find the tool you need with search. That's also where you'll find Excel's documentation to help you learn how to use each tool.
There is one spot where Excel Online is full featured: Functions. Excel includes functions , including 11 new ones just added to this version along with 51 functions added in , and 57 added in —and they're all included in the Excel Web App. Everything else is supported; even if the function isn't shown in the auto-complete dropdown, it'll still work once you enter it correctly.
Excel will auto-complete the function name—press the Tab key to accept the suggested function—and then give you tips on what data you should include in the function. Or, you can look through the included functions by clicking the fx button right under the toolbar. You can click cells to add them to your function, just as in other versions of Excel. And, once you've added a function to a cell, just select it and drag down to add the function to all of the other cells you've selected.
If you only need to glance at the data, just select a range of numbers and the bottom right toolbar will show you the sum, average, and count automatically.
Tip : Learn more about each function in Excel with Microsoft's Excel function guide. Once you've added data to your spreadsheet and have crunched your numbers with functions, it's time to bring it to life with charts. Excel Online includes a wide range of charts—and most of the tools you need to customize their appearance. Just select the data you want to visualize, then click the Insert menu and select one of the chart styles you'd like.
Excel will automatically generate the chart—and if you don't like it, just click any of the other chart types in the new Chart tab. Then, you can customize your chart's title, legends, and labels. Just select the menu option for the item you want, and you can both add text and customize where it's displayed.
Unfortunately, there are no theme or color options for charts in Excel Online. If you want something different from the default colors, start your spreadsheet using one of the built-in Excel templates, and your charts will pick up the colors from that template. Alternatively, open your spreadsheet in Excel on your computer to change the design—then save it, and the changes will show up in Excel Online, too.
Or, perhaps you just need an easier way to sort through data. For that, Excel's Tables are the tool you need. Select the data you want to sort through, and click the Format as Table button in the Home toolbar. Or, you can add custom filters to find exactly the data you want.
Excel for Mac and Windows also includes Pivot Tables , 2-dimensional tables that give you more powerful ways to view and organize data. You can't create them in Microsoft Excel Online—but if you have already added them to a spreadsheet, you can view them in Excel Online.
With your spreadsheets online, Excel can work in the background even when your computer is turned off. One of the handiest ways to use that is with Excel Online's built-in Surveys tool. It's one Excel feature you won't find in the other versions of Excel.
That opens an editor pop-over, where you can add a title and description to your form or survey, along with your survey questions. Just add your question and an optional subtitle, along with choices if you selected that question type. Then, choose whether the question is required, and optionally provide a default answer. Then, click the Share Survey button to copy a link to your survey. You can share that link privately in emails, or share it publicly to get a wider range of answers.
Excel will automatically save your survey responses to a new sheet in your spreadsheet, with table formatting so you can easily sort through and filter your survey responses. For a more full-featured form app connected to a spreadsheet, check out our guide to Google Forms.
Or, use any online form apps , and connect it to your Excel spreadsheet with integrations. Need to crunch numbers as a team? Just click the big Share button in the top right corner of Excel Online to get started. You can share the spreadsheet via email, sending a message along with a private link to your colleagues.
Or, you can get a view or edit link to share directly. Anyone with the link can view or edit your spreadsheet, depending on the link you chose. If you have an Office Business account, Excel offers more granular sharing options. You can restrict your file to only people in your company, or make it view-only. You can also add a link expiration date so it's not shared forever, or copy a QR code that your collaborators can scan to work with you.
No comments:
Post a Comment