200+ Excel Interview Questions & Answers

1. When would you use Pie Charts?

It’s a good idea to use a Pie Chart if you are trying to compare parts of a whole and not changes over time.

2. How to calculate the difference between two times?

Excel stores dates as integers, and times as fractions. For this reason, you can subtract one time from another using a mathematical operator and format it as time.

3. How to create a keyboard shortcut to a macro?

You can do it using the icon in the bottom-left part of a window for a new macro or navigate to View >> Macros, where you can also edit macros that you’ve already created.

4. How to make a row header visible when you scroll?

You have to freeze the top row. In order to do it, go to View >> Window >> Freeze Panes >> Freeze Top Row.

5. How to sort multiple columns at once?

To sort multiple columns at once, you have to use custom sort. You can find it in Home >> Editing >> Sort & Filter >> Custom Sort. Here, you can find multiple levels of sorting.

6. What’s the difference between COUNT, COUNTA, COUNTBLANK?

COUNT – count numbers.

COUNTA – counts all cells that are not empty.

COUNTBLANK – counts the number of empty cells.

7. What’s the difference between COUNTIF, COUNTIFS?

The COUNTIF function returns the number of cells in a range that meet the given criteria. The COUNTIFS applies criteria to cells across multiple ranges and counts the number of times all criteria are met.

8. What’s the difference between VLOOKUP and HLOOKUP?

The HLOOKUP (horizontal lookup) function searches for a value in the top row of a table. The VLOOKUP (vertical lookup) function is used to lookup and retrieve data from a specific table column.

9. What’s the difference between XLS and XLSX?

XLS is the old proprietary binary file format used in Excel before Excel 2007. Since then, it has been abandoned and changed to an XML-based XLSX format.

10. How to add a space between words?

You have at least two options. You can use the following formula =A1&” “&A2 or the CONCAT (formerly CONCATENATE) function. With the function, the formula will look like this.

=CONCAT(A1," ",A2)

1	=CONCAT(A1," ",A2)

11. How many rows and columns Excel has?

Excel 2019 contains just over 1 million rows (1,048,576), which is 2^20 and 16,384 (2^14).

12. How to quickly get the column number of the column XEI?

You can use the COLUMN function and reference to any cell inside this column. =COLUMN(XEI1). The number of column XEI is 16363.

13. How to change the delimiter in CSV?

Excel uses a regional system setting to determine the default separator. If you use Windows, you can change it in Control Panel >> Change date, time, or number formats. Inside the Region window, under the Formats tab, click Additional settings. Inside the Customize Format window, under the Numbers tab there is the default List Separator, which you can change.

14. How to use data validation?

Data validation is located on the Ribbon, under Data >> Data Tools. You can decide here what kind of data is required. It can be a whole number, decimal, list, date, time. You can also set ranges for the required data – for example, a number between 1 and 10.

15. How to format the date to show only a month and a year?

There are two ways you can do it.

In the first method, you can right-click the date and choose Format Cells … Next, on the Numbers tab, chose Date as Category and choose the desired format. If you don’t see the format you like, select Custom, instead of Date.

In the second method, use the TEXT function.

The first argument is a date, and the second one is the type of formatting.

=TEXT(A1,"mm-yyyy")

1	=TEXT(A1,"mm-yyyy")

16. Write a formula for how many days have passed.

You can use arithmetical operators on dates because dates are stored as a number in Excel.

number_od_days = later_date – earlier_date

Otherwise, it will return a negative number. If you always want to get a positive number, you can use the ABS function to calculate the absolute value.

number_of_days = ABS(date – date)

17. How to switch axes in a Chart?

You can switch exes in Excel without changing data by following these steps:

Click a chart to select it.
Click the Design Tab on the Ribbon.
Navigate to Data >> Switch Row/Column.

18. How to create a header?

You can create a header in two ways.

The first way is to navigate to View >> Workbook Views >> Page Layout. After this change, you can add a header to the document.

And now, the second method. Navigate to Insert >> Text >> Header and footer.

The difference between these two methods is that in the first one, a selected cell is highlighted. In the second one, the header is highlighted, and you can start typing immediately.

19. How to delete blank rows?

First, you have to find blank rows with Go to Special and then remove them with Home >> Cells >> Delete Cells.

20. How to transpose?

First, you have to copy cells. Next, right-click a cell and choose Transpose, or press “t” twice, and then Enter.

21. How to lock cells?

Go to Review >> Protect >> Protect Sheet. Here, you can choose what kind of protection you want and set a password.

22. How to move a column?

Click column letter to show a green border. Then grab and move the edge to the place you want the column to be moved.

You can also do it differently. First, click the column letter, press Ctrl + X, then click column where you want to place the column and press Ctrl + V.

23. How to remove spaces from a cell?

You can use the SUBSTITUTE function and change all spaces to no character. In other words, it removes spaces.

=SUBSTITUTE(A1," ","")

1	=SUBSTITUTE(A1," ","")

24. How to round down a value?

If you want to round down a number to the nearest integer, use this formula:

