Blog

Excel is a spreadsheet or electronic worksheet developed by Microsoft for MACOS, Windows, iOS and Android. It is one part of the Microsoft Office suite. It was first released for Macintosh systems in 1985, followed by the first Windows version in 1987. Microsoft Excel allows users to organize, store, and modify the data by applying various formulas. It gives a clear view of reports by dividing into columns and rows. Excel is open to integrate with any external databases to conduct analysis and to generate reports and so on.

Freeze Panes are used to lock any row or column. The locked row or column will be visible on the screen even after we scroll the sheet vertically or horizontally.

You can hide or show (minimize or maximize) the ribbon by pressing CNTRL+F1.

The Order of Operations is: BEDMAS

P/B     Parenthesis/ Brackets

E     Exponents

D     Division

M    Multiplication

A     Addition

S     Subtraction

Refer to the table below for the number of rows, columns and cells for Microsoft Excel 2003 & later version: –

The total number of characters a cell can contain is 32,767.

Press and hold down the Ctrl key and press the down arrow key.

Press and hold down the Ctrl key and press the right arrow key.

VLOOKUP is an MS Excel function to look up and retrieve data from a specific column in table. VLOOKUP supports approximate and exact matching, and wildcards (* ?) for partial matches.

Syntax:

VLOOKUP (lookup_value, table_array, col_index_num,

In Microsoft Excel, a formula can include a range of cells for adding values together, finding an average, and other calculations. For example, if you wanted to include cells D2 through D13 in a formula, you would denote that using the text “D2:D13” in the parentheses for the formula. 

There are three report formats available in Excel

• Compact
• Report
• Tabular

Formula is a statement which is written by the user (user-created) to be calculated. A formula can contain values, cell references, defined names, and functions.

Functions are in-built codes provided in MS-Excel, such as IF, COUNT, SUM, AVERAGE and so on. And, if additional functions are required, we can create through VBA. This will carry out an operation of some kind and provide an output value. Functions are used inside formulas.

The “What If” condition is used to modify the data or make changes to data using excel formulas. Excel provides three types of What If Analysis tools: Scenarios, Goal Seek, Data Tables

Syntax: SUBSTITUTE (text, oldText, newText, [instanceNumber]) 

The fundamental order of operation used in Excel is the well-known PEMDAS. It stands for: parentheses, exponents, multiplication, division, addition, subtraction. It is the universal order of operations used by Excel. If anomaly whatsoever is reported check the alignment of the above order to spot if any parameter has been left missing.

A volatile function recalculates the formula again and again (whenever any modification occurs in a worksheet).

Highly volatile: NOW (), RAND (), TODAY ()

Almost volatile; CELL (), INDIRECT (), OFFSET (), INFO (). 

We stop format losing in a pivot table by merely modifying the options in the pivot table. Under the pivot table, the options that are including are: to disable the option AutoFormat and Enable Preserve Formatting.

Sum: SUM (number1, [number2], …)

Average: AVERAGE (number1, [number2], …)

Max & Min: =MAX (A2:A6) & =MIN (A2:A6)

Count & Counta:  COUNT (value1, [value2], …)   & COUNTA (value1, [value2], …)

If: IF (logical_test, [value_if_true], [value_if_false])

Trim: TRIM (text)

Len: LEN (text)

And & Or: AND returns TRUE if all conditions are met, FALSE otherwise. –OR returns TRUE if any condition is met, FALSE otherwise.

Concatenate: CONCATENATE (text1, [text2], …)

Today & Now: =TODAY () to insert the today’s date in a cell. & =NOW () to insert the current date and time in a cell.

• DATE
• DAYS
• FIND, FINDB
• LOOKUP
• VLOOKUP
• MATCH
• TEXT
• CHOOSE
• SUM
• IF
• INDEX
• RANDOMIZE

This function returns information about a cell in a worksheet. The type of information to be returned is specified as info_type. CELL can get things like address and filename, as well as detailed info about the formatting used in the cell. See below for a full list of information available.

Syntax: =CELL (info_type, [reference])

To stop the format loss in the pivot table can be quickly done by modifying the options present in the Pivot table. You can go to the options that are present in the Pivot Table and then disable the option “AutoFormat” and “Enable Preserve Formatting”.

If you want to protect your worksheet from being copied, go into Menu bar > Review > Protect sheet > Password.

By entering password, you can prevent your worksheet from getting copied.

If the multiple sources are different worksheets from the same workbook, then you can use these multiple sources of data to make Pivot table.

when there is a need to provide a dynamic range in Data Source of Pivot tables, the users need to create a named range using the offset function available and then base the pivot table using a named range that was built in the first step. 

Indeed, cell reference is particularly useful in that it saves some time. Elementarily, cell reference is used in order to avoid writing the data again and again for the purpose of calculation. The location to which a particular cell is directed is known as the cell reference.

Excel allows you to automate the tasks you do regularly by recording them into macros. So, a macro is an action or a set of them that you can perform n number of times. 

 By using Format cell option. There are six options: –

• Number
• Border
• Fill
• Alignment
• Font
• Protection

Advanced Criteria Filter.

 ‘$’ (dollar) symbol is used.

Spreadsheets are similar to a paper ledger sheet. It has rows and columns and has their insertion named cells.

Ctrl+F is the shortcut key to open the find tab and Ctrl+H is the shortcut to open find and replace tab.

By using Transpose function and Paste Special Dialog Box are the two methods to transpose a data set in Excel.

No, it will not. VBA is Object based programming language.

To create dropdown lists, follow the given steps:

Click on Data tab present in the ribbon

Then, from the Data Tools group, click on Data Validation

Navigate to Settings>Allow>List

Select the source list array

Syntax:

SUBSTITUTE (text, oldText, newText, [instanceNumber])

Short key is F7

• Step by step execution – F8
• Breakpoints – F9
• Using Debug.Print
• Immediate Window
• Watch Window

PivotTable is a tool used to summarize large quantities of data quickly and easily. It can help you analyze a data set of tens, hundreds, or even thousands of rows with minimal effort using a number of pre-defined functions such as SUM, COUNT, and AVERAGE.

Conditional formatting is your way of telling excel to format all the cells that meet a criterion in a certain way. For e.g., you can use conditional formatting to change the font color of all cells with negative values or change background color of cells with duplicate values.

There are numerous combinations of the formatting condition and styles, but the process is the same every time. Here’s an overview:

• Select a range.
• Go to the Home section, and click Conditional Formatting.
• Select the rule (and, if you want, customize the condition).
• Select the formatting style.
• Click OK.

The keys Ctrl + PgUp / Pgdown is used to move to the previous /next worksheet in Excel.

Name box is used to finding the required cell or range name. To find any cell address or name, enter the elements in the name box.

Below is the most used excel functions:

• SUMIF
• VLOOKUP
• COUNTIF
• IFERROR
• INDEX/MATCH

It is used to validate the Data in a cell/Range

Whenever we are working with tabular data, we need to get the SUBTOTALS of various functions which include; MAX, MIN, AVERAGE, COUNT, STDEV.

It is used to filter the table, pivot table data visually.

It is used to filter the dates interactively by Year, Month, Quarter and Day.

Excel provides three options for this:

Passwords can be set to open Workbooks

You can protect sheets from being added, deleted, hidden or unhidden

Protecting window sizes or positions from being changed.

Below are the steps to reduce the file size:

• Find the last cell that contains data in the sheet. Delete all rows and columns after this cell
• To delete the rows, press the key Shift+Space then press Ctrl+Shift+Down on your keyboard
• Rows will get selected till the last row. Press Ctrl+- on the keyboard to delete the blank rows
• To delete the column, Press the key Ctrl+Space then press Ctrl+Shift+Right Arrow key on your keyboard
• Columns will get selected till the last row
• Press Ctrl+- on the keyboard to delete the blank columns

To calculate compound interest in Excel, you can use the FV function. FV returns the future value of an investment based on the periodic, constant interest rate and payments.

Syntax: FV(rate, nper, pmt, pv, type)

By using the conditional formatting. Below are the steps that you can follow:

• Select the cells which you want to highlight with the negative values.
• Go to the Home tab and click on the Conditional Formatting option
• Go to the Highlight Cell Rules and click on the Less Than option.
• In the dialog box of Less Than, specify the value as 0.

1. Date and Time Functions
2. Math Functions
3. Logical Functions
4. Lookup and Reference Functions
5. Engineering Functions
6. Financial Functions
7. Information Functions
8. Array Functions
9. Cube Functions
10. Database Functions
11. Statistical Functions
12. Text Functions
13. Web Functions