EXCEL

How user can check whether the Pivot Table is modified or not?

To check whether the Pivot Table is modified or not, you should use the "PivotTableUpdate" in worksheet containing the Pivot Table.

What are the key components of Excel User Interface?

- Quick access toolbar - Excel tabs - Function search - Ribbon - Window Controls - Name Box - Sheet tab - status bar - current workbook window - formula bar - scroll bars

What are the tools in Excel to Remove data duplicates?

There are many methods that you can use to remove duplicates from Excel depending upon, whether you want to remove duplicates from one column or multiple columns, it all depends upon your requirements. • You can remove duplicates using the Remove Duplicates tool from the data tab. • You can use the Pivot table to remove duplicates from a single column. • You can also use Filter to sort out duplicates using selecting and deleting. • You can also utilize Conditional Formatting to highlight and remove duplicates as per your requirements. • You can also use Power Query to remove duplicates. • And there are a lot more ways you can get the results you desire; the limit is only your ability and imagination when it comes to Excel.

What are the tools available to analyze Transactional Data in Excel?

Transactions could be analyzed manually in Excel Ranges, but this is not very efficient, especially when working with large sets of data. Excel PowerQuery is designed to import and process transactional data, Excel Tables are designed to store and Excel Pivot Tables and Pivot Charts are designed to analyze and report on transactional data.

What is VLOOKUP formula syntax and typical usage?

VLOOKUP function finds items in a table or range by row. VLOOKUP is designed to retrieve data from a specific column in table. V LOOKUP supports approximate and exact matching, based on the parameters you supply and wildcards (* ?) for partial matches. The "V" in VLOOKUP stands for "vertical". Lookup values must appear in the first column of the table, with lookup columns to the right. =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup],...) - lookup_value = value of what are we looking for - table-array = excel range containing the return value - col_index_num = column number in the range, containing the return value - [range_lookup] = TRUE for approximate match, FALSE for exact match VLOOKUP function finds items in a table or range by row

Using which formula you can convert values in a cell into lower case?

We can convert data into lower case letter by using LOWER formula. - see example below Value in cell B2 = LOWER (A2)

Using which function you can convert data in upper case letter?

We can convert data into upper case by using UPPER function - see example below Value in cell B2 = UPPER (A2)

How to update data on a PivotTable after changing the data in database?

When we update or change a data in a database then we have to press a refresh button on pivotable because after that data will be update in PivotTable. For refresh you can right click on pivotable and then click refresh

How can you lookup data for cities, states and countries in Excel?

With Office 365 Microsoft has introduced option to extract the geographic data for the cities, states and countries. In order to extract the data, first enter the city, state or country name on the Excel workbook as shown in the example below Once entered select the data and choose the option Geography from the data tab and once you do that Excel will convert the data and connect it to an online source so you can extract more information. The icon (flag-like) will appear against the names which are connected, refer to the below image to see the results. Then we can use the add a column button after selecting the range to add more information to the sheet, note that while we can add information using the button, the headers will not be added and we have to add that information manually.

How can you lookup real time stock, mutual fund or ETF prices in Excel?

With Office 365 and Excel online its quite easy to get the stock prices and information you're looking for. In order to do the same first we have to enter ticker symbol, company name or fund name on the Excel workbook. See the example below Once enter all you need to do is simply go to data tab and then click the stocks option and then Excel will convert the information to stocks and you can know if its been converted to stocks and is connected to an online source with more information apparent with the appearance of the symbol before the name. Once you have the stocks converted you can add more information by selecting one or more stocks and clicking the button add a column which will appear on the top right corner of the selection. Clicking that will give options to add the data type. You can add as much information by simply clicking the options and do note that the headers are not added automatically for the data so you would have to manually type that information.

How can you protect Worksheets in Excel and why might you consider doing it?