=ROUNDDOWN(5.67,0)

1	=ROUNDDOWN(5.67,0)

The second argument indicates how many decimal places you want. If there is 0, there are no decimal places. In this case, the result is 5.

25. How to add text to a number?

You can use the CONCAT function (formerly CONCATENATE), or the concatenate operator (&) and add a number to a text the same way as adding text to a text.

=A1&A2

=A1&A2

26. How to change all letters to uppercase?

Use the UPPER function.

=UPPER("This is a text")

1	=UPPER("This is a text")

gives

THIS IS A TEXT

27. How to convert letters to lowercase?

Use the LOWER function.

=LOWER("This is a text")

1	=LOWER("This is a text")

gives

this is a text

28. How to convert text, so each word begins with a capital letter?

Use the PROPER function.

=PROPER("This is a text")

1	=PROPER("This is a text")

This Is Text

29. How to convert hex to decimal?

This formula converts hexadecimal value to decimal.

=HEX2DEC("1A")

1	=HEX2DEC("1A")

The result is:

30. How to insert a new line in a cell (line break)?

Place a cursor in a cell where you want a line break and then press Alt + Enter.

31. How to find and replace data in formulas?

Navigate to Home >> Editing >> Find & Select >> Replace or use the Ctrl + H keyboard shortcut.
Click Options >> to show additional options.
Choose: Look in: Formulas.

32. How to format the date as text?

The quickest way to do it is to add “‘” (single quote) before a date in the same cell.

33. How to format date using a formula?

You can do it with the TEXT function. This is how it works:

=TEXT(TODAY(),"dd-mm-yyyy")

1	=TEXT(TODAY(),"dd-mm-yyyy")

It returns the following result for the current date:

05-10-2019

1	05-10-2019

34. How to freeze multiple rows?

If you want to freeze the first three rows, you have to click cell A4 and navigate to View >> Window >> Freeze Panes >> Freeze Panes.

35. How to get the current date?

Use the TODAY function. It will return the current date in the date format:

36. How to get the current date and time?

Use the NOW function. It will return the current date and time in the date and time format:

10/05/2019 11:03

1	10/05/2019 11:03

37. How to import a CSV file?

You can import CSV file by navigating to Data >> Get & Transform Data >> From Text/CSV. Select a CSV file, and then click Load.

38. How to link data from another sheet?

You have to use a sheet name before referencing cells.

=Sheet2!A1:B2

1	=Sheet2!A1:B2

39. How to remove duplicates in a column?

Select cells in a column, you want to check, then navigate to Data >> Data Tools >> Remove Duplicates.

40. How to remove spaces before and after the text?

Use the TRIM function.

=TRIM("  this is a text     ")

1	=TRIM(" this is a text ")

The above code returns “this is a text” without spaces at the beginning or the end of the text.

41. How to replace a character?

To change one character into another, use the SUBSTITUTE function.

=SUBSTITUTE("111 222 333"," ","-")

1	=SUBSTITUTE("111 222 333"," ","-")

This code will give us the following result.

"111-222-333"

1	"111-222-333"

42. How to show an absolute value?

Use the ABS function. It returns a number without a sign.

=ABS(-1.2) returns 1.2

43. How to split cells?

To split cells, you can use Text to Columns, which you can find in Data >> Data Tools.

44. How to show zero if a value is divided by zero?

To handle exception in Excel, you can use the IF function because it will return an error. Instead, you should use the IFERROR function.

Now, if the function returns an error (dividing by 0), it’s going to replace this error with a given value. Here’s how it look like:

=IFERROR(5/0,0)

1	=IFERROR(5/0,0)

This formula will return zero. If we are not dividing by zero, it will return the result.

45. What does the IRR function do?

It returns the internal rate of return.

46. When to use Power Query?

Power Query is a technology that allows users to access data stored in many data sources with no-code user experience.

47. Show apostrophe before the text.

Adding a single apostrophe in the front of a cell will convert this cell into text. Add two apostrophes, and it will display it as a single apostrophe.

''Text

''Text

48. Show leading zeros data.

Right-click a number and choose Format Cells. On the Number tab, click Custom and inside Type enter zeros. If you enter five zeros, value 123 will be displayed as 00123.

49. How to quickly access Visual Basic Editor?

You can access VBE using Left Alt + F11.

50. Excel shows #### instead of value. How can you fix it?

Excel shows hash signs if there is not enough room to display value. Just expand the column to fix this issue.

51. How can you use Excel templates?

Templates in Excel are used to increase productivity in different kinds of jobs, such as finance, teaching, project management, etc.

52. Explain a PivotTable and its uses.

PivotTables are used to create good-looking reports for large data sets. They are used to summarize, sort, reorganize, group, count, total or average data stored in a database or inside a worksheet.

53. Explain a spreadsheet.

A spreadsheet is an interactive application for organization, analysis, and storage of data in tabular form. It consists of rows and columns and simulates a physical spreadsheet.

54. What is the difference between the SUBSTITUTE and REPLACE functions?

