Formula in Excel 365
How to see formulas on the spreadsheet
Sometimes it is handy to view (and print) the formulas in all the cells in a spreadsheet without visiting each cell manually.
Excel Formula auditing tools
When working with formulas in Excel, it is often necessary to find where a mistake was made. Excel provides very useful tools:
- the simple way to evaluate just a part of a formula,
- the Formula Evaluator allows you to see how the various parts of a nested formula are evaluated and the order in which the formula is evaluated:
Creating and using named ranges
Named ranges (also known as defined ranges) are a powerful feature in Excel that allows you to assign a symbolic name to a cell, range of cells, or formula. These defined names are a convenient replacement for the address of a cell, data range, or formula in other formulas, charts, diagrams, shapes, etc.
Recalculating the worksheet
By default, Excel automatic recalculation feature is enabled. Thus, all formulas in the workbook sheets recalculate immediately after data dependencies change. Automatic recalculation of all formulas also occurs when opening a workbook.
How to format negative timestamps
Excel supports different formats for positive and negative numbers. You can combine text and formatted values to make the display more presentative. Another numeric format is a timestamp, and you can find a difference between two timestamps and display it in days, hours, and minutes. However, Excel does not handle well negative timestamp differences.
Calculating the number of days between two dates
If you need to count the number of dates between two dates in Excel, you don't need to use
DATEDIF and other Excel date manipulation functions. Date in Excel isn't a separate
type, but a number with proper formatting. The integer part of this number represents days
since the fixed moment for all dates. Subtract one date from another, and you will get the
number of days between two dates. Also, you can add or subtract an integer from the date,
and get the date in the future or the past.
How to create a break-even chart in Excel
A revenue the company generates from selling the products or providing the services should cover the
fixed costs, variable costs, and leave a contribution margin. The point where the total operating
margin (the difference between the price of the product or service and the variable costs per item
or customer) covers the fixed costs is called a break-even point.
Different rounding effects in Excel
When you work with digits, it's crucial how you round them. It doesn't depend on what exactly you
are rounding - decimals, thousands, or millions. You can see very different amounts after summing
the figures, rounded in different ways:
How to view current stock prices and other quotes in Excel
There are countless reasons why you might need to have stock prices in your Excel spreadsheet.
In the past, you had to copy and paste from Yahoo Finance
or another financial service, but at the moment, Microsoft offers a new way to add and update
some stock information such as stock prices, changes of price, etc.
How to calculate age in Excel
To calculate the age of a person, a pet, a project, or even some device, you need to compute a
number of complete years. The issue is that it doesn't seem enough to calculate a year's difference.
Excel proposes a very useful formula that can help you to calculate that very fast.