Worksheets protection is a two-step process, by default all the cells in an Excel worksheet are have a locked status from the format options and so if you want users to only be able to edit certain cells then you would need to unlock them. Let's see an example of the same. Observe the image below and we are going to allow users to edit in the cells B3:C7 so we are going to select the area we want users to allow the editing. Then right click and select the option format cells and then to go the Protection tab and uncheck the option Locked and then select OK. Now we are ready to protect the worksheet and go the review tab and select the option Protect sheet and then you enter the password which optional based on your requirements and hit OK and your worksheet should now be protected except for the cells which are now unprotected. Why protect your worksheet? Protecting the sheet has many applications as it can prevent users from making unintended changes to the sheet, or you only want them to edit only certain cells etc.

What is Excel PowerQuery and how can you use it?

Power Query is the technology within Excel tht enables business users to access data stored in various different sources and allows users to import and transform the data without writing programming code. Power Query supports data sources such as file, databases, Microsoft Azure services, and many other third-party online services. Power Query also provides a Custom Connectors SDK so that third parties can create their own data connectors and seamlessly plug them into Power Query. Starting with Excel 2016, Power Query capabilities are natively built into Excel and can be found under the Ribbon - > Data->"Get & Transform" section. Power Query Functionality is also available in Excel 2010 and 2013 by installing the Microsoft Power Query for Excel add-in.

What is Excel Dashboard and when should you consider using it?

An Excel dashboard is designed to help business leaders in tracking Key Performance Indicators and metrics to enable data driven decision making based on the data presented in dashboard. Dashboard typically contains charts/tables/views that are sourced by data, located at different datastores. Sometimes dashboard might be called a report, however, not all reports are dashboards.

If you are trying to print Excel Worksheet but data doesn't fit onto the page, what are the options to make it fit?

Multiple options exist in Microsoft Excel to make document fit into the page. You can adjust the print settings in 4 ways when such an event happens. • You can choose to fit the sheet on one page and Excel will adjust print area in such a way that it all fits on a single page. • If you're having issues with column, then you can choose option Fit all Column on one page to ensure that it stays on one page. • If it's the rows that are giving you the problem then you can choose the option Fit All Rows on One Page to solve your issues • You also have further option to set custom scaling options by using the Custom Scaling Options to adjust various aspects to make the sheet fit your requirements.

What is the purpose of SUBSTITUTE function?

SUBSTITUTE function is used to change any character or word from a cell.

How can you duplicate Excel worksheet?

In order to duplicate the worksheet, all you need to do is simply just right click on the worksheet name and then select the option Move or Copy. After that select the position in the same workbook or a different workbook where you want the new sheet to appear and check the option Create a copy and hit okay and the duplicate worksheet will be created.

What is the purpose of LEN function?

LEN function is used to count the number of characters in any cell.

What is the purpose of LEFT formula?

Left Formula is use to extract words from the left side of Cell.

How can you save your Excel data into PDF file?

Saving the Excel data as PDF is quite easy. In order to do that follow the below steps. • Open the Workbook you want saved as PDF. • On the file tab click Save As. • Then select the location to save the File to. • After you name the file, select the PDF from the option Save as type • You can also choose Options to select active sheet, all sheets or only selected area. • Click Ok and then click save as you're done.

What is the concept of "spilling data" in Excel?

Spilling data is new feature introduced with new formula type which can result multiple values with a single formula and the data which gets filled without the formula can be considered as the data spilled. This feature is new and has been introduced to Excel Online, Office 365 and more. Let's see an example of one such formula in Excel and we can see how spilling works. In the above example our source data is stored in range A1:D7 and we are using a spill formula to get the data from the range A1:D7 and you can see that it brought the range over and which the help of a single formula. This is an example of data spilling.

What is the correct name of the area outlined in yellow on the screen print below?

Excel name box

What is the purpose of SEARCH function?

SEARCH function is used to find any word in a cell or between any word in cell

On which tab you can locate Grouping and Ungrouping option? a) View Tab b) Review Tab c) Data Tab d) Formula Tab

Answer c is correct. We can Locate grouping and ungrouping option exist in data tab.

What is the purpose of CONCATENATE and & functions?

CONCATENATE and & functions is use merge any two or more than two words in a cell

How to make Pivot Table using multiple sources of data?

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

What is the purpose of RIGHT function?