The SUBSTITUTE function replaces all of the occurrences of the given word. You can also use this function to replace only a single occurrence of the word, by providing the fourth, optional argument.

=SUBSTITUTE(text, old_text, new_text, [instance_num])

1	=SUBSTITUTE(text, old_text, new_text, [instance_num])

The REPLACE function replaces the given number of words, starting from the given position with the new text.

=REPLACE(old_text, start_num, num_chars, new_text)

1	=REPLACE(old_text, start_num, num_chars, new_text)

55. Explain workbook protection types in Excel.

You can restrict a user from the following:

Selecting cells.
Formatting cells.
Formatting rows and columns.
Inserting rows and columns.
Inserting hyperlinks.
Deleting rows and columns.
Sorting.
Using Autofilter.
Using PivotTables & PivotCharts.
Editing objects.
Editing scenarios.

You can find all these options under Review >> Protect >> Protect Sheet.

56. Explain format painter.

Format painter is a tool to format a cell in the same way that another cell is formatted. You can find it in Home >> Clipboard >> Format Painter.

57. How to add a new Excel worksheet?

There is a button (+) at the bottom of the screen to add a new sheet. You can also use the Shift + F11 keyboard shortcut.

58. How to apply the same formatting to every sheet in a workbook?

Select all sheets by right-clicking a worksheet and then choose Select All Sheets. Now, when you have all the sheets selected, any change you make inside a sheet will also be applied to the rest of the sheets.

59. How to combine the text from multiple cells using a function?

There are two functions you can use to combine text: The CONCAT (formerly CONCATENATE) and TEXTJOIN functions.

60. How to merge cells?

To merge cells, use the Merge & Center option. You can find it in Home >> Alignment on the Ribbon.

61. How to refresh a PivotTable?

There are at least to ways you can achieve this:

Right-click PivotTable and click Refresh from the contextual menu.
Click PivotTable and choose PivotTable Tools >> Analyze >> Data >> Refresh.

62. How to resize a column?

You can resize a column by moving your cursor between two columns header and drag it left or right. You can also double click it. Then the cell will have the width of the widest value inside this column.

63. How to select all blank cells?

Go to Home >> Editing >> Find & Select >> Go To Special. Select Blanks and click OK.

64. How to sort data?

You can sort data by clicking the column you want to sort. Then you should navigate to Home >> Editing >> Sort & Filter, where you can choose whether you wish to sort your data in ascending or descending order.

65. How to wrap text?

There is a feature in Excel you can use to wrap text. You can find it in Home >> Alignment >> Wrap Text.

66. Demonstrate a few ways to zoom in or zoom out.

There are a few ways to zoom in Excel:

View >> Zoom.
Use tools in the bottom right corner.
Use Ctrl + Mouse Wheel.

67. How to apply conditional formatting to an entire row?

Click the row number to select the entire row and go to Home >> Styles >> Conditional Formatting.

68. How to calculate 10% of a number?

You can multiply the number by 0.1, or use the following formula:

=A1*10%

=A1*10%

69. How to get rid of scientific notation?

Right-click the number to open the contextual menu and click Format Cells. Inside the Category field, choose Number and then click OK.

70. How does Excel EXP work?

The EXP function returns the constant E raised to the power of a given number. E = 2.71828… and is the base of the natural logarithm.

=EXP(0) = 1
=EXP(1) = 2.71828183
=EXP(2) = 7.3890561

=EXP(0) = 1

=EXP(1) = 2.71828183

=EXP(2) = 7.3890561

71. How does Excel quartile function work?

QUARTILE.INC (formerly QUARTILE) works similarly to the PERCENTILE.INC function. It returns the quartile of the given data set.

=QUARTILE.INC(A1:A8,1)	=PERCENTILE.INC(A1:A8,0.25)
=QUARTILE.INC(A1:A8,2)	=PERCENTILE.INC(A1:A8,0.5)
=QUARTILE.INC(A1:A8,3)	=PERCENTILE.INC(A1:A8,0.75)

=QUARTILE.INC(A1:A8,1) =PERCENTILE.INC(A1:A8,0.25)

=QUARTILE.INC(A1:A8,2) =PERCENTILE.INC(A1:A8,0.5)

=QUARTILE.INC(A1:A8,3) =PERCENTILE.INC(A1:A8,0.75)

The QUARTILE.INC function can also have 0 and 4 as the second parameter. They work the same way as MIN and MAX functions.

72. How does VLOOKUP work?

You can use VLOOKUP to find things in a table or a range by row. For example, find an employee name based on their employee ID.

73. How does the slicer work?

Slicers work similarly as Filter Controls. They are less flexible, but their look is more appealing. They are useful for people who don’t know Excel well, and filtering controls may seem to be too complicated for them. The disadvantage of this solution is that they use a lot of space on the screen so that it can be a problem for smaller displays.

74. How Excel stores dates and times?

Excel stores dates as integers and times as decimal fraction. Each day is 1 counting from the year 1900.

10/07/2019 12:09 equals 43745.51

