Excel Formula Expert 1

 
 
 

What is Excel Formula ? 

Excel Formula is one of the best feature in Microsoft Excel, which makes Excel a very very rich application. There are so many useful built-in formulas available in Excel, which makes our work easier in Excel. For all the automated work, Excel Macro is not required. There are so many automated things that can be done by using simple formulas in Excel. Formulas are simple text (With a Syntax) which is entered in to the Excel Worksheet Cells. So how computer will recognize whether it is a formula or simple text? Answer is simple.. every formula in Excel starts with Equal Sign (=). 
 
This Article is written for the people who does not know about Excel formula. This article answers following questions about Excel Formulas:
 
 
1. What is Excel Formula?
2. How to Use Excel Formula – a step by step tutorial.
3. How an Excel Formula works in Excel?
4. How to refer a Cell in Excel Formula?
5. What is Cell Address?
 

How to Use Excel Formula – Step by Step 

1. Select the Cell where you want to place your formula. 
2. Type the Equal (=) sign. 
 
Note: After typing = sign if you Click on Any other Cell then the Address of that Cell will be automatically placed there. So be careful while writing a formula in Excel. While writing formula DO NOT select any other cell, unless you want to refer that Cell in to your formula. Therefore you can refer a cell in to your formula in two ways: 
 
i) By typing the Address of that Cell (like A1, B8 etc) 
 
ii) By Selecting that Cell while writing your formula. 
 
3. Type the address of the cell to include in the formula or Select that Cell. 
 
4. Type the Formula as per the Syntax of that Formula 
 
5. Press Enter or Press Tab Key. (Again after writing the Complete formula, do not click somewhere else, instead you need to press Enter or Tab key Only)
6. And now you are done. 
 
 
Let’s take an example of Simple Formula in Excel. 
 
In Cell A1, I have value as 10 and in Cell B1, I have value 5. I want sum of these two Cell values in Cell C1. 
 
1. Click on Cell C1
2. Type Equal to Sign (=)
3. Now Take your mouse at Cell A1. Automatically the Cell Address A1 will be typed in Cell C1.
4. Now Type Plus Sign ( + )
5. Now Click on the Cell B1. (Same as previously this time B1 Cell Address will be typed)
6. Now It will look as same as shown in below: 
 
7. Now press Enter
8. Value of Cell A1 (i.e. 10) and Value of Cell B1 (i.e. 5) will be added and the Result will be shown in Cell C1 (Where formula is written). 
 
 
Note: In the above Example, you can see that the Value of Cell is referred by the Address of that Cell. It means in Excel Formula if you want to Refer the Value of Cell then you can refer it by passing the Address of that Cell.
Address of Cell is formed by Column Name and Row Number. It means if you want a Cell which is in Column J and Row 3 then the Address of the Cell will be J3.

The Power of Excel Formulas

One of the most powerful features in Excel is the ability to calculate numerical information using formulas. Just like a calculator, Excel can add, subtract, multiply, and divide. In this lesson, we’ll show you how to use cell references to create simple formulas.

Mathematical operators

Excel uses standard operators for formulas, such as a plus sign for addition (+), a minus sign for subtraction (-), an asterisk for multiplication (*), a forward slash for division (/), and a caret (^) for exponents.
 
All formulas in Excel must begin with an equals sign (=). This is because the cell contains, or is equal to, the formula and the value it calculates.

Understanding cell references

While you can create simple formulas in Excel manually (for example, =2+2 or =5*5), most of the time you will use cell addresses to create a formula. This is known as making a cell reference. Using cell references will ensure that your formulas are always accurate because you can change the value of referenced cells without having to rewrite the formula.
 

By combining a mathematical operator with cell references, you can create a variety of simple formulas in Excel. Formulas can also include a combination of cell references and numbers, as in the examples below:

To create a formula:

In our example below, we’ll use a simple formula and cell references to calculate a budget.
 

1. Select the cell that will contain the formula. In our example, we’ll select cell B3.

 

2. Type the equals sign (=). Notice how it appears in both the cell and the formula bar.

3. Type the cell address of the cell you want to reference first in the formula: cell B1 in our example. A blue border will appear around the referenced cell.

4. Type the mathematical operator you want to use. In our example, we’ll type the addition sign (+).
 

5. Type the cell address of the cell you want to reference second in the formula: cell B2 in our example. A red border will appear around the referenced cell.

6. Press Enter on your keyboard. The formula will be calculated, and the value will be displayed in the cell.