RIGHT function is use extract words from the right side of the cell.

What is the purpose of TEXT formula?

Text Formula is used to change the format of any word, like if you want to write a date in text format then text formula will be use.

Determine what is the correct name for the areas, highlighted in red below?

These are excel column and row labels

What is Excel Pivot Chart and when should you use it?

A Pivot Chart is a graphicl representation of the data from Pivot Table. A Pivot Chart is especially useful for user when you are trying to analyze and display results from large dataset. Follow these steps to insert Pivot Chart in Microsoft Excel: 1. Click on any cell inside of already created Pivot Table 2. Click on Analyze Tab on the Menu Bar, and further click on Pivot Chart under Tools section. 3. The Insert Chart dialog box appears. 4. Click on the desired Pivot Chart required to represent the Pivot Table. 5. Click on OK.

What is a Pivot Table and when should you use them in Excel?

A pivot table is a data summarization tool that is used to analyze and report the data. Pivot tables are used to summarize, sort, reorganize, group, count, total or average data stored in a database. It allows its users to transform columns into rows and rows into columns. It allows grouping by any data field.

What is SmartArt in Excel and when would you use it?

A smart art is a graphic representation of information which you use to add more flare to your spreadsheet as it increases the visual appeal for the document. It can show information based on type of information for you want to display, for instance List, Process, Cycle, Hierarchy or more. Refer to the following image to see the options available across the smart art to see it in action. When can you use it? It depends on you but smart art is very helpful in portraying data visually, for instance if you want to show the relationship between the data or you want to show the pyramid structure or hierarchy of the data, its often well explained by a smart art than just text

What is the difference between absolute and relative reference in Microsoft Excel?

Cell reference refer to the address of an Excel cell as we know and understand. The rows on Excel are referred using numbers and the columns are based on the Alphabets. The combination of an Alphabet and a number becomes the address of a cell like A1. Relative Reference In Excel the concept of dragging formulas is very helpful and with the help of relative references we can apply the same formula to a lot of cells without copying and changing the formula. Let's see an example below to see this concept in action. In the image above we are multiplying the price by unit cost and the formula is =B2*C2 and we can use the Excel drag formula to apply the same formula to all the cells till D6 and the formula will be adjusted for all the cells. Like in the image below notice the formula for D6 how it's been adjusted to say =B6*C6 And this happens because it's a relative cell reference so the formula adjusts relative to the cells it's getting dragged to. Absolute Reference Absolute reference is used when you want to drag the formula but you want to keep a row or column or even both of them consistent. We can use the dollar sign ($) to make a cell consistent. Let's see this on a formula in a below example to see it in action. Now in the formula above you can see the formula = D3*$E$1, as you can observe the reference to cell E1 has dollar signs before both the column and row reference and so we can drag the formula and that will remain unchanged when drag it. Have a look at the image below on the cell E7 and you can see while we are dragging the formula the reference to cell E1 remains unchanged. Similarly, you can use the dollar sign to fix column or row reference based on your requirement. **Note: You can toggle between different kind of cell references using the F4 key.

What statement below is relevant to Pivot Table? a) PIVOT TABLE is used to summarize data. b) PIVOT TABLE is used to find data. c) PIVOT TABLE is used for formatting of the data. d) None of the above.

Correct answer is a. Let's review why this answer is correct and other answers are incorrect: • Answer a is correct, PIVOT TABLE is utilized to summarize data, with PIVOT TABLE we can easily make summary of the data in multitudes of way we like. • Answer b is incorrect because, PIVOT TABLE cannot be used to find data. • Answer c is incorrect because the ideal usage of PIVOT TABLE is not formatting.

What is the purpose of flash fill? a) Flash fill is used to enter data. b) Flash fill is used for, auto fill a value based on the patterns of the data you entered before. c) Flash fill is used to generate values. d) None of the above

Correct answer is b. Let's review why this answer is correct and other answers are incorrect: • Answer a is incorrect and it is not used to enter data just by using flash fill. • Answer b is correct. Flash fill is used to automatically fill the data after putting a couple of examples and Excel will do its best to enter the data by analyzing your patterns. • Answer c is incorrect, as Flash fill cannot generate values.