You can check your time by formatting them as a number. The more decimal places you use, the more precise the result will be.

75. How many data formats are there? Can you name a few?

There are eleven number formats (+ custom) in Excel:

General
Number
Currency
Accounting
Date
Time
Percentage
Fraction
Scientific
Text
Special
Custom

To check all of them, right-click a cell and choose format cells. You will see all of them on the Number tab in Category.

76. How to add 50 days to a date in Excel?

When you have the TODAY function, you can add + 50 to see what date it will be 50 days from now.

If today is 10/07/2019, then 50 days from now is 11/26/2019.

=TODAY()+50

1	=TODAY()+50

77. How to add a comma before or end of the text?

A1 = This is text

=”,”&A1 returns “,This is text”

=A1&”,” returns “This is text,”

You can also use the CONCAT function.

=CONCAT(“,”, A1) returns “,This is text”

=CONCAT(A1, “,”) returns “This is text,”

78. How to add decimal places?

You can format a cell as a number using right-click and then Format Cells, or you can navigate to Home >> Number and click Increase Decimal or Decrease Decimal. It will increase or increase a decimal point by 1 each time you click the button.

79. How to add values?

There are a few ways to add in Excel.

With an addition operator (+).

=A1+A2+2

=A1+A2+2

With the SUM function.

=SUM(A1,A2,2)

1	=SUM(A1,A2,2)

AutoSum feature, which you can find in Home >> Editing >> AutoSum.

80. How to add minutes to time?

You can add minutes using at least two approaches.

A1 = 0:45

=NOW() + A1

1	=NOW() + A1

Or you can type minutes directly this way.

=NOW() + "0:45"

1	=NOW() + "0:45"

81. How to add a secondary axis?

You can add a secondary axis using this approach.

Click anywhere in a chart.
Select ChartTools >> Design >> Type >> Change Chart Type.
Choose Combo >> Cluster Column – Line on Secondary Axis.
Select a secondary axis for the data series you want to show.
Select the drop-down arrow and choose Line.
Click OK.

82. How to add/delete rows?

There are a few approaches you can make to fulfill this task:

If you want to delete multiple rows, select their number, then use the right-click and then Delete. If you’re going to do it for just a single row, right-click its number and then Delete.
Select cells, then navigate to Home >> Cells >> Delete >> Delete Sheet Rows.
Select a cell, and use the Ctrl + – keyboard shortcut. From the options you get, choose Entire row.

83. How to adjust column width in Excel, so it’s just enough?

To adjust the width of the column (e.g., column B), move your cursor between letters B and C and double-click. Whether the column was too narrow or too wide, it will adjust to the widest cell inside this column.

84. How to AutoFill a range?

Click a cell you want to use for AutoFill and drag it the desired number of cells inside this column. Sometimes, depending on whether the adjacent column is filled, you can use double-click.

85. How to calculate the median?

To calculate a median, take the data set you want to use as the parameter of the MEDIAN function.

=MEDIAN(A1:A10)

1	=MEDIAN(A1:A10)

86. How to calculate square meter from square feet?

1 square meter = 10.7639 square foot

15 square meters = 10.7639 * 15 = 161.459 square foot

=15 * 10.7639

1	=15 * 10.7639

87. How to calculate the top 10%?

For this task, you have to use PERCENTILE.INC function and calculate the 90% percentile.

=PERCENTILE.INC(A1:A10,0.9)

1	=PERCENTILE.INC(A1:A10,0.9)

88. How to center text in a cell?

Navigate to Home >> Alignment and click the Center button.

89. How to change the author’s name in comments?

If you have a comment inside a cell, right-click this cell and select Edit Comment. Double-click the author’s name and change it to the name you want.

90. How to change the currency?

There are a few ways you can achieve this:

Navigate to Home >> Number. Click a little triangle icon, next to the dollar sign, and choose the format you want.
Right-click a cell and choose the Format Cells option. Choose Currency from the Category window and select symbol from the dropdown list.

91. How to change the default Excel date format?

Right-click a cell and select Format Cells. Now, on the Number tab, click Date option.

92. How to change the default weekend?

You have to do it outside Excel. On Windows, follow these steps:

Open Control Panel.
Set View to Category.
Click Change date, time, or number formats under Clock and Region.

93. How to convert binary value to hexadecimal value?

Use the BIN2HEX function.

=BIN2HEX(10110111)

1	=BIN2HEX(10110111)

returns

94. How to convert days to years, months, and days?

Let’s suppose you have 10,000 days, and you want to find out how many years months and days it is.

To do it, enter 10000 into a cell, right-click it, and choose Format Cells. Choose Custom and enter the following code into Type:

y "years," m "months," d "days."

1	y "years," m "months," d "days."

This will return the following result:

27 years, 5 months, 18 days.

1	27 years, 5 months, 18 days.

95. How to convert grams to kilograms?

1 kilogram = 1000 grams.

= grams / 1000

1	= grams / 1000

96. How to convert hours to days?

