Being.Shadaan (ExcelWorld)

Conditional Formatting   Introduction Video: Conditional Formatting Let's say you have a worksheet with thousands of rows of data. It would be extremely difficult to see patterns and trends just from examining the raw information. Similar to charts and sparklines,  conditional formatting  provides another way to visualize data and make worksheets easier to understand. Optional: Download our  practice workbook . Understanding conditional formatting Conditional formatting allows you to automatically apply formatting—such as  colors ,  icons , and  data bars —to one or more cells based on the  cell   value . To do this, you'll need to create a  conditional formatting   rule . For example, a conditional formatting rule might be:  If the value is less than $2000, color the cell red.  By applying this rule, you'd be able to quickly see which cells contain values less than $2000. To crea...

Power Pivot (add-in) - an Introduction PowerPivot is an add-in for Microsoft Excel that enables you to import millions of rows of data from multiple data sources into a single Excel workbook, create relationships between heterogeneous data, create calculated columns and measures using formulas, build PivotTables and PivotCharts, and then further analyze the data so that you can make timely business decisions without requiring IT assistance. Power Pivot is a feature of Microsoft Excel. It is available as an add-in in Excel 2010 and 2013, and is included natively in Excel 2016. PowerPivot extends a local instance of Microsoft Analysis Services Tabular that is embedded directly into an Excel Workbook. This allows a user to build a ROLAP model in PowerPivot, and use pivot tables to explore the model once it is built. This allows Excel to act as a Self-Service BI platform, implementing professional expression languages to query the model and calculate advanced measures...

References Cell reference is nothing but referring to the position of a cell which is available in the same sheet or different sheet or even different workbook. In Excel, each row and column has its own name. Each row is identified by its row number and each column is identified by alphabet. In same way, each cell in Excel has its own name. Such as A1, F26 or W345 - consisting of the column letter and row number that intersect at the cell's location. When listing a cell reference, the column letter is always listed first. One advantage to using cell references in spreadsheet formulas is that, normally, if the data located in the referenced cells changes, the formula or chart automatically updates to reflect the change. If a workbook has been set not to automatically update when changes are made to a worksheet, a manual update can be carried out by pressing the  F9  key on the keyboard. Formulae Refer to = A5 Cell A5 =A1:F4 Cells A1 throu...

Odd() Function The Excel ODD function returns the next odd integer after rounding a given number up. The ODD function always rounds numbers up (away from zero) so positive numbers become larger and negative numbers become smaller (i.e. more negative). Syntax The syntax for the ODD function in Microsoft Excel is: =ODD( number ) Parameters or Arguments number A numeric value that will be rounded up to the nearest odd integer. Odd Function Examples The following spreadsheet shows several examples of the Excel Odd function: Formulas: A B 1 =ODD( 22 ) 2 1.22 =ODD( A2 ) 3 =ODD( A2+4 ) 4 0 =ODD( A4 ) 5 -1.3 =ODD( A5 ) 6 -10 =ODD( A6 ) 7 -3 =ODD( A7 ) Results: A B 1 23 2 1.22 3 3 7 4 0 1 5 -1.3 -3 6 -10 -11 7 -3 -3 The above examples show how the odd function always rounds away from zero. I.e. If the supplie...

Int() Function The Excel INT function returns the integer part of a decimal number by rounding down to the integer. Note the INT function rounds down, so negative numbers become more negative. For example, while INT(10.8) returns 10, INT(-10.8) returns -11. Syntax The syntax for the INT function in Microsoft Excel is: =INT( expression ) Parameters or Arguments expression A numeric expression whose integer portion is returned. Excel Int Function Examples The following spreadsheets show the Excel Int function applied to different numbers: Formulas: A B 1 =INT(100.9) 2 5.22 =INT( A2 ) 3 5.99 =INT( A3 ) 4 99.5 =INT( A4 ) 5 -6.1 =INT( A5 ) 6 -100.9 =INT( A6 ) Results: A B 1 100 2 5.22 5 3 5.99 5 4 99.5 99 5 -6.1 -7 6 -100.9 -101 The above examples show how the Int function always rounds down. Even with negative numbers, the ...

Trunc() Function The Excel TRUNC function returns a truncated number based on a given number of digits. For example, TRUNC(4.9) will return 4, and TRUNC(-3.5) will return -3. The TRUNC function does no rounding, it simply truncates as specified. Syntax The syntax for the TRUNC function in Microsoft Excel is: =TRUNC( number, [digits] ) Parameters or Arguments number The number to truncate. digits Optional. It is the number of decimal places to display in the resulting truncated number. If this parameter is omitted, the TRUNC function will assume 0. Trunc Function Examples The following spreadsheet shows several examples of the Excel Trunc function: Formulas: A B 1 99.999 =TRUNC( A1, 1 ) 2 99.999 =TRUNC( A2, 2 ) 3 99.999 =TRUNC( A3 ) 4 99.999 =TRUNC( A4, -1 ) 5 -99.999 =TRUNC( A5, 2 ) 6 -99.999 =TRUNC( A6, -1 ) Results: A B 1 99.999 99.9 2 99.999 99.99 ...