What is the shortcut key combination of Paste special? a) CTRL + V b) CTRL + ALT + V c) CTRL + A d) CTRL + Shift + V

Correct answer is b. Let's review why this answer is correct and other answers are incorrect: • Answer a is incorrect because CTRL + V is the shortcut key combination to paste data as it is. • Answer b is correct. CTRL + ALT + V can used to open the Paste special dialog box and access all the options that come along with it. • Answer c is incorrect because CTRL + A is used to select all data or sheet. • Answer d is incorrect because there is no such shortcut key combination in Excel.

Which of below path should we follow to locate the option select "Visible cells" only? a) Home tab > Find and select > Go to b) Home tab > Find and select > Go to special c) Home tab > Find and select > Data validation d) Home tab > Find and select > Constants

Correct answer is b. Let's review why this answer is correct and other answers are incorrect: • Answer a is incorrect because Go to option is used to jump on a specific table or a range for which have a named range in the Name manager. • Answer b is correct. We can follow Home tab > Find and select > Go to special and find the option to select "Visible cells only". • Answer c is incorrect because Data Validation is use to apply control on a specific range. • Answer d is incorect because Constants are used to select only those cells in which something is written.

Which formula can be used to calculate total sales which we can find by multiplying individual unit sold by unit price for every row across the range C2:D17? a) = SUMIF b) = SUMPRODUCT c) = SUMIFS d) = DSUM

Correct answer is b. Let's review why this answer is correct and other answers are incorrect: • Answer a is incorrect because SUMIF formula is used to calculate the SUM with one criterion. • Answer b is correct. SUMPRODUCT is used to multiply cells of different range with each other and then calculate the SUM of result. • Answer c is incorrect because SUMIFS is used to calculate the SUM when we have more than one criterion. • Answer d is incorrect because DSUM is used to calculate SUM when we have criteria as the heading.

What is the purpose of data validation in Excel? a) Data Validation is used to calculate the data. b) Data Validation is used to find the data. c) Data Validation is used to apply control (what we want to enter in a cell) d) None of the above

Correct answer is c. Let's review why this answer is correct and other answers are incorrect: • Answer a is incorrect as Data Validation is not used for calculating anything. • Answer b is incorrect as Data Validation not used for locating the data. • Answer c is correct. Data Validation is used to apply control on what kind of data can be entered on the selected range of cells.

How can you duplicate Excel workbook?

Duplicating a workbook is quite simple, you can simply create a copy of the Excel file you have using the host file manager but we are going to focus on the method based on the Excel. So, all you need to do is simply just to go the file ribbon and then select the option save as then select the browse option. Rename your workbook whatever you might like and there you have now the copy of the original workbook created and open as well

How would you use Excel freeze panes concept and why you might consider doing it?

Excel Freeze Panes can be utilized by following method. Go to View tab and select the option Freeze Panes. From there we have three options to choose from • Freeze Panes - If you select a single column or single row then any column preceding that column or row will be frozen. If you want both rows and column frozen at once then place the selection on a cell before the column and row you want frozen and that section will then be frozen. A faint line will appear dividing the frozen columns and nonfrozen columns. • Freeze first Column - Only the first column will be frozen. • Freeze first Row - Only the first row will be frozen. When to use the Freeze Panes option? Sometimes we want to navigate around a data and want to see the headers of the section be visible all the time or sometimes we want to see some information constantly visible, in that case this option is very helpful.

What is Excel range, and how is Excel Range different from Excel Tables

Excel Range is a group or block of cell references that are entered to perform calculations or as an argument for a function. Range can also be used to create a graph, or used to analyze the data. There is also a concept of Named Range. A named range is just one or more cells to which you, or Excel, have assigned a name. A common one that Excel adds automatically is "Print Area. Named ranges can be individual cells that you've named to make formulas easier to read, e.g., instead of "=B1 + C2" if you've renamed both cells your formula could be "=Revenue and Expenses", just for example. Named ranges can also be created for groups of cells. Excel Table is a defined grid of cells for data and formulas that automatically expands as you add to it and also automatically has the capacity to sort and filter. Creating a table also automatically adds a named range to your worksheet.