Let’s say you want to convert 10000 hours to days. The easiest way to do it is to divide the number of hours by 24.

=10000/24

The result of this operation is 416.6667.

There is a different approach. You can round down the number of days and then use the MOD function to return the remainder of a division.

=ROUNDDOWN(10000/24,0)&" days and "&MOD(10000,24)&" hours"

1	=ROUNDDOWN(10000/24,0)&" days and "&MOD(10000,24)&" hours"

result:

416 days and 16 hours

97. How to convert XLS to XLSX?

Open the XLS file in Excel 2007 or newer. Next, go to File >> Save As >> Browse. Choose: Save as type: Excel Workbook (*.xlsx).

98. How to copy and paste text?

To copy the text in Excel, right-click the cell and select Copy or use Ctrl + C. To paste, use Ctrl + V or right-click the cell you want to paste and choose one of many pasting options.

99. How to create a CSV file?

Open Excel file, then go to File >> Save As and choose Save as type: … (*.csv)

100. How to create a filter?

To create a filter, you have to convert your data to a table. Do it by clicking Insert >> Table. Now, in the column header on the right side, you have little arrow where you can access filtering options.

101. How to create a hyperlink?

You can do it:

From the Ribbon, by navigating to Insert >> Links >> Link.
By using the Ctrl + K keyboard shortcut.

102. How to create a PivotTable?

Click the data you want to use for PivotTable.
Navigate to Insert >> Tables >> PivotTable.

103. How to create sparklines?

Click a cell to insert a sparkline.
Navigate to Insert >> Sparklines and choose one of the three options available.
Select the Data Range.
Click OK.

104. How to reduce Excel file size?

There are a few ways you can do it:

Remove unnecessary worksheets.
Remove hidden rows and columns.
Compress images.
Remove formatting.
Save Excel files as XLSB.
Remove Pivot Cache.
Split workbook.
Compress Excel file to zip.

105. How to make an absolute reference?

Click a cell with reference to another cell.
Inside the Formula Bar, click cell reference and press F4.
Press Enter.

106. How to enter dates?

There are a few ways:

Insert number, then format it as a date.
Use the TODAY function.
Enter a date in the format you use.
Use the DATE function.

107. How to evenly space rows?

First, you need to select rows you for which you want to do this. Next, use one of the following methods:

Move the cursor between two rows of numbers and drag them to the desired height.
Right-click any row number you selected and choose Row Height. Now, choose the desired height.

108. How to explode a Pie Chart?

Click a pie chart and start dragging pies outside. You can also do it for a single pie.

109. How to export an Excel sheet to CSV?

In Excel, you can save a file as CSV.

Navigate to File >> Save As >> Browse. In Save as type, you can choose a file with the CSV extension.

110. How to find the largest number?

The MAX function returns the largest value in a set of values.

=MAX(number1, …)

1	=MAX(number1, …)

You can achieve the same effect using the QUARTILE.INC function:

=QUARTILE.INC(query, 4)

1	=QUARTILE.INC(query, 4)

111. How to find q1 and q3?

You can do it with the QUARTILE.INC and PERCENTILE.INC functions.

To find the first quartile, use one of the following formulas:

=QUARTILE.INC(A1:A8,1)
=PERCENTILE.INC(A1:A8,0.25)

1 2	=QUARTILE.INC(A1:A8,1) =PERCENTILE.INC(A1:A8,0.25)

To find the third quartile, use these formulas:

=QUARTILE.INC(A1:A8,3)
=PERCENTILE.INC(A1:A8,0.75)

1 2	=QUARTILE.INC(A1:A8,3) =PERCENTILE.INC(A1:A8,0.75)

112. How to return working days between two dates?

Use the NETWORKDAYS function.

=NETWORKDAYS(start_date, end_date)

1	=NETWORKDAYS(start_date, end_date)

113. How to insert a picture in an Excel cell?

Navigate to Insert >> Illustrations >> Pictures and choose a picture to insert.

114. How to link an Excel file in Word?

In Word, navigate to Insert >> Text >> Object >> Object. Choose: Create from File and click Browse. Now, select a file you want to link.

115. How to lock a chart in Excel, so it stays in position?

Right-click a Chart Area and click Format Chart Area. Click Size & Properties. Now, select Don’t move or size with cells.

116. How to lock columns?

Navigate to View >> Window >> Freeze Panes >> Freeze First Column.

To freeze more than one (for example, 2) columns, move the cursor to cell C1 and Freeze Panes. Now, two columns are locked.

117. How to secure an Excel file with a password?

Navigate to File >> Info. Here, you have a few different types of workbook protection. Click Protect Workbook and then Encrypt with Password.

118. How to remove Excel file password (if you know it)?

To remove Excel file password, you have to choose File >> Save As. Now, you can override the file or create a new name. Inside the Save As window, click Tools >> General Options. Remove password, click OK and then Save. Now, you can open the file without a password.

119. How to move cells?

To move a cell, click a cell and move the cursor to its border. Drag it and move it to the place you want it pasted. It will replace the value that was there before.

