Free Online eBook Download - pdftoday.in

Excel 2016 FORMULAS and FUNCTIONS

To  perform data entry chores. You spend most of your excel life. Whether you are copying, moving, formatting, naming or filling cells. Ranges are a big part of excel’s day-to-day- operations. Ranges save time, and they save wear and tear on your typing finger. Ranges are powerful tools that can unlock the hidden depths of excel.

When you are selecting a range, always press and hold down the ctrl key. Excel comes with a couple of tricks to make selecting a range via the keyboard easier or more efficient.

A 3D range is a range selected on multiple worksheets. This is a powerful concept because it means that you can select a range on two or more sheets and then enter data, apply formatting, or give a command, and the operation will affect all the ranges simultaneously. This is useful when you are working with a multi sheet model where some or all the labels are the same on same sheet.   To create a 3D range, first you need to group the word-sheets you want to work with. To select multiple sheets, to select all the sheets in a workbook, right-click any sheet- tab and click the select all sheets command. Another way to select very large ranges is to select view, zoom and click a reduced magnification in the zoom dialog box. You can then use this “big picture” view to select your range.

The go to special dialog box gives you two options for selecting cells adjacent to the active cell. Click the current region option to select a rectangular range that extends to the right from the active cell.

A worksheet is merely a lifeless collection of numbers and text until you define some kind of relationship among the various entries. You do this by creating formulas that perform calculations and produce results. Excel doesn’t object if you use spaces between operators and operand in your formulas. This is actually a good practice to get into because separating the elements of a formula in this way can make them much easier to read.

One of the best ways to improve the readability of worksheets is to display your data in a format that is logical, consistent and straightforward. Formatting currency amounts with leading dollar sign, percentages and large number with comma are a few of the way you can improve your spreadsheet style.

When you work with range of cells, it might appear as though you are working with a single thing. In reality, however, excel treats the range as a number of discrete units.

Despite your best efforts, the odd error might appear in your formulas from time to time. Such errors can be mathematical. Whatever the error and whatever the cause, formula woes need to be worked out because you or someone else in your company is likely depending on your models to produce accurate results When entering function names and defined names, use all lowercase letters. There are mostly syntax errors, so fixing them means double-checking your formula and correcting range name or function name misspellings, or inserting missing quotation marks or colons.

Excel’s analysis Tool-Pak is a large collection of powerful statistical tools. Some of these tools use advanced statistical techniques and were designed with only a limited number of technical users in mind. However, many of them have general applications and can be amazingly useful.

When putting together the data for a worksheet model, it’s common to pull the data from various sources. Unfortunately, this often means that the data arrives at different times, and you end up with an incomplete model. If you are working with a big list, you might want to keep a running total of the number of pieces of data you are still missing.

The date and time functions enable you to convert dates and times to serial numbers and perform operations on those numbers. This capability is useful for such things as accounts receivable aging, project scheduling, time-management applications and much more.

Excel has number of commands that enable you to work efficiently with table data. To take advantage of these commands, you must convert your data from a normal range to a table.

Tables and external database can contain hundreds or even thousands of records Analyzing that much data can be a nightmare without the right kinds of tools. To help you, excel offers a powerful data analysis tool called a Pivot Table. This tool enables you to summarize hundreds of records in a concise tabular format. You can then manipulate the layout of the table to see different views of your data.

To understand Pivot Tables, you need to see how they fit in with excel’s other database analysis features. Database analysis has several levels of complexity. The simplest level involves the basic lookup and retrieval of information. The next level of database analysis involves applying a single question to multiple variables.

The subtotal calculation that excel applies to a field is the same calculation it uses for the data area. You can, however, change this calculation, add extra calculations, and even add a subtotal for the innermost field. Click the field you want to work with, select analyze, active field, field settings, and them use either of these methods.

At times, it’s not enough to simply enter data into a worksheet, build a few formulas, and add a little formatting to make things presentable. The problem with modifying formula variables is that you see only a single result at one time. If you want to make changes to a data table, you can edit the formula as well as the input value. However, the data table results are a different matter. When you run the Data Table command, excel enters an array formula in the interior of the data table.

Many businesses use product margin as a measure of fiscal health. A strong margin usually means that expenses are under control and that the market is satisfied with your price points. Product margin depends on many factors, of course, but you can use Goal Seek to find the optimum margin based on a single variable.

In these complex and uncertain times, forecasting business performance is increasingly important. Today, more than ever before, managers at all levels need to make intelligent predictions of future sales six months, a year, or even three years down the road, managers can anticipate related needs such as employee acquisitions, warehouse space, and raw material requirements. Similarly, a profit forecast enables a company to plan for its future expansion. Excel 2016 includes a new tool called Forecast Sheet.