What is the Excel VBA and when do you use it?

Excel VBA stands for Visual Basic for Applications is the programming language of the Excel and it can be utilized for a variety of use. The only limit is your imagination. It can add features to Excel and you can use to automate the tasks, connect any online source to sheet, add user forms and a lot more. Programmed code in Excel VBA is often referred to as a Macro as well.

What is the function of Format Painter in Excel?

Format painter is an amazing feature of Excel and as the name suggest it formats. What it does it can copy the format from area to other. Let's see it in action in the below example. • Observe the cells B3 and D3 in the image below and notice how the B3 has a "Blue Accent 1" color and accounting formatting in it. • Now we can use the Format Painter to transfer the formatting using the Format Painter. First select the cell B3 then on the home tab, in the Clipboard group, click Format Painter. • The moving lines appear across the selected cell and the mouse pointer changes to plus and a paintbrush. • Now all you need to do is just simply select the cell D3 and then it would be formatted the same way as B3. **Note: You can double click Format Painter to apply same formatting to multiple cells.

What are Excel Tables and when you might consider using them?

Excel tables have been designed to make managing and analyzing a group of related data easier. Excel Table can include the following elements: Header row: Header row is necessary to uniquely identify each column and provide descriptions of table columns to the user. By default, a table has a header row. Header row also provides filtering enabled in the header row so that you can filter or sort your table data quickly. Regular rows: Alternate shading or banding in rows helps to better distinguish the data. This alternative shading extends as the data in the table grows and can be turned off in the table settings. Calculated columns: Can be created instantly and applied to all other cells in that table column. Total row: Table provides an opportunity to calculate totals. Once you add a total row to a table, Excel gives you an AutoSum drop-down list to select from functions such as SUM, AVERAGE, and so on. When you select one of these options, the table will automatically convert themT to a SUBTOTAL function, which will ignore rows that have been hidden with a filter by default. If you want to include hidden rows in your calculations, you can change the SUBTOTAL function arguments. Important Considerations: • Excel tables are different from the data tables that are part of a suite of what-if analysis commands. • You cannot create or insert tables in a shared workbook.

What is the purpose of FIND function?

FIND formula is used find any word in cell.

What is XLOOKUP formula?

XLOOKUP function performs lookups in vertical or horizontal ranges. It is a more robust and flexible successor than traditional VLOOKUP (or HLOOKUP, and LOOKUP functions). XLOOKUP supports approximate and exact matching, and wildcards (* ?) for partial matches. Below is the syntax of XLOOKUP Formula =XLOOKUP (lookup, lookup_array, return_array, [not_found], [match_mode], [search_mode]) Where arguments are used as follows: lookup - The value to lookup lookup_array - The array or range to search. return_array - The array or range to return. not_found - [optional] Value to return if no match found. match_mode - [optional] 0 = exact match (default), -1 = exact match or next smallest, 1 = exact match or next larger, 2 = wildcard match. search_mode - [optional] 1 = search from first (default), -1 = search from last, 2 = binary search ascending, -2 = binary search descending.

How can you protect Workbook in Excel and why you might consider doing it?

You can protect a workbook by going to the review tab and then selecting the option Protect Workbook. Refer to below image to see how it looks. Make sure the structure option is selected (Windows option is available only in Excel 2007, Excel 2010, Excel for Mac 2011, and Excel 2016 for Mac.) and then you can save the workbook by entering a password or without one as per your requirements. Why Protect the workbook? We can use the protect workbook option to prevent users from viewing hidden worksheets, adding, moving, deleting, or hiding worksheets, and renaming worksheets.

If you know that particular functionality exists in Excel but you are not sure on which Ribbon tab it is available how can you use it quickly?

You can use search functionlity on Excel Ribbon. You can type in the name of menu item or functionality in the Search box and Excel will present you with list of options available, that matches your search criteria - see below image.