120. How to swap cells?

If you want to swap cells, click a cell and move the cursor to its border. Press and hold the Shift key. If you wish to swap cells in a row, move the cursor to the right of the cell. If you’re going to do it in a column, move it to the bottom of the cell.

121. How to multiply in Excel?

There are a few ways you can use multiplication in Excel:

Use the multiplication operator (*).

= A1*A2*A3

1	= A1A2A3

The PRODUCT function. It will multiply all the arguments.

=PRODUCT(A1, A2, A3)

1	=PRODUCT(A1, A2, A3)

122. How to name columns?

Click a letter of a column to select all the cells inside it. Go to Formulas >> Defined Names >> Define Name. Enter a name and click OK.

You can select this column inside the Name Box.

123. How to open CSV files?

If Excel already recognizes a file, you can open it. If it’s not recognized, right-click >> Open with >> Excel. You can also open Excel and then import the CSV file.

124. How to open a hyperlink?

Click and hold the Ctrl button and press the link.

125. How to remove a hyperlink?

Right-click the link and choose: Remove Hyperlink.

126. How to open large CSV files?

The best way to do it is to use Power Query.

127. How to open an XLSB file?

The XLSB file is an Excel binary file, and it opens the same way as an XLSX file.

128. What are the differences between XLSX and XLSB files?

XLSX is a file format that has been introduced in Excel 2007 and replaced the old XLS format. It’s stored as a compressed XML file.

You can extract this file with a standard file archiver like 7-Zip. Among other files, you will find XML, which you can read with a text editor. This file format can be created and read by other applications.

When it comes to the XLSB file, you can extract it, but you won’t find XML files, only binaries. If you try to open them with a text editor, you will find gibberish.

The advantage of the binary format is that it has a smaller size and opens and saves quicker.

129. How to quickly move the cursor to the last cell of the Excel column?

To quickly move to the last row, click a cell inside an empty column, or make sure there is no value in this cell below selected cell and press Ctrl + Down Arrow.

130. How to quickly move to the last cell in the table column?

Click a cell inside the Excel table and press Ctrl + Down Arrow. It will move the cursor to the last cell of the table.

131. How to refresh cells?

Press F9.

132. How to fix #DIV/0?

The #DIV/0! error occurs when there is a division by zero or by a cell that is empty. You can handle this exception by using the IFERROR function. It will return the second argument if there is an error.

Use the following formula to return 0 if there is an error:

=IFERROR(A1/A2,0)

1	=IFERROR(A1/A2,0)

Use this one to return blank, instead of error:

=IFERROR(A1/A2, "")

1	=IFERROR(A1/A2, "")

133. How to fix #REF!?

If there is a reference inside your sheet to other sheets, workbooks, etc. that don’t exist, you get the #REF! error.

You can get rid of this error by removing references to workbooks or worksheets that don’t exist.

The easiest way to do this is to select cells with #REF! errors and navigate to Home >> Editing >> Find & Select >> Go To Special. Select Formulas and check Errors.

Now, you have all the errors in formulas selected. Press Delete to remove these errors.

134. How to rotate Pie Chart?

To rotate a pie chart in Excel, you have to right-click it and choose Format Data Series. Now, in Series Options, drag the slider below Angle of first slice. It will work between 0 and 360 degrees.

135. How to round decimals?

There are a few ways you can achieve this.

The ROUND function rounds the number to the number of digits given in the second argument.

=ROUND(1.23456,3)

1	=ROUND(1.23456,3)

This will return the following result.

1.235

136. How to round to the nearest cent?

You can do it with the ROUND function. 1 dollar = 100 cents. Therefore you use 2 as the second argument.

=ROUND(1.23456,2)

1	=ROUND(1.23456,2)

This will return the following result.

1.23

137. How to round to the nearest half?

In this case, the MROUND function comes handy. It returns a number rounded to the nearest multiple.

=MROUND(1.23456,0.5)

1	=MROUND(1.23456,0.5)

This formula result is 1.

=MROUND(1.73456,0.5)

1	=MROUND(1.73456,0.5)

It returns 1.5.

138. How to save charts as images?

Right-click a chart and press Ctrl + C, then right-click a cell where you want the chart to be placed as image. Right-click again and choose to place it as Picture (U).

139. How to save macros?

If you try to save an XLSX file with a macro, Excel will return an error that the macro cannot be saved. To save the macro, you have to use the macro enables file.

Navigate to File >> Save As >> Browse. From Save as type, choose Excel Macro-Enabled Workbook (*.xlsm) and click Save.

140. How to select all the data?

There are at least two ways you can use:

You can click a button in the top left corner (column and row intersection) to select all cells inside this sheet.
Use Ctrl + A in a way that there is at least one empty cell over or to the left of the selected one.
Use Ctrl + A twice.

141. How to select the entire row?

The first method is to click the row number.

The second way is to click any cell inside a sheet and then press Shift + Space.

142. How to select non-adjacent cells?

Press and hold the Ctrl button and start clicking and dragging cells.

