- What is ribbon?
- Specify the order of operations used for evaluating formulas in Excel.
- Explain Macro in MS-Excel.
- Which are the two macro languages in MS-Excel?
- Is it possible to prevent someone from copying the cell from your worksheet?
- What are charts in MS-Excel?
- What does a red triangle at the top right of a cell indicate?
- How can you add a new Excel worksheet?
- What is the use of NameBox in MS-Excel?
- How can you resize the column?
- Explain pivot tables and its uses.
- Explain workbook protection types in Excel.
- Can we create shortcuts to Excel functions?
- How can you apply the same formatting to every sheet in a workbook in MS-Excel?
- What are left, right, fill and distributed alignments?
- To move to the previous worksheet and next sheet, what keys will you press?
- What filter will you use, if you want more than two conditions or if you want to analyze the list using database function?
- What is the quick way to return to a particular area of a worksheet?
- What is the benefit of using formula in Excel sheet?
- How can you disable the automating sorting in pivot tables?
- How cell reference is useful in the calculation?
- What do you mean by cells in an Excel sheet?
- Explain what is a spreadsheet?
- What do you mean by cell address?
- Can you add comments to a cell?
- Can you add new rows and columns to an Excel sheet?
- How do you add a Note to a cell?
- How do you apply a single format to all the sheets present in a workbook?
- What do you understand by Relative Cell Addresses?
- Can you protect cells of a sheet from being copied?
- How do you create Named Ranges?
- How do you create dropdown lists in Excel?
- How would you format a cell? What are the options?
- What is an Excel Table?
- What are the benefits of using Excel Table?
- What are the commonly used file formats in which an Excel file can be saved?
- How to reduce the size of an Excel file?
- What steps can you take to handle slow Excel workbooks?
- How would you save an Excel worksheet as a PDF?
- How to create a hyperlink in Excel?
- When would you consider switching from automatic to manual calculation in Excel?
- What is Flash Fill?
- What are wildcard characters in Excel?
- What is a Print Area and how can you set it in Excel?
- How can you insert Page Numbers in Excel?
- What are the different data formats in Excel?
- How can you wrap text in Excel?
- How can you merge cells in Excel?
- What is ‘Format Painter’ used for?
- How would you clear all the formatting without removing the cell contents?
- What is conditional formatting?
- How would you highlight cells with negative values in it?
- How would you highlight cells with duplicate values in it?
- How would you highlight cells with errors in it?
- How can you make text invisible in Excel?
- What is the difference between a function and a formula in Excel?
- Explain the SUM and SUMIF functions.
- Explain the COUNT function.
- What is a Horizontal Lookup in Microsoft Excel?
- How does the AND function work in Microsoft Excel?
- Can you name some Text Functions?
- What is the difference between absolute and relative cell references?
- What are the different types of errors you can encounter in Excel?
- How can you tackle errors when working with Excel Formulas?
- Which function would you use to get the current date and time in Excel?
- How can you combine the text from multiple cells using a formula?
- What is the syntax of the VLOOKUP function?
- How would you get rid of leading / trailing / double spaces in a text in Excel?
- What are the known limitations of the VLOOKUP function?
- When would you use the SUBTOTAL function?
- What are volatile functions? Can you name a few?
- What is the shortcut for opening the find and replace dialog box?
- What is the shortcut for spell check?
- What is the shortcut to quickly auto-sum rows/columns?
- What is the shortcut to open a new Excel workbook?
- How can you select all the cells in the worksheet?
- How would you insert a new line in the same cell?
- What’s the shortcut to insert a comment in Excel?
- What is a Pivot Table, and when would you use one?
- What are the various sections in a Pivot Table?
- What are slicers?
- What is a Pivot Chart?
- What are the difference between Pivot Charts Vs Regular Charts?
- Can you group dates in Pivot Tables?
- What is a Pivot Cache?
- What is a Column chart?
- What is a Bar chart?
- What is a Line chart?
- What is a Waterfall chart? When would you use it?
- What are Combination charts?
- What is a Bullet chart? When should we use it?
- How to replace one value with another in Excel?
- What kind of data filters are available in Excel?
- How can you sort data in Excel?
- How can you transpose a data set in Excel?
- How can you select all blank cells in Excel?
- What is an advanced Filter in Excel?
- What is a one-variable data table?
- What is a two-variable data table?
- What is Scenario Manager?
- What is Goal Seek?
- What is a Solver?
- What are the benefits of using VBA in Excel?
- How can you record a macro in Excel?
- What are limitations of recording a macro in Excel?
- What is a UDF in Excel VBA?
- What are Events in VBA?
- What are some of the useful loops in VBA?
- What are the different ways to run a macro in Excel?
- What are UserForms?
- What are add-ins?
- What is difference between a dashboard and a report?
- What are some interactive controls you can use in dashboards?
- What are some useful chart types that you can use in a dashboard?
- What are some best practices when creating a dashboard in Excel?
Ribbon refers to the topmost area of the application that contains menu items and toolbars available in MS-Excel. Ribbon can be shown/hidden using CTRL+F1. The ribbon runs on the top of the application and is the replacement for the toolbars and menus. The ribbons have various tabs on the top, and each tab has its own group of commands.
The order of operations in Microsoft Excel is same as in standard mathematics. It's defined by the term "PEMDAS" or "BEDMAS".
- Parentheses or Brackets
Macros are used for iterating over a group of tasks. Users can create macros for their customized repetitive functions and instructions. Macros can be either written or recorded depending on the user.
XLM and VBA (Visual Basic Applications). Earlier versions of Excel used XLM. VBA was introduced in Excel 5 and mostly used now.
Yes, it is possible. To protect your worksheet from getting copied, you need to go into Menu bar >Review > Protect Sheet > Password. By entering a password, you can secure your sheet from getting copied by others.
To enable graphical representation of the data in Excel, charts are provided. A user can use any chart type, including column, bar, line, pie, scatter, etc. by selecting an option from Insert tab's Chart group.
The red triangle indicates that some comment is associated with the cell. Hover the mouse over it, and you can read the full comment.
To add a new Excel worksheet, you should insert worksheet tab at the bottom of the screen.
Name Box is used to return to a particular area of the worksheet by typing the range name or cell address in the name box.
To resize the column, you should change the width of one column and then drag the boundary on the right side of the column heading till the width you want. The other way of doing it is to select the Format from the home tab, and in Format you have to select AUTOFIT COLUMN WIDTH under cell section. On clicking on this, the cell size will get formatted.
A pivot table is a tool that allows for quick summarization of large data. It automatically performs a sort, count, total or average of the data stored in the spreadsheet and displays result in another spreadsheet. It saves a lot of time. Allows to link external data sources to our Excel.
Excel provides three ways to protect a workbook:
- Password protection for opening a workbook
- Protection for adding, deleting, hiding and unhiding sheets
- Protection from changing size or position of windows.
Yes. 'Quick Access Toolbar' above the home button can be customized to display most frequently used shortcuts.
Right Click 'Worksheet tab' > Choose 'Select All Sheets'. Now any formatting done will be applied to the whole workbook. To apply to a particular group of sheets, select only those sheets that need formatting.
Left /Right alignment: Aligns the text to left and right most of the cell.
Fill as the name suggests, fills the cell with same text repetitively.
Distributed, spreads the text across the width of the cell.
To move to the previous worksheet, you will use the keys Ctrl + PgUp, and to move to the next sheet you will use keys Ctrl + PgDown.
You will use Advanced Criteria Filter, to analyze the list or if more than two conditions should be tested.
The quick way to return to a specific area of the worksheet is by using name box. You can type the cell address or range name in name box to return to a specific area of a worksheet.
Calculating the numbers in Excel sheet, not only help you to give the final 'sum up' of the number but, it also calculates automatically the number replaced by another number or digit. Through Excel sheet, the complex calculations become easy like payroll deduction or averaging the student's result.
To disable the automating sorting in pivot tables,
Go to > "More Sort Options"> Right Click "Pivot table" > Select "Sort" menu > Select "More Options" > Deselect the "Sort automatically when the report is created."
In order to avoid writing the data again and again for calculating purpose, cell reference is used. When you write any formula, for specific function, you need to direct Excel the specific location of that data. This location is referred as, cell reference. So, every time a new value added to the cell, the cell will calculate according to the reference cell formula.
The area which falls at the intersection of a column and a row where the information is to be inserted is known as a cell. There are a total of 1,048,576 x 16,384 cells present in a single excel sheet.
Spreadsheets are a collection of cells that help you manage the data. A single workbook may have more than one worksheet. You can see all the sheets at the bottom of the window, along with the names that you have given them.
The cell address of an Excel sheet refers to the address that is obtained by the combination of the Row number and the Column alphabet. Each cell of an MS Excel sheet will have a distinct cell address.
Yes, comments can be added. To add comments to a cell, select the cell, right-click on it and then select the New Comment option. These comments will be visible to all those people who have access to the Excel sheet.
Yes, you can add rows and columns to an Excel sheet. To add new rows and columns select the place where you intend to add them and right-click on it. Then select the Insert option from where you can choose to select an entire row or column.
To add a Note, select the cell and right-click on the same. then select the New Note option and type in any note that you wish to. In case you want to delete the Note, follow the same procedure and select the Delete Note option. Notes are indicated by a red triangle at the top-right corner of the cell.
To apply the same format to all the sheets of a workbook, follow the given steps:
- Right-click on any sheet present in that workbook.
- Then, click on the Select All Sheets option.
- Format any of the sheets and you will see that the format has been applied to all the other sheets as well.
Whenever you copy formulas in Excel, the addresses of the reference cells get modified automatically in order to match the position where the formula is copied. This is done by a system that is called Relative Cell Addresses.
Yes, you can do it by protecting the required cells or the complete sheet. In order to do this, follow the given steps:
- Select the cells that you want to protect.
- Open up the Font window from the Home tab.
- From the Protection pane, select Protection and then check the Hidden box.
- Click on Review tab present in the Ribbon, and then select Protect sheet option.
- Specify a password.
To create named ranges, follow the given steps:
- Select the area to which you intend to give a name.
- From Ribbon, select Formulas.
- Click on Define Name from Defined Names group.
- Give any name of your choice.
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.
A cell can be formatted by using the format cells options. There are 6 format cells options:
An Excel Table is a feature in Excel. This is not the same as tabular data.
When you convert tabular data into an Excel Table, there are a few additional features that get added to it that can be really useful.
When you convert tabular data into Excel Table, the following features are automatically added to the data:
- You can use the filter icon on each column header to sorting and filter the data.
- You can easily apply styles to a table and format it. There are a lot of in-built styles available that you can use with a single click.
- You can use Table Name and Column Names instead of cell references in formulas.
- When you add a new row/column to the tabular data, the Excel Table automatically expands to cover the new row/column as a part of it. And since you can use Table/Column names in formulas, you don’t need to go and update formulas when new rows/columns are added. Excel Table automatically accounts for it.
There are a lot of file formats in which you can save your Excel workbook. Some commonly used ones are:
- .XLSM (when you file has macros)
- .XLB (binary format)
- .XLA (for add-ins)
There are many ways you can reduce the file size of an Excel Workbook:
- Delete unused data.
- Delete unused worksheets.
- Remove images or compress these images.
- Remove unnecessary formatting.
- Remove Pivot Tables you don’t need. This will also help clear the Pivot Cache.
- Save file in the .XLSB format.
You can use the following techniques to handle slow Excel workbooks:
- Avoid Volatile Functions such as INDIRECT, OFFSET, etc.
- Use Helper Columns instead of array formula.
- Use Conditional formatting only when absolutely necessary (as it is volatile too).
- Use Excel Tables and Named Ranges.
- Convert Unused Formulas to Values.
- Keep All Referenced Data in One Sheet.
- Avoid Using Entire Row/Column in References.
- Use Manual Calculation Mode.
To save a worksheet as a PDF document, you can specify the file type as PDF when saving the file.
To do this, click on the File tab and then click on Save As.
In the Save As dialog box, select the location where you want to save the file and use the Save As type drop-down to select PDF. This will save the entire worksheet as a PDF document.
To create a hyperlink, select the cell in which you want the hyperlink and use the keyboard shortcut Control K (hold the control key and press the K key).
This will open the Insert Hyperlink dialog box where you could specify the URL.
You can also get the option to add the hyperlink when you right-click on the cell.
While in most cases automatic calculation mode is the way to go, in case you have a formula heavy file where recalculation takes a lot of time every time you change anything in the sheet, then you can switch to manual calculation.
Once you have switched to manual calculation, you need to refresh every time you want the sheet to recalculate.
Flash Fill is an amazing tool that was added in Excel 2013 and is available in all version after that.
You can use Flash Fill in Excel to make data entry easy. It’s a smart tool that tries to identify patterns based on your data entry and does that for you.
Some simple examples of using Flash Fill could be to get the first name from the full name, get name initials, format phone numbers, etc.
There are three wildcard characters in Excel:
- * (asterisk) – It represents any number of characters. For example, Ex* could mean Excel, Excels, Example, Expert, etc.
- ? (question mark) – It represents one single character. For example, Gl?ss could be Glass or Gloss.
- ~ (tilde) – It is used to identify a wildcard character (~, *, ?) in the text. For example, let’s say you want to find the exact phrase Excel* in a list. If you use Excel* as the search string, it would give you any word that has Excel at the beginning followed by any number of characters (such as Excel, Excels, Excellent). To specifically look for excel*, we need to use ~. So our search string would be excel~*. Here, the presence of ~ ensures that excel reads the following character as is, and not as a wildcard.
Wildcard characters are useful when you want to use it in formulas or while filtering data.
A print area is a range of cells (contiguous or non-contiguous) that you designate to print whenever you print that worksheet. For example, instead of printing the entire worksheet, if I only want to print the first 10 rows, I can set the first 10 rows as the print area.
To set the Print Area in Excel:
- Select the cells for which you want to set the Print Area.
- Click on Page Layout tab.
- Click on Print Area.
- Click on Set Print Area.
You can insert page numbers using the Page Setup dilaog box.
Here are the steps to do this:
- Click the Page Layout tab.
- In the Page Setup category, click on the dialog box launcher icon (which is the small tilted arrow icon at the bottom right of the group).
- In the Page Setup dialog box, click on Header/Footer tab.
- Select the Page Number format from the Footer drop-down.
The following formats are available in Excel:
- Text Format – This can include text as well as alphanumeric strings (such as ABC123). A text string can also include punctuation and symbols.
- Number Format – There are different formats even within numbers. For example you can have decimals, fractions, have thousand separators, etc. No matter what format has been applied, you can use numbers in calculations such as addition, subtraction, division, and multiplication.
- Date Format – The most important thing to know about dates is that these are stored as numbers in the Excel. However, you can format it to be shown as dates. For example, 01-01-2019 would be saved as 43466 in Excel, which is the number for the given date. In Excel, you can show dates in different formats such as long date (01 January 2019), short date (01-01-2019), etc.
- Accounting / Currency Format – When you apply the accounting / currency format to a number, Excel adds the currency symbol along with two decimal places.
- Percentage Format – You can format numbers to be shown as percentage. For example, you can make 0.1 to show up as 10% when you apply the percentage format to it.
Wrapping text in Excel allows you to avoid any text overflowing out of the cell. By applying the the wrap text option, you can make sure all the text fits nicely in one single cell (which may change it’s height though).
To wrap text, select the cell, go to the Home tab and click on the Wrap text option in the Alignment group. Note that this is a toggle button, which means that if you click on it again, it will unwrap the text.
To merge cells, you need to first select the cells that you want to merge, then go to the Home tab, and click on the ‘Merge and Center’ option in the Alignment group. Note that while using ‘Merge and Center’ gets the job done, it is not the most efficient way to do it. The problem of using ‘Merge and Center’ is that the resulting cells wouldn’t sort properly. The right way merge cells is by using the ‘Center Across Selection’ option.
‘Format Painter’ allows you to copy the format from a cell and apply it on another cell (or range of cells).
Sometimes, you may want to remove all the formatting (colors, borders, font styling, etc.) and just have plain simple data. You can do that by clearing all the formatting in Excel.
To do this, you need to use the ‘Clear Formats’ option, which can be found in the Home tab in the editing group. It becomes visible when you click on the ‘Clear’ drop down.
Note there are other options as well – such as clear contents, clear comments, and clear Hyperlinks. In case you want to clear everything – use the ‘Clear All’ option.
Conditional Formatting allows you to format a cell based on the value in it. For example, if you want to highlight all the cells where the value in less than 30 with a red color, you can do that with Conditional Formatting.
You can do this using conditional formatting. Here are the steps:
- Select the cells in which you want to highlight the cells with negative value.
- Go to the Home tab and click on Conditional Formatting option.
- Go to Highlight Cell Rules and click on ‘Less Than’ option.
- In the ‘Less Than’ dialogue box, specify the value as 0 and the formatting.
You can do this easily using conditional formatting. Here are the steps:
- Select the data in which you want to highlight duplicate cells.
- Go to the Home tab and click on Conditional Formatting option.
- Go to Highlight Cell Rules and click on ‘Duplicate Values’ option.
In Excel there can be different types of errors – such as #N/A, #DIV/0!, #VALUE!, #REF!, #NAME, and #NUM.
You can highlight all the cells that contain any of these errors using conditional formatting.
Here are the steps to highlight cells with errors:
- Select the data in which you want to highlight the cells with errors.
- Go to the Home tab and click on Conditional Formatting option.
- Click on ‘New Rule’.
- In the New Formatting Rule dialog box, select ‘Use a formula to determine which cells to format’ option.
- In the formula field, enter =ISERROR(A1), where A1 is the active cell in the selection.
- Click on the Format button and specify the color in which you want to highlight the cells.
- Click OK.
There are multiple ways to do this:
- You can simply make the font white and it will appear as if it’s invisible.
- You can change make the text invisible by changing the custom format. Here are the steps to do this. Select the cell, press Control + 1 (hold the control key and press 1). This will open the Format Cells dialog box. In the Custom option, type ;;; in custom option field. This will make the text invisible (but it will still be there).
A formula is a user-defined expression that calculates a value. A function is pre-defined built-in operation that can take the specified number of arguments. A user can create formulas that can be complex and can have multiple functions in it.
For example, =A1+A2 is a formula and =SUM(A1:A10) is a function.
SUM function takes n number of arguments and performs a summation of each one them. It basically sums up all the numbers in the range of cells. For example, =SUM (E1: G1), shall add up the numbers from the range E1 to G1.
SUMIF function is used to perform summation only if a certain condition is met. Thus SUM and SUMIF functions are almost identical except for the presence of criteria in SUMIF. For example, =SUMIF (E1: G1,”<10), shall add up the numbers from the range E1 to G1 which are lesser than 10.
There are 5 count functions available in excel.
- COUNT function shall return the total count of cells that have numbers in the range of cells mentioned in the parameter. Syntax is as =COUNT (val1, [val2], …)
- COUNTA function returns the total count of non-empty cells in the range of cells mentioned in the parameter. Syntax is as =COUNTA (val1, [val2], …)
- COUNTIF function returns the total count of cells in the range of cells mentioned in the parameter that satisfies the given condition. The syntax is as -=COUNTIF (A5: I5,”<10”), this shall count the number of cells having a value less than 10.
- COUNTBLANK function is the opposite of COUNTA. This function returns the total count of empty cells in the range of cells mentioned in the parameter. The syntax is as =COUNTBLANK (A5: I5).
- COUNTIFS function returns the total count of cells that satisfy certain conditions. These conditions can be logical operators, numbers and so on. The syntax is as =COUNTIFS (F1: F6,”<10”, J2: J10,” black”), where two conditions have been set.
Horizontal Lookup or HLOOKUP looks for a value from the topmost row of the table horizontally and then moves in a downward direction.
Syntax of HLOOKUP is, = HLOOKUP (Val, giventable, row_no, [rnge_look]),
- Val is the value to be searched in the first row of the table.
- giventable is the row/rows that are sorted in ascending order.
- row_no is the row from which the lookup value is to be recovered.
- [rnge_look] is not a mandatory argument where TRUE (default) means inexact match and FALSE means exact match.
AND is an inbuilt function that gives TRUE if all the conditions mentioned in the form of parameters are satisfied. The syntax is =AND (G6<=17, U8<59).
- CONCATENATE( ) – used to join several text strings to one string.
- TEXT( ) – Converting a value into text formatting.
- PROPER ( ) – Arranging the characters in proper way.
- LEFT ( ) – Returns the specified number of characters from the starting character.
In Excel reference refers to a cell reference – such as A1 or range reference – such as A1:A10.
Relative References: These are cell references that change when you copy and paste the formula that has the references. To give you a simple example, if you put =A10 in cell A1, and then you copy cell A1 and paste it in cell A2, the reference would change to A11. This happens as this is a relative cell reference and it changes relative to the cell it’s copied from.
Absolute References: These are the references that remain the same and don’t change copy and paste the formula that has the references. For example, if you put =$A$10 in cell A1 and then copy cell A1 and paste it in cell A2, the reference would still remain $A$10. The $ sign before the column alphabet and the row number makes it absolute.
When working with Excel, you can encounter the following six types of errors:
- #N/A Error: This is called the ‘Value Not Available’ error. You will see this when you use a lookup formula and it can’t find the value (hence Not Available).
- #DIV/0! Error: You’re likely to see this error when a number is divided by 0. This is called the division error.
- #VALUE! Error: The value error occurs when you use an incorrect data type in a formula.
- #REF! Error: This is called the reference error and you will see this when the reference in the formula is no longer valid. This could be the case when the formula refers to a cell reference and that cell reference does not exist (happens when you delete a row/column or worksheet that was referred in the formula).
- #NAME ERROR: This error is likely to a result of a misspelled function.
- #NUM ERROR: Number error can occur if you try and calculate a very large value in Excel. For example, =194^643 will return a number error.
There are various ways you can tackle the errors in Excel:
- You can highlight the errors using conditional formatting. This requires using the ISERROR function in conditional formatting.
- You can use the IFERROR function in Excel to get a specific value in case the formula returns an error.
- You can use ISERROR to get TRUE in case there is an error and FALSE in case there is not.
- You can use IFNA function to tackle the #N/A error.
The following functions can be used:
- TODAY() – This function takes no argument and would return the current date value.
- NOW() – This function takes no argument and would return the current date and time value. Remember that that dates and time are stored as numbers in Excel. So you can perform operations such as addition/subtraction with these dates.
To combine text from different cells, you can use any one of the following three methods:
- TEXTJOIN function – If you’re using Office 365 subscription, you will have the TEXTJOIN function available in your version.
- CONCATENATE function – If you want to combine values in cell A1 and A2, you can use the formula =CONCATENATE(A1,A2)
- Ampersand (&) operator – This works just like the CONCATENATE function. To combine text strings in cell A1 and A2, use the formula =A1&A2
VLOOKUP is definitely one of the most popular Excel functions. And this is also one of the most asked Excel question that I have seen in interviews.
Here is the VLOOKUP syntax:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
- lookup_value – this is the look-up value you are trying to find in the left-most column of a table. It could be a value, a cell reference, or a text string. In the score sheet example, this would be your name.
- table_array – this is the table array in which you are looking for the value. This could be a reference to a range of cells or a named range. In the score sheet example, this would be the entire table that contains score for everyone for every subject
- col_index – this is the column index number from which you want to fetch the matching value. In the score sheet example, if you want the scores for Math (which is the first column in a table that contains the scores), you’d look in column 1. If you want the scores for Physics, you’d look in column 2.
- [range_lookup] – here you specify whether you want an exact match or an approximate match. If omitted, it defaults to TRUE – approximate match.
To get rid of leading, trailing, and double spaces, you need to use the TRIM function.
For example, if you have a text string in cell A1 and you want to remove the spaces, you can use the following formula:
Note that it doesn’t remove single spaces between words.
Excel TRIM function does a good job in removing spaces in Excel, however, it fails when you have non-printing characters (such as line breaks) in your data set. To remove non-printing characters, you can use a combination of TRIM and CLEAN functions.
If you have some text in cell A1 from which you want to remove spaces, use the below formula:
The VLOOKUP function is mighty useful, but it also has a few limitations:
It can not be used when the lookup value is on the right. For VLOOKUP to work, the lookup value should always be in the left-most column. Now this limitation can be overcome by using it with other formulas, it tends to make formulas complex. VLOOKUP would give a wrong result if you add/delete a new column in your data (as the column number value now refers to the wrong column). You can make the column number dynamic, but if you planning to combine two or more functions, why not use INDEX/MATCH in the first place. When used on large data sets, it can make your workbook slow.
When you’re working with tabular data, you can use the SUBTOTAL function to get variety of subtotals – such as AVERAGE, COUNT, MAX, MIN, STDEV.
One of the highlights of SUBTOTAL function is that it allows you to ignore hidden/filtered cells. So if you have a huge data set and you filter it based on a criteria or hide some rows, SUBTOTAL function will automatically update to give you the result from the visible cells only.
Of course, if you don’t want the data of filtered/hidden cells to be ignored, you can do that too.
A volatile function recalculates the formula again and again (whenever there is any change in the worksheet). This can slow down the workbook considerably.
A very simple example of a volatile function is the NOW() function (to get the current date and time in a cell). Whenever you edit any cell in a worksheet, it gets recalculate. This is fine if you have a small data set and less number of formulas, but when you have large spreadsheets, this could significantly slow down the processing.
Here is a list of volatile formulas:
Highly volatile: RAND(), NOW(), TODAY() Almost volatile: OFFSET(), CELL(), INDIRECT(), INFO() BONUS TIP: Conditional formatting is also volatile. You should use it only the cells where it’s needed.
- CONTROL F – This opens the Find and Replace dialog box with the Find tab selected.
- CONTROL H – This opens the Find and Replace dialog box with the Replace tab selected.
F7 – This opens the spell-check dialog box.
If you have numbers in a column/row, you can quickly get the sum by using this Excel keyboard shortcut.
This works only when you have the Excel application open.
You can use CONTROL A A – hold the control key and hit the A key twice.
In case you don’t have any data around the active cell, hitting the A key once would select the entire worksheet. But in case there is data, hitting the A key once select the entire data and hitting it again then selects all the cells in the worksheet.
To insert a new line in the same cell, use the shortcut ALT Enter – hold the ALT key and press enter.
Select the cell in which you want to add the comment, hold the ALT key press the F2 key.
In case you have selected a range of cells, it will insert the comment in the active cell only.
A Pivot Table is a feature in Microsoft Excel that allows you to quickly summarize huge data sets (with a few clicks).
Even if you’re absolutely new to the world of Excel, you can easily use a Pivot Table. It’s as easy as dragging and dropping rows/columns headers to create reports.
To give you an example, if you have the 10,000 rows sales data from four different regions, you can use a Pivot Table to instantly find what are the total sales in each region. If you want to further drill down and see what are the sales of each product in each region, you can easily do that (it’s literally as easy as dragging a data point in a box).
A Pivot table is made up of four different sections:
- Values Area: This is the area where the values are reported.
- Rows Area: The headings to the left of the Values area makes the Rows area.
- Columns Area: The headings at the top of the Values area makes the Columns area.
- Filter Area: It is an optional filter that you can use to further drill down in the data set.
Slicers were introduced in Pivot Table in the 2010 version of Excel.
A Pivot Table Slicer enables you to filter the data when you select one or more than one options in the Slicer box.
When you create a Pivot Table, you get the summary of your data. You can also plot this summary in a chart that is connected to the data.
This chart is called the Pivot Chart.
One big benefit of using a Pivot Chart is that it updates when you change the Pivot Table layout. For example, if you have the total sales by region, and you update the Pivot Table to show sales data for each product in the regions, the Pivot Chart would accordingly update.
While Pivot Charts are amazing and come with the ability to update when the Pivot Table updates, these are not as flexible as the regular charts.
In general, you can do a lot of customization in a regular Excel chart, but not in a Pivot chart. Also, if you customize a Pivot Chart, and then update the Pivot Table, you are likely to lose the customization.
Despite the limitations, Pivot Charts are useful and can help create quick views from a Pivot Table.
If you have date wise records, you can easily group these into the following segments:
- Hours / Minutes / Seconds The option to group data in Pivot Table is in the Analyze tab, which becomes visible when you select a cell in the Pivot Table in the Rows area.
Pivot Cache is something that automatically gets generated when you create a Pivot Table.It is an object that holds a replica of the data source. While you can’t see it, it is a part of the workbook and is connected to the Pivot Table. When you make any changes in the Pivot Table, it does not use the data source, rather it uses the Pivot Cache.The reason a pivot cache gets generated is to optimize the pivot table functioning. Even when you have thousands of rows of data, a pivot table is super fast in summarizing it. You can drag and drop items in the rows/columns/values/filters boxes and it will instantly update the results.Pivot Cache enables this fast functioning of a pivot table.
A column chart is made up of vertical bars that is used to compare values over time or two compare values in different categories.
For example, you can use it to see how the sales have done over the years. Or you can use it to compare which product category has done better sales. Since you can see all the vertical bars at one go, it is easier to visually see and compare.
A bar chart is made up of horizontal bars that is used to compare values in different categories.
For example, you can use it to compare which product category has done better sales. Or what has been the response of a survey.
Line chart are useful when you want to show a trend over the years (or other time periods such as weeks, months, or quarters). You can have multiple lines in a line chart. This would allow you to compare different categories over the same period of time.
A waterfall chart shows different values (positive and negative) that lead to the final result value. For example, if you’re analyzing companies net income, you can have all the cost components shown in the waterfall chart.
This will help you visually see how the value from revenue to net income is obtained when all the costs are deducted.
Combination charts are those where you combine more than one chart type. A popular example of this is showing bar chart with a line chart.
Combination charts let you present and compare two different data-sets that are related to each other. For example, you may be interested in plotting the revenue figures of a company, and at the same time, also be able to show how the profit margin has changed. A combination chart is an apt way of doing this.
Bullet charts were designed by the dashboard expert Stephen Few, and since then it has been widely accepted as one of the best charting representations where you need to show performance against a target.
One of the best things about bullet charts is that it is power-packed with information and takes little space in your report or dashboards.
You can replace one value with another using the FIND & REPLACE feature in Excel.
To do this, select the data set and use the keyboard shortcut – CONTROL H (hold the control key and then press H). This will open the Find & Replace dialog box.
In this dialog box, you can specify the value you want to change and the replacement value.
In Excel, you can filter a data set based on the kind of data.
The following types of data filters are available in Excel:
- Text Filter
- Number Filter
- Date Filter You can apply filter to a data set, by selecting the data, then clicking the Home tab and clicking on the Filter icon.
When you have tabular data and you apply filters, based on the data in the column, Excel shows you the relevant filter. For example, if you have text data, it will show you filters related to text (such as text contains, begins with, ends with, etc.).
There is a sorting feature in Excel that can sort data based on text, numbers, or colors.
Here are some ways to sort data in Excel:
- Select the data and click on one of the two sort icons in the Data tab.
- Select the data and click on the Sort icon. It will open the sort dialog box and you can specify the column to sort and the criteria (ascending/descending).
- Apply data filter, and click on the filter. Along with the filter options, it also shows the data sorting options.
There are two popular ways to transposing data in Excel:
- Using Paste Special dialog box.
- Using the Transpose Function. With Paste Special dialog box, you need to first copy the data that you want to transpose, select the cell where you want to paste it, right-click and go to Paste special, and select the Transpose option.
If you work with a data set that has blank cells in it, you can easily select these cells in Excel. Once selected, you can choose to highlight these, delete these, or add some value to it (such as 0 or NA).
To do this, you need to use the Go To Special dialog box in Excel.
Here are the steps to select all blank cells in Excel:
- Select the entire data set (including blank cells).
- Press F5 (this opens the Go To dialog box).
- Click the ‘Special’ button (this opens the Go To special dialogue box).
- Select Blanks and click OK (this selects all the blank cells in your data set).
Excel Advanced Filter – as the name suggests – is the advanced version of the regular filter. You can use this when you need to use more complex criteria to filter your data set.
Here are some differences between the regular filter and Advanced filter:
- While the regular data filter will filter the existing data set, you can use Excel advanced filter to extract the data set to some other location as well.
- Excel Advanced Filter allows you to use complex criteria. For example, if you have sales data, you can filter data on a criterion where the sales rep is Bob and the region is either North or South.
One variable Data Table in Excel is most suited in situations when you want to see how the final result changes when you change one of the input variables.
For example, if you want to know how much on monthly installment change if you increase/decrease the number of months, you can set up a one-variable data table for it. This can be useful when you want to keep the monthly payment less than $500 and know what all options you have (6 months, 9 months, 12 months, etc.)
The option to set One-Variable data table is in the Data tab, in the What-if Analysis drop down.
Two variable Data Table in Excel is most suited in situations when you want to see how the final result changes when you change two of the input variables.
For example, if you want to know how much on monthly installment change if you increase/decrease the number of months and the interest rate.
You can set up a two-variable data table for it that will show you a the final monthly installment based on different combinations of interest rate and number of months. This can be useful when you want to keep the monthly payment less than $500 and know what all options you have.
The option to set Two-Variable data table is in the Data tab, in the What-if Analysis drop down.
Scenario Manager in Excel can be the tool of choice when you have multiple variables, and you want to see the effect on the final result when these variables change. If you only have one or two variables changing, you can create a one variable or two-variable data table. But if you have 3 or more than 3 variable that can change, then scenario manager is the way to go.
Goal Seek in Excel, as the name suggests, helps you in achieving a value (the goal) by altering a dependent value.
For example, if you’re buying a car and you want to know how many month’s installment you should opt-for so that your monthly payment is not more than $500, you can do this using Goal seek.
Solver in Excel is an add-in that allows you to get an optimum solution when there are many variables and constraints. You can consider it to be an advanced version of Goal Seek.
With Solver, you can specify what the constraints are and the objective that you need to achieve. It does the calculation in the back-end to give you a possible solution.
While Excel has a lot of amazing features and functionalities, it may not have everything you need.
VBA allows you to enhance Excel’s ability by creating codes that can automate tasks in Excel. Below are some of the things you can do this VBA:
- Automate a set of tasks and save time.
- Create your own Excel functions (in case existing functions are not enough).
- Create and share your codes with other people so they can also automate tasks and save time.
- Create custom applications.
Even if you know nothing about VBA, you can still create some macros and automate your work.
You can do this by recording a macro. When you record a macro, Excel closely watches the steps you’re taking and notes it down in a language that it understands – which is VBA. Now, when you stop the recording, save the macro, and run it, Excel simply goes back to the VBA code it generated and follows the exact same steps.
This means that even if you know nothing about VBA, you can automate some tasks just by letting Excel record your steps once and then reuse these later.
You can record a macro by using the Record Macro option which is available in the Developer tab in the ribbon. Once you record the macro, Excel stores it with the name you specify and then you can easily reuse it as many times as you want.
While recording a macro is a great way to quickly generate code, it has the following limitations:
- You can’t execute a code without selecting the object. If you want the macro recorder to go to the next worksheet and highlight all the filled cells in column A, without leaving the current worksheet, then it won’t be able to do this. It’s because if I ask you to do this, even you won’t be able to do that (without leaving the current sheet). And if you can’t do it yourself, how will the macro recorder capture your actions. In such cases, you need to manually go and create/edit the code.
- You can’t create a custom function with a macro recorder. With VBA, you can create custom functions that you can use in the worksheet as regular functions. You can create this by writing the code manually.
- You can’t run codes based on Events. In VBA you can use many events – such as opening a workbook, adding a worksheet, double-clicking on a cell, etc, to run a code associated with that event. You can’t use a macro recorder to do this.
- You can’t create loops with a macro recorder. When you manually enter the code, you can leverage the power of loops in VBA (such as For Next, For Each Next, Do While, Do until). But you can’t do this when you record a macro.
- You can’t analyze conditions: You can check for conditions within the code using macro recorder. If you write a VBA code manually, you can use the IF Then Else statements to analyze a condition and run a code if true (or another code if false).
A UDF is a User Defined Function in VBA. These are also called custom functions.
With VBA, you can create a custom Function (UDF) that can be used in the worksheets just like regular functions.
These are helpful when the existing Excel functions are not enough. In such cases, you can create your own custom UDFs to cater to your specific needs.
In Excel VBA, an event is an action that can trigger execution of the specified macro.
For example, when you open a new workbook, it’s an event. When you insert a new worksheet, it’s an event. When you double-click on a cell, it’s an event.
There are many such events in VBA, and you can create codes for these events. This means that as soon as an event occurs, and if you have specified a code for that event, that code would instantly be executed.
Excel automatically does this as soon as it notices that an event has taken place. So you only need to write the code and place it in the correct event subroutine.
There are the following loops in Excel VBA:
- For Next Loop
- Do While Loop
- Do Until Loop
- For Each Next Loop
You can use the following ways to run a macro in Excel:
- Assign the Macro to a Shape.
- Assign the Macro to a Button.
- Run a Macro from the Ribbon (Developer Tab).
- Run a Macro from the VB Editor.
- Run a Macro using a Keyboard Shortcut.
- Call a Macro from another Macro.
A UserForm is a dialog box that you can design and build in Excel VBA.
Once created, these can be used in many ways in the Excel worksheet area:
- You can use it to get input from the user.
- You can make a form and record the entries in Excel.
- You can add buttons to the UserForm and give options to the user. You can also code these buttons so every time a user clicks on a button, a specific macro is executed.
An add-in is a file that you can load with Excel when it start.
When you create an add-in and install it in Excel, it opens whenever the Excel application opens. You can have many different macros in an add-in and whenever you open Excel, these codes are available for use.
This is useful as you can create an add-in and becomes available to all the workbooks. So if there are some tasks that you need to do often, you can automate these by writing a macro and then saving these as add-ins. Now no matter what Excel you open, you can use the macro.
Another benefit of add-in is that you can share the add-in file with others as well. All they need to do is install it once and they will also have the same macros available to them
A report is meant to provide relevant data. It could be a report that has all the data or can also have a few charts/visualizations. Examples of reports can be sales transaction data or employee survey data.
A dashboard is meant to answer questions using the data. It could be to show which regions are performing better in sales or which areas are lagging in terms of employee feedback. These dashboards could be static or interactive (where the user can make selections and change views and the data would dynamically update).
Excel has a number of interactive tools that can be used in a dashboard:
- Drop Down Lists
- Scroll bars
- Radio Buttons Apart from these regular interactive tools, you can also use VBA to add more functionality to your dashboard.
One of the need when creating a dashboard is to show relevant data with visuals. Charts that can tell a good story and show relevant information are more suited for dashboard.
Since a dashboard is usually expected to fit in a single screen, there is limited space for data and visuals. In such cases, combinations charts come handy.
The following charts can be useful when creating a dashboard:
- Combination charts
- Scatter Charts
- Bullet Chart
- Waterfall chart
- Heat Maps
Here are some best practices when creating dashboard in Excel:
- Convert Tabular Data into Excel Tables: Creating dashboards using an Excel Table as the data source is way easier. Unless you have extremely strong reasons against it, always convert back-end data into an Excel Table.
- Numbering your Charts/Section: This helps when you’re presenting the dashboard and referring to different charts/tables. It’s easier to ask people to focus on chart numbered 2, instead of saying the line chart or bar chart on the top-left.
- Restrict Movement in the dashboard area: While an Excel worksheet is huge, it’s better to remove all the rows/columns except the ones that have your dashboard.
- Freeze Important rows/column: In case you want some rows/columns to always be visible, you can freeze these.
- Make Shapes/Charts Stick: A user may end up changing row/column width. You don’t want the shapes and charts to get misaligned and move from their original place. So it’s better to make them stick to their position.
- Provide a User Guide: It’s a good idea to include a separate sheet that has the details on how to use the dashboard. This becomes more useful when you have interactive controls in the dashboard.
- Save Space with Combination Charts: Since there is limited space in a dashboard (as you need to fit it in one screen), using combination charts can save you space.
- Use Symbols & Conditional Formatting: You can make dashboard more visual and easy to read by using symbols and conditional formatting. For example, it’s easier to see the lowest value in a table when it’s highlighted in red, instead of going through all the values one by one.
- Use colors wisely to show contrast: When you want to highlight a data point in a table or a bar in the chart, it better to make it stand out by highlighting it in eye-catching color.
Want to test this skill? Check out Adaface assessments
Excel & Data Interpretation
Excel, Data Interpretation & Numerical Reasoning
Excel Online Test
Business Analyst Excel Test
Financial & Excel Modeling Test
Data Analyst Excel Test
Microsoft Excel Basic Test
(100% free to get started, no credit card required)