Business Management
Excel
4.8 Rating 42 Questions 32 mins read118 Readers
Business Management
4.8 Rating 42 Questions 32 mins read118 Readers
Charts are a visual representation of data, and they can be very helpful for understanding complex information quickly. Excel offers a wide variety of chart types, and each one is well-suited for a different type of data. For example, line charts are typically used to show trends over time, while column charts are better for comparing values across different categories. If you're not sure which chart type to use, you can always experiment with different options to see which one tells your story in the most effective way.
Whatever chart type you choose, Excel makes it easy to customize your chart to achieve the perfect look. You can change the colors, add labels, and even add special effects like shadows or reflections. With a little experimentation, you'll be able to create charts that are both informative and visually appealing.
In Excel, you can Freeze Panes to keep an area of a worksheet visible while you scroll to another area of the worksheet. For example, you might want to keep row and column labels visible as you scroll through the data in the worksheet. Or, you might want to keep a title in place as you scroll down a long list of sales data. To Freeze Panes, select the cell below and to the right of the area that you want to keep visible as you scroll. Then, on the View tab, in the Window group, click Freeze Panes.
Depending on what cell you have selected when you click Freeze Panes, different panes are frozen: if you select one cell, only that row is frozen; if you select one cell in a row but not the header cells at the top of the worksheet, only that column is frozen; if you select multiple cells including header cells (for both rows and columns), multiple rows and columns are frozen. You can also freeze just the top row or leftmost column by clicking Freeze Top Row or Freeze First Column on the View tab.
To unfreeze panes, click Unfreeze Panes on the View tab. If multiple panes are frozen and Unfreeze Panes are not available, click Freeze Panes again so that no panes are frozen before you click Unfreeze Panes. For more information about freezing panes, see Keep headings visible when scrolling in Excel.
Moreover, the keyboard shortcut for this free function is Alt+W+F. To use it, simply select the cell where you want to split the screen. Then press Alt+W+F and Excel will automatically freeze the panes for you. You can also use this shortcut to unfreeze panes that have been previously frozen. Simply press Alt+W+F again and the panes will be released. This keyboard shortcut is a quick and easy way to Freezing panes in Excel without having to use the mouse.
When working with large amounts of data in Excel, it is often necessary to reference specific cells by their address. A cell address is a column and row number where the cell is located. For example, the address of the cell in the top left corner of the spreadsheet is A1. The column number comes first, followed by the row number. The address of a cell can be entered manually by typing it into the formula bar, or it can be selected using the mouse.
When multiple cells are selected, Excel will show the range of addresses in the selection as well. In many cases, it is also possible to use relative cell references, which will adjust based on the position of the formula.
For example, if a formula in cell A1 needs to reference the cell to its right, it can use a relative reference of B1. However, if that same formula were copied to cell B1, it would automatically update to reference C1 instead. Relative references are often used when creating formulas that must be applied across a range of cells.
In Microsoft Excel, you can change the width of a column in two ways: by dragging the column boundary or by changing the width in the Column width box. To resize a column by dragging the column boundary, position your mouse over the right border of the column until you see the cursor change to a double-line with an arrow on each side. Then, click and hold down your mouse button as you drag the column boundary to the desired width.
If you want to be more precise, you can change the width in the Column width box. First, select the column or columns that you want to resize. Then, go to Home > Format > Column Width. In the Column width box, type in the desired width and press Enter. The new column width will be applied to all selected columns. Whether you resize by dragging or by entering a value, the maximum column width is 255 characters.
You can also use keyboard shortcuts to resize columns. You can change column width by pressing Alt+O, then C (for Column) and W (for Width). Press Enter after you type your values.
Expect to come across this popular question in Excel interview questions for freshers.
There are a few ways to prevent someone from copying cells from your worksheet. One way is to protect the sheet, which will prevent anyone from making any changes to the sheet. To do this, go to the Review tab and click 'Protect Sheet.' You'll then be able to set a password for the sheet.
Another way is to hide the formulas in your cells. To do this, select the cells that contain formulas and then go to the Home tab and click 'Format.' In the 'Format Cells' window, go to the 'Number' tab and select 'Custom.' Then enter a formula in the 'Type' field that will display nothing when the cell is formatted (e.g. ";;;"). Finally, you can lock cells so that they can't be edited.
For this, select the cells you want to lock and then go to the Home tab and click 'Format.' In the 'Format Cells' window, go to the 'Protection' tab and check the 'Locked' box. Once you've done this, you'll need to protect the sheet again so people can't uncheck the box and edit the cells.
Most people are familiar with the order of operations from mathematics class: PEMDAS (or sometimes MEMDAS), which stands for parentheses, exponents, multiplication and division (left to right), and addition and subtraction (left to right). This order is also followed in Microsoft Excel when performing calculations. However, there are a few exceptions to keep in mind.
So when working in Excel, it's important to be aware of the order of operations to ensure that your calculations are being performed correctly.
A staple in VLOOKUP interview questions, be prepared to answer this one.
Excel's LOOKUP function searches for a value in an array and returns a corresponding value from another array. The function can search horizontally (across columns) or vertically (down rows), but it only searches in one direction at a time. This makes it different from Excel's VLOOKUP and HLOOKUP functions, which can search in multiple directions. LOOKUP is also case-sensitive, so you must ensure that the values you are searching for are entered in the correct case.
When using the function, you need to specify the array to search, the value to look for, and the array to return the corresponding value from. If more than one value is found, LOOKUP will return the first match it finds. For example, if you have a list of employee names and ID numbers, you could use LOOKUP to find an employee's ID number based on their name. The function is particularly useful when you have large lists of data and need to find corresponding values quickly.
Macros are a great way to automate repetitive tasks in Excel. A macro is a piece of code that can be executed by clicking a button or running a command. You can use macros to speed up data entry, simplify complex formulas, and perform other time-saving tasks.
To create an Excel macro, you'll need to use the Visual Basic for Applications (VBA) editor. This is a tool that allows you to write and edit code. To open the VBA editor, press Alt+F11 on your keyboard. Once the editor is open, you can start creating your macro.
When you're creating a macro, you'll need to specify what actions should be carried out. For example, you might want to insert a row or column, copy data from one sheet to another, or format a range of cells. Once you've specified the actions, you can assign a shortcut key or create a button that will run the macro.
Macros can save you a lot of time when used correctly. With just a few lines of code, you can automate tedious and time-consuming tasks.
Pivot tables are an extremely useful tool for exploring and analyzing data, but they can sometimes be frustrating when they automatically sort the data in a way that you don't want. Fortunately, it's easy to disable automatic sorting in pivot tables. Just follow these simple steps:
1.Open the pivot table in Excel.
2.Click on the "Options" tab.
3.In the "PivotTable Options" menu, select "Sort and Filter."
4.Uncheck the "AutoSort" box.
5.Click "OK" to save your changes.
Now your pivot table will no longer automatically sort itself, and you can arrange the data however you like. Keep in mind, though, that this setting is only applied to the current pivot table if you create a new pivot table, you'll need to disable automatic sorting again.
In Microsoft Excel, there are four different functions that can be used to count cells: COUNT, COUNTA, COUNTIF, and COUNTBLANK. Each of these functions has a different purpose, and as a result, they can produce different results.
The COUNT function simply counts the number of cells that contain numeric values. It does not matter if the cells contain text or formulas, the COUNT function will only count cells that have numerical values.
The COUNTA function, on the other hand, counts the number of cells that are not empty. This means that it will count cells that contain text, numbers, formulas, or anything else. The COUNTA function is often used to get an approximate count of the total number of entries in a range of cells.
The COUNTIF function is a bit more specific than the previous two functions. This function allows you to specify a criterion, and then it will only count the cells that meet that criterion. For example, you could use the COUNTIF function to count the number of cells that contain the word "apple."
Finally, the COUNTBLANK function counts the number of blank or empty cells in a range. This can be useful for determining how many cells in a range still need to be populated with data.
Data validation is a feature in Microsoft Excel that is used to control what data can be entered into a cell. This is done by setting up rules that define the type of data that can be entered, as well as the minimum and maximum values. For example, a data validation rule could be set up to only allow whole numbers between 1 and 10 to be entered into a cell.
Data validation is an important tool for ensuring accuracy in spreadsheet data. It can be used, for instance, to make sure that only valid dates are entered into cells that contain date information. Data validation can also be used to create drop-down lists in cells, which can help to limit the options for data entry and reduce mistakes.
There are four main types of text alignments in MS Excel: left, right, fill, and distributed. Left alignment is the default setting in Excel, and it simply means that the text is aligned along the left side of the cell. Right alignment is the opposite of left alignment, and it means that the text is aligned along the right side of the cell.
Fill alignment means that the text is evenly distributed across the width of the cell, while distributed alignment means that the text is evenly spaced out across the entire row (including any empty cells). Each type of alignment has its own uses, and you can experiment with different alignments to see which one works best for your needs.
This is a frequently asked advanced Excel interview questions and answers.
The AND function in Excel returns a TRUE value if all conditions are TRUE, and a FALSE value if any of the conditions are FALSE. For example, the formula =AND(1>0, 2>1) returns a TRUE value because both conditions are met. The formula =AND(1>0, 2<1) returns a FALSE value because one of the conditions is not met. The AND function can be used to test multiple conditions at the same time.
In order to do this, simply enter the desired conditions separated by commas. For example, the formula =AND(A1>0, A2>0, A3>0) will return a TRUE value if all values in cells A1, A2, and A3 are greater than 0. If any of the values are less than or equal to 0, the function will return a FALSE value. The AND function is often used in conjunction with other functions such as IF or COUNTIF in order to create more complex formulas.
The SUBSTITUTE function is used when you want to replace a specific text string with another string. For example, if you wanted to replace all instances of the word "dog" with the word "cat", you would use the SUBSTITUTE function. The REPLACE function, on the other hand, is used when you want to replace a specific character or set of characters at a specific location in a text string.
For example, if you wanted to replace the third character in the string "1A2B3C" with the letter "X", you would use the REPLACE function. In summary, the SUBSTITUTE function is used to replace a specific text string with another text string, while the REPLACE function is used to replace a specific character or set of characters at a specific location in a text string.
There are two ways to pass arguments to a VBA function. The first is by using the Call statement. For example, if you have a function named "MyFunction" that takes two arguments, you would use the following syntax: Call MyFunction(argument1, argument2). The second way to pass arguments to a function is by using the Application.Run method. This method takes a variable number of arguments, which are passed to the function as an array.
For example, the following code would call MyFunction with three arguments: Application.Run "MyFunction", argument1, argument2, argument3. When using the Application.Run method, you must enclose the function name in quotation marks.