143. How to separate date and time in Excel using formula?

To achieve this task, you can use the TEXT function.

To separate date use this formula.

=TEXT(A1,"mm/dd/yyyy")

1	=TEXT(A1,"mm/dd/yyyy")

To separate the date use this one.

=TEXT(A1,"hh:mm:ss")

1	=TEXT(A1,"hh:mm:ss")

144. How to shift columns?

Click column name.
Move your cursor on the column border.
Press and hold Shift.
Move it to the place you want.

145. How to show all comments?

To show all comments, navigate to Review >> Comments >> Show All Comments.

146. Stop Excel from changing numbers to scientific notation?

You have to format the place where the scientific notion occurs to numbers. To do it, select the area you want, right-click and choose Format Cells. Click Number in Category and choose the options you want. Now if you enter a big number, it will be displayed without scientific notation.

147. How to subtract two numbers?

Use the arithmetic operator for subtraction (–).

=1-2
=A1-A2

1 2	=1-2 =A1-A2

148. How to subtract a percentage?

You can easily subtract percentages from numbers with the percentage operator.

If you use the following formula:

=5-5%

=5-5%

It will subtract percentage, but also format the value to percentages.

495.00%

495.00%

You probably don’t want to do it, so you have to format it as a number, or you can use a modified formula. Just use double quotes for percentages.

=5-"5%"

=5-"5%"

Now, the result is formatted as a number.

4.95

4.95

149. How to subtract dates?

You can do it by subtracting an older date from a newer date.

A1: 10/12/2019

A2: 04/11/2017

=A1-A2

=A1-A2

This formula will return 914.

150. How to remove a comment?

Click a cell with a comment and navigate to Review >> Comments >> Delete.

151. How to turn on autosave?

You can find autosave options in File >> Options >> Save. Under Save workbooks, there is an option called AutoSave OneDrive and SharePoint Online files by default on Excel. This will save your work on OneDrive and SharePoint accounts in real-time.

152. How to unhide all columns?

The fastest way to do it is to select the whole document and right-click any column letter. From the contextual menu click Unhide.

153. When will the Developer Tab be helpful?

As the name suggests, the Developer tab is helpful for developers. Here, you can find additional tools for code creation, add-ins, controls, and XML.

154. When to use Line Charts?

Line graphs are used to track changes over periods of time.

155. When to use Bar Charts?

Bar graphs are used to track changes over time or compare things between different groups.

156. How to use Excel quick analysis?

A quick analysis tool is a handy tool, and it’s used to have a preview of different options before selecting the one you want.

157. How to return the number of characters in string?

Use the LEN function.

158. How to use a PI?

You can enter 3.14, or use the PI function, which returns the value of PI, accurate to 15 digits.

159. How to use SUMIF?

The SUMIF function sums the values in a range that meets the criteria from the second argument.

=SUMIF(A1:A10, “>10”)

160. How to write superscript?

If you want to write, for example, m², write in a cell m2 and select 2.
Right-click and choose Format Cells.
In Effects, click Superscript.
Click OK.

161. How to clear all the formatting without deleting the cell contents?

You can do it in Home >> Editing >> Clear >> Clear Formats.

162. How to save an Excel worksheet as a PDF?

Go to File >> Save As and choose Browse. From Save as Type, select PDF (*.pdf).

163. What is the keyboard shortcut to display formulas?

Ctrl + (apostrophe)


164. Tell me about the new features in Excel 2019?
There are a lot of new features in Excel 2019. You can find
the full list of them on the Office
Suite site.
165. What is the difference between random and pseudorandom numbers?
A computer can't generate random numbers. Functions like RAND and RANDBETWEEN make pseudorandom numbers. 
Read
more >>
166. When to use sparklines?
A sparkline is a
tiny chart that you can place inside a cell to provide a graphic representation
of data. Sparklines are handy if you want to show trends in a
series of data. You should place them near the data.
Read more >>
167. How to move to the previous and next sheet with a keyboard shortcut?
Move to the previous tab: Ctrl + Page Up.
Move to the next tab: Ctrl
+ Page Down.
168. What are add-ins?
If you can't do something with standard Excel tools, you can
search for add-ins, which provide optional commands and features for Microsoft
Excel. Add-ins are not immediately available in Excel, so If you want to use
them, you have to activate them (if they are available) or install from
external websites.
169. What are Calculated Fields in PivotTable? What is a Column chart?
It's a numeric or date field that derives data from
calculating other fields. This data is not entered into a calculated field by
the user.
170. What is a Combination chart?
A combination chart is a form of a chart that combines two or more chart types into a single one. A popular example of a combination chart is a bar chart and the line chart. This combination chart displays the data using a number of bars and lines, each representing different categories.
171. What are Events in VBA?
An event occurs when a user performs a certain action. This triggers
Excel VBA to execute code.
172. Can you explain alignments in Excel: left, right, fill, and distributed?
Left and right alignment align the text to the left or right
edge of the cell.
Fill, fills the cell with the same text repetitively.
Distributed alignment spreads the text across the width of
the cell.
173. Are there limitations of recording a macro?
When you record a macro, you'll get a lot of unnecessary
code. There are many things you can't record and have to write code.
174. What is a named range? What are its benefits?
A named range is a cell or group of cells to which a name is
applied. The benefit of this is that named ranges are easier to find, and the
formula looks cleaner.
For example, you can use =SUM(REVENUE) instead of =SUM(A1:A10),
which will instantly tell you what the formula is about.
Read
more >>
175. What is a dashboard?
Dashboard shows which regions are performing better in sales
or which areas are lagging in terms of employee feedback. These dashboards
could be static or interactive
176. List loops you can use in VBA?
Next
While
Until
For Each Next
Read more >>
177. What are the structured references?
When you use a table name or a column name, and not a range,
you are using structured references.
178. Tell me about the benefits of using Tables?
Tables offer the following benefits:
You can use the filter icon on each column
header to sort and filter data.
You can easily apply styles.
You can use Table and Column names, instead of
cell references.
Read more >>
179. Tell me about the benefits of using VBA?
The most popular benefit of VBA is the ability to automate
tasks and create new Excel functions. 
180. In which popular file formats you can save an Excel file?
XLS: Old Excel
format.
XLSX: Excel 2007+
format.
XLSM: Macro
enables an Excel file.
CSV: Comma
separated file.
XLSB: Binary
Excel file format.
XLAM: Excel
Add-in.
Read more >>
181. What is a Pivot Chart?
A pivot chart is especially useful when you deal with large
amounts of data. Pivot Charts come with the ability to update when the PivotTable
updates.
182. Can you give examples of different ways to run a macro?
You can run macro using: 
Button
Shape
Quick Access Toolbar
Developer Tab
VBA Editor
Keyboard Shortcut
Another macro
183. What are the limitations of VLOOKUP?
The significant limitation of VLOOKUP is that it can only
find the first matching data.
184. What are your favorite keyboard shortcuts?
There are many keyboard shortcuts in Excel. Everybody can
have different preferences.
My list:
Ctrl + Z - undo
Ctrl + S - save

Ctrl + – show formulas

Ctrl + A – select entire worksheet

Shift + Arrow – Expand selection

Ctrl + C – copy

Ctrl + V – paste

Ctrl + H – replace

185. What are User Forms?

A User Form is a custom-built dialog box where a user can enter data in an easy-to-use user form.

186. What are volatile functions?

A volatile function must be recalculated whenever calculation occurs in any cells on the worksheet.

187. What characters, instead of the equation (=), you can use to start formula.

You could use the plus (+) sign in Lotus 1-2-3, and Microsoft tolerates it to help Lotus users make a switch.

188. If there is a red triangle in the top-right corner, what does it indicate?

It means that there is a comment attached to this cell.

189. Which formula returns the length of a text string in a cell?

The LEN function.

=LEN("This is a text")

1	=LEN("This is a text")

This formula returns 14.

190. Tell me something about Pivot Cache?

Pivot Cache is an object in memory that holds a replica of the PivotTable data source. It’s automatically generated with a new PivotTable. When you make any changes inside the PivotTable, Excel uses Pivot Cache to speed up and optimize the PivotTable functioning.

191. Tell me something about the Ribbon?

The ribbon is the strip above the work area, where buttons and icons are located. The Ribbon replaces the menus, buttons, and toolbars found in the earlier versions of Excel.

192. What is a UDF in VBA?

UDF is short for Defined User Function. You can also find UDF under a different name – custom functions.

They are important if the standard Excel functions are not enough for your particular task. You can use the UDF functions in the same way as regular Excel functions.

193. What is conditional formatting?

Conditional formatting in Excel enables you to highlight cells with a particular color, depending on the cell’s value.

194. Tell me something about data validation?

If you want a user to enter data that meets specified criteria, you use data validation. For example, you can only allow entering numbers in a particular range, for example between 18 and 65. If a user enters anything else, like text, or number below or above this value, Excel will return an error and will not accept the entry.

195. What is Flash Fill?

Flash Fill automatically fills other cells when it detects a pattern. For example, with a Flash Fill you can separate first and last names from a single column and place them into two different columns.

196. Is there a difference between a function and a formula?

A function is a pre-defined calculation.

The formula is any calculation in Excel.

For example:

=A1+B1 is a formula

=SUM(A1, B1) is a formula that contains a function.

197. What is the difference between relative and absolute references?

Relative references change when a formula is copied to another cell.

Absolute references, on the other hand, remain constant no matter where they are copied.

198. What is the shortcut to AutoSum?

To quickly sum cells in a row or columns, use the following shortcut – Alt + =.

199. What is VBA?

VBA (Visual Basic for Applications) is the programming language used in Excel, Word, and other applications from Office Suite.

It’s used to write and edit custom functions, procedures, or any other code.

200. What keyboard shortcut copies the cell above?

Ctrl + D

201. What is the shortcut to insert a comment?

Ctrl + F2.

Post Views: 2,833