With conditional formatting, among other things, we can join indication icons to our numbers. While we can rearrange available icons as we wish, the choice of available icons is fairly limited: In particular, while Green Arrow Up and Red Arrow Down are available, we don’t have the often-requested Green Arrow Down and Red Arrow Up. We can expand our choice of icons with the help of the Wingdings fonts, a series of fonts that render characters as a variety …
Author: administrator@dailyexcel.net
Lookup with unique identifiers
INDEX function The INDEX function returns the value from a range of cells based on the row and column specified in the function arguments. We are simplifying here: the INDEX function can actually return values from one or more arrays, i.e., a broader term for lists of data that includes cell ranges. But the most common use is with cell ranges, as that is the typical way of structuring data in Excel. The syntax of the INDEX function is …
Conditional calculations
IF functions for cell ranges Using conditional statements, i.e., the IF function, we can test conditions and perform actions if conditions are met. This IF-THEN-ELSE conditional processing is useful when we want to add something new to our rows. However, it is not really appropriate for retrieving data from or about whole ranges of cells, sometimes containing thousands of rows and columns. For example, in order to sum the incentive paid out to “green” team members, we had to …
Conditional statements
IF function basics Conditional statements, conditional expressions, conditional processing, IF-THEN, or IF-THEN-ELSE are all names for a programming concept where conditions are tested and actions are performed if conditions are met. If a certain condition is met, something is calculated. If that certain condition is not met, either nothing happens or something else is calculated. In Excel, when conditions are met, something is TRUE. When conditions are not met, something is FALSE. TRUE and FALSE are logical values. You will …
Date and Time in Excel
How are date and time stored and formatted? No matter how date and/or time are formatted and possibly edited, the underlying values saved in cells are still decimal numbers. Given that Excel’s era starts on January 1, 1900, if we enter number 1 in the cell and format it as a date, the date we will see is in fact January 1, 1900.Zero will provide for a nonexistent date of January 0, 1900, and a negative number will result in …
Numbers in Excel
How are numbers stored, formatted and calculated? When dealing with numbers in Excel, we should be aware of a few things: numbers in Excel can be formatted in many ways: plain numbers, currency, percentage, date, etc. no matter how they are formatted and possibly edited, underlying values saved in cells are still decimal numbers those decimal numbers that Excel uses and calculates with go up to 15 decimal places (and even with that, there are caveats) this is not changed even …
Text in Excel
Text-related features Excel may have a reputation for crunching numbers, but text-formatted data such as names, addresses, and descriptions is often encountered. Excel is also often used for processing data from a wide variety of unrelated databases, applications, and web sites. With those, issues such as numbers formatted as text, text formatted as numbers, multiple text fields combined into a single cell, and broken tables are not uncommon. Further complicating things, Excel functions designed to manipulate text-formatted data are many, …
Spreadsheet basics
Starting with spreadsheets Spreadsheets are used to store and manipulate data. That data is stored in cells and organized in tables (i.e., rows and columns). Table cells can contain a value (text or number) or a formula. Formulas calculate new values, usually by referencing existing values. Both values and formulas can be easily modified. These simple concepts can then be used to perform basic tasks, i.e., you can use your spreadsheet as a tape calculator. On the other hand, …