If the result of a formula is too large to be displayed in a cell, it may appear as pound signs (#######) instead of a value. This means the column is not wide enough to display the cell content. Simply increase the column width to show the cell content.

Modifying values with cell references

The true advantage of cell references is that they allow you to update data in your worksheet without having to rewrite formulas. In the example below, we’ve modified the value of cell B1 from $1,200 to $1,800. The formula in B3 will automatically recalculate and display the new value in cell B3.

Excel will not always tell you if your formula contains an error, so it’s up to you to check all of your formulas. 

To create a formula using the point-and-click method:

Rather than typing cell addresses manually, you can point and click on the cells you want to include in your formula. This method can save a lot of time and effort when creating formulas. In our example below, we’ll create a formula to calculate the cost of ordering several boxes of plastic silverware.
 

1. Select the cell that will contain the formula. In our example, we’ll select cell D3.

2. Type the equals sign (=).

3. Select the cell you want to reference first in the formula: cell B3 in our example. The cell address will appear in the formula, and a dashed blue line will appear around the referenced cell.

4. Type the mathematical operator you want to use. In our example, we’ll type the multiplication sign (*).
 
5. Select the cell you want to reference second in the formula: cell C3 in our example. The cell address will appear in the formula, and a dashed red line will appear around the referenced cell.

6. Press Enter on your keyboard. The formula will be calculated, and the value will be displayed in the cell.

Formulas can also be copied to adjacent cells with the fill handle, which can save a lot of time and effort if you need to perform the same calculation multiple times in a worksheet. 
 
 

To edit a formula:

Sometimes you may want to modify an existing formula. In the example below, we’ve entered an incorrect cell address in our formula, so we’ll need to correct it.
 

1. Select the cell containing the formula you want to edit. In our example, we’ll select cell B3.

2. Click the formula bar to edit the formula. You can also double
-click the cell to view and edit the formula directly within the cell.

3. A border will appear around any referenced cells. In our example, we’ll change the second part of the formula to reference cell B2 instead of cell C2.

4. When you’re done, press Enter on your keyboard or click the checkmark in the formula bar.
 
 
5. The formula will be updated, and the new value will be displayed in the cell.
 
 
If you change your mind, you can press the Esc key on your keyboard to avoid accidentally making changes to your formula.
 
To show all of the formulas in a spreadsheet, you can hold the Ctrl key and press ` (grave accent). The grave accent key is usually located in the top-left corner of the keyboard. You can press Ctrl+` again to switch back to the normal view.

Working With Excel In The Cloud- An Intro To Onedrive

 
 

EXCEL AND ONE DRIVE

 
 
You might have heard the phrase “working in the cloud” and wonder what that means, and how that affects the way you work with Excel. It’s really all about providing easy access to your data via a browser—to anyone, anywhere, at anytime. No need for other people to have Excel installed to view, edit, or collaborate on that data.
 
By using Excel Online, a browser-based extension of Excel, you will be able to do all that. You can even use it to create a new workbook on a computer that doesn’t have Excel installed. 
 
You can view and edit workbooks in a browser in one of two ways: 
 
· By uploading or creating new workbooks on OneDrive, which is a free Web-based file storage and sharing service. 
 
· By saving workbooks to a SharePoint site where Office Online is installed. 
 

What is Excel Online? 

Excel Online is a browser-based application that you can use to view and edit Excel workbooks in your browser. Workbooks and worksheets look the same in Excel Online as they do in Excel, and Excel Online has the familiar look and feel of Excel. Many Excel features are available in Excel Online, but not all features are supported or work exactly the same way. To make changes beyond what you can do in the browser, Excel Online includes a way for you to open the workbook in Excel, and then save it back to the server. 

Excel Online makes it easy for you to: 
 
· Extend Excel on the Web Use Excel features and functionality you are familiar with, in a Web environment. 
 
· Work anywhere A browser is all you need to access your workbooks. 
 
· Work together Your teammates can work with you on projects regardless of which version of Excel they have. 
Using OneDrive to access Excel Online 
 
To use Excel Online on OneDrive, you need a Microsoft account. Use that ID to sign in and then go to OneDrive to access Excel Online. To work on existing Excel workbooks in Excel Online, you can upload those workbooks in OneDrive. 
Create a OneDrive ID 
 
1. Go to http://OneDrive.live.com. 
 
2. Click Sign up. 
Upload an existing workbook 
 
To upload one or more workbooks for use in Excel Online, do the following: 
 
1. Sign into OneDrive. 
 
2. Under Files, click the folder where you want to upload the workbooks. 
 
Folders that display a lock icon are private folders; folders can also be shared folders or public (internet) folders. 
 
NOTE: By clicking Create and then Folder, you can create a new folder where you can upload your workbooks. 
 
3. Click Upload to add the workbooks you want to open in Excel Online. 
Start a new workbook in Excel Online 
 
Instead of uploading a workbook from your computer, you can add a new, blank workbook to a OneDrive folder directly in the browser. 
 
1. Sign in to OneDrive. 
 
2. Click the folder where you want to add a new workbook. 
 
3. Click Create, and then click Excel workbook. 
 
Excel Online opens in edit mode. 
 
NOTE: There’s no need to save your changes. Excel Online saves your workbook automatically while you work on it. If you make changes you don’t want to keep, use the Undo command (or press CTRL+Z). 
View worksheet data in Excel Online 
 
When you open a workbook in view mode in Excel Online, you can view and interact with the worksheet data to help you interpret it. For example, you can sort and filter the data in columns or expand PivotTables to see additional details and data trends. To make changes to the data, you must open the workbook in edit mode. 
 
1. Sign in to OneDrive. 
 
2. Click the folder that contains the workbook you want to view. 
 
3. Click the workbook that you want to explore. 
Edit worksheet data in Excel Online 
 
When you work on an Excel Online workbook in edit mode, you can change the data, enter or edit formulas, and apply basic formatting to draw attention to the data. 
 
1. Sign in to OneDrive. 
 
2. Click the folder that contains the workbook you want to edit. 
 
3. Click the workbook you want to change. 
 
Excel Online opens in edit mode. 
 
To make advanced editing changes to the worksheet that exceed the capabilities of Excel Online, do the following: 
 
1. Click Open in Excel. 
 
2. In Excel, make the editing changes you want. 
 
3. On the File tab, click Save. 
 
The workbook is automatically saved in the OneDrive folder. 
 
Use Excel Online to collaborate on worksheet data with other people 
 
To collaborate with other people on the same workbook at the same time, you can share a workbook with them. This feature is useful when you have a worksheet that requires input, such as a group project or a signup sheet. No more sending a workbook around via e-mail, or waiting for someone else to check it back in on the server. 
 
1. Sign in to OneDrive. 
 
2. Click the folder that contains the workbook you want to share. 
 
NOTE: Other people will be able to access everything in the folder you select because you cannot provide permission to individual workbooks only. To keep specific workbooks private, use one or more folders for private workbooks, and create a separate folder for the workbooks you want to share. 
 
3. Click Share. 
 
4. Invite the people you want to share the file with. 
 
5. Click Share. 
 
6. Back in the folder, click the workbook. 
 
After users click the link you sent them and start working on the worksheet in edit mode, you can see the changes that they make. 
 
NOTE: If changes are made to the same cell by different people (including you), the last change will displayed in the cell. 

Collaboration Becomes Easy

Many of the features in Office are geared toward saving and sharing files online. OneDrive is Microsoft’s online storage space you can use to save, edit, and share your documents and other files. You can access OneDrive from your computer, smartphone, or any of the devices you use.
Once you have a microsoft account, you will be able to sign in to Office. Just click Sign in in the upper right corner of the excel window.
 

Benefits of using OneDrive

Once you’re signed in to your Microsoft account, here are a few of the things you’ll be able to do with OneDrive:
 
· Access your files anywhere: When you save your files to OneDrive, you’ll be able to access them from any computer, tablet, or smartphone that has an Internet connection. You’ll also be able to create new documents from OneDrive.
 
· Back up your files: Saving files to OneDrive gives them an extra layer of protection. Even if something happens to your computer, OneDrive will keep your files safe and accessible.
 
· Share files: It’s easy to share your OneDrive files with friends and coworkers. You can choose whether they can edit or simply read files. This option is great for collaboration because multiple people can edit a document at the same time (this is also known as co-authoring).

Saving and Opening Files

When you’re signed in to your Microsoft account, OneDrive will appear as an option whenever you save or open a file. You still have the option of saving files to your computer. However, saving files to your OneDrive allows you to access them from any other computer, and it also allows you to share files with friends and coworkers.
 
For example, when you click Save As, you can select either OneDrive or This PC as the save location.

Why Excel?

Welcome To The World Of Analysis With Excel…

Microsoft Excel was released in 1985 and has grown to become arguably the most important computer program in workplaces around the world. Whether you are budgeting, organizing client sales lists, or need to plan an office social gathering, Excel is a powerful tool that has become entrenched in business processes worldwide. 

Why Excel?

Among the computer programs which exist, Microsoft Excel is one of the most important because of the key role it plays in many sectors. It is the most used spreadsheet program in many business activities, classwork and even personal data organisation. Since 1985, it has played a vital role in performing formula based arithmetic and calculations, and other activities that may require mathematical calculations. Many businesses, personal and institutional enterprises have embraced the use of Excel because of its utility and the ability to serve as a visual basic for different applications. 

Brief History of MS Excel 

 Some Areas of Excel Functionalities

The importance of MS Excel can be seen in the different departmental units it is used as follows:

 

Finance and Accounting 

 
If you walk through the finance or accounting department at any major corporate office, you will see computer screens filled with Excel spreadsheets outlining financial results, budgets, forecasts, and plans used to make big business decisions. 
This is the area of business with the biggest reliance and benefit from Excel spreadsheets. Advanced formulas in Excel can turn manual processes that took weeks to complete in the 1980s into something that takes only a few minutes today. 
 
Most users know that Excel can add, subtract, multiply, and divide, but it can do much more with advanced IF functions when coupled with VLOOKUP, INDEX-MATCH-MATCH, and pivot tables.

Marketing and Product Management

While marketing and product professionals look to their finance teams to do the heavy lifting for financial analysis, using spreadsheets to list customer and sales targets can help you manage your sales force and plan future marketing plans based on past results. 
 
Using a pivot table, users can quickly and easily summarize customer and sales data by category with a quick drag-and-drop. All parts of business can benefit from strong Excel knowledge, and marketing functions are not exempt.
 

Human Resources Planning 

 
While database systems like Oracle (ORCL), SAP (SAP), and Quickbooks (INTU) can be used to manage payroll and employee information, exporting that data into Excel allows users to discover trends, summarize expenses and hours by pay period, month, or year, and better understand how your workforce is spread out by function or pay level. 
 
HR professionals can use Excel to take a giant spreadsheet full of employee data and understand exactly where the costs are coming from and how to best plan and control them for the future. 

Education

 
A good use of Excel in schools and educational institutions can automate classroom activities, like games, quizzes and tests. More importantly, it can be used for computation and analysis of test scores, logs, attendance and so on.
 
For example, Excel based App like SimpleSchool Grading and Analysis App can compute, analyse and generate instant report sheets for students by just inputting the students scores.
 

Data Organization

Even non-financial businesses employ Excel spreadsheets. The ability to organize data in Excel tables and set up tools for updating, organizing, and displaying the data makes the program popular for administrative purposes.
 
Comprehensive, easy-to-reference Excel tables allow administrators to check a single statistic in a process, such as service fault issues in a product, against a report of trends for the same service faults. Through these comparisons, issues can be identified and solutions developed.

Programming

 
Excel supports Microsoft’s Visual Basic for Applications programming language for creating macros that increase efficiency and ease of use by automating or simplifying complex functions.
 
People who are proficient at creating Excel macros are much in demand at organizations of all types.

Graphing

 
Excel also produces a variety of charts and graphs for representing income statements and other statistical data visually. You save time by making the charts in the spreadsheet where the data resides.
 
Visual presentation of the data drives home your points more effectively than columns of numbers. Being able to graph within Excel also frees up some capital that otherwise would have to be invested in another graphing program.

You Can Do Anything With a Spreadsheet

 
Using Excel for business has almost no limits for applications. Here are some examples:
When planning a team outing to a baseball game, you can use Excel to track the RSVP list and costs.
Excel creates revenue growth models for new products based on new customer forecasts. 
 
When planning an editorial calendar for a website, you can list out dates and topics in a spreadsheet.
When creating a budget for a small product, you can list expense categories in a spreadsheet, update it monthly and create a chart to show how close the product is to budget across each category.
You can calculate customer discounts based on monthly purchase volume by product.
Users can summarize customer revenue by product to find areas where to build a stronger customer relationships.
 
This is a very short list to give you an idea of the diverse uses for Excel.

The Bottom Line

Excel is not going anywhere, and businesses will continue to use Excel as a primary tool for diverse functions and applications ranging from IT projects to company picnics.
 
A working knowledge of Excel is vital for most office based professionals today, and stronger Excel skills can open the door to promotion and leadership opportunities. Excel is a powerful tool but cannot function alone. It takes a savvy computer user to take advantage of everything Excel has to offer to provide the best results for their company.