Microsoft Excel is said to be a spreadsheet application or an electronic worksheet that is helpful for storing, analyzing data, manipulating data, and organizing reports.
Accounting, Date, Percentage, Number, and Text are the different data formats available in Excel.
If you want to copy the format of a cell, text, image etc and apply on another text, the Format painter is used.
The place where we store the data is called a cell.
Comments in Excel are used to describe a formula given in a cell and leave notes for the users for any extra/special information.
To add comments in Excel, perform the below actions: Right-click on the cell Select “Insert” from the toolbar Click “Comment”. Comment box appears. You can enter the required information here.
Pie, Bar, Scatter, Line are some of the available charts in MS Excel, which is useful to provide graphical representation of a report/analysis.
A specific area that runs at the top of the application, comprised of toolbar and menu items is called a Ribbon. There are various tabs available in ribbon containing a set of commands to use in the application.
Ctrl+F1 is the shortcut key to hide the ribbon in Excel
To protect the worksheet in Excel, navigate to Menu bar -> Review -> Protect sheet -> Password. Provide a password to protect the worksheet and avoid copying the data.
To get the total of columns and rows in Excel, use the function ‘SUM’.
Report, Compact and Tabular are the formats available in Excel.
To verify whether the conditions are true or false, the function ‘IF’ is used in Excel.
To return a value for array, you can use the function Look Up
To delete the blank columns in Excel, press Ctrl+-.
There are 1048576 rows and 16384 columns in Microsoft Excel 2013.
The syntax for VLookUp is given below: VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])
The different errors displayed in Excel are #REF!, #DIV/0!, #NUM, #N/A, #NAME, and #VALUE!.
PEMDAS is the acronym given for the order of operations in Excel. P – Parenthesis/ Brackets E – Exponentiation (^) M – Multiplication D – Division A – Addition S – Subtraction
The major functions performed in Excel are SUMIF, INDEX/MATCH, VLOOKUP, IFERROR and COUNTIF.
Use the function ‘LEN’ to find the text string length.
When there is a modification performed in the worksheet, make use of volatile function to recalculate the formula repeatedly.
TODAY(), NOW(), and RAND() are the highly volatile formulas. INDIRECT(), OFFSET(), INFO(), and CELL() are the other volatile formulas.
Ctrl+F is the shortcut key to open the find tab and Ctrl+H is the shortcut to open find and replace tab.
To open a spell-check dialog box, the shortcut key is F7.
ALT=’ is the shortcut to perform auto-sum on the rows and columns.
Ctrl+N is the shortcut to open a new Excel workbook.
Filter Area, Columns Area, Values Area, and Rows Area are the sections available in Pivot Table.
The 2010 version Excel has the feature called Slicer in Pivot Table. With the help of Slicer in Pivot table, users can filter the data while selecting one or more options in slicer box.
Stephen Few is a dashboard expert who designed Bullet Charts and this chart has been extensively acknowledged as one of the topmost graphical representation to show the performance report.
Date filter, Text Filter and Number Filter are the different types of data filter available in Excel.
Using Transpose function and Paste Special Dialog Box are the two (2) methods to transpose a data set in Excel.
There is an in-built feature in Excel to remove duplicates from a data set. Steps to remove duplicates is given below: Select Data -> Select ‘Data’ tab -> Click ‘Remove Duplicates’.
Visual Basic Applications (VBA) and XLM are the two (2) macro languages available in MS Excel.
Use the event ‘PivotTableUpdate’ to check the status of a Pivot Table modification in a worksheet.
yntax of SUBSTITUTE function in Excel: ‘SUBSTITUTE(text, oldText, newText, [instanceNumber])’
Syntax of REPLACE function in Excel: REPLACE(oldText, startNumber, NumberCharacters, newText)
The keys Ctrl + PgUp is used to move to the previous worksheet in Excel
The keys Ctrl + PgDown is used to move to the previous worksheet in Excel
Advanced Criteria Filter is used to analyse the list employed with database function.
The keys ‘Ctrl+F9’ is the shortcut key to minimize the workbook.
‘Esc’ key is used to cancel the entry in Excel.
Yes, we can easily change the font and color of the sheet tabs in Excel.
The key elements such as Minimum distractions, visual presentation of information, easy to communicate, and provide useful data to the business stands out to be the best dashboard.
Slicers, Tables, IFERROR, Powerpivot, and Sparklines are the new enhancements available in Excel latest version.
Yes, it is possible to close all the open excel files at a time.
We give a name for a cell or a Range which is called Name Manager. Using the Name manager, Table gets managed.
The symbol ‘$’ is used to lock or fix the reference.
If you want to lock a specific column or row, Freeze panes can be used.
Yes, we have a unique address for each cell based on the value of the row and column.
MS Excel is a spreadsheet developed by Microsoft for macOS, Windows, IOS and Android. This spreadsheet allows users to organize, store, and modify the data by applying various formulas. It gives a clear view of reports by dividing into columns and rows. Excel is open to integrate with any external databases to conduct analysis and to generate reports and so on.
The ribbon is the top place of the excel application, in which you can see the menu icons and toolbars available in Excel application. You can use the CTRL+F1 to show or hide the ribbon. The ribbon contains various tabs, and every tab contains different commands.
We have Eleven data types available in Excel for storing data. Let us list a few of them. Currency: records data in currency form Name: data related to numbers are stored percentage: records numbers as a percentage Date: stores data as date Text: It records data in the form of strings
The order of operations in excel same as like standard mathematics. This is defined by “BEDMAS” or “PEMDAS” Brackets or Parentheses Addition Division Exponent Multiplication Subtraction
If you are required to perform tasks repeatedly in Microsoft Excel, you have a facility here to automate the tasks using Maco. A macro is defined as a set of actions that you can run based on the repetitions. When you create a macro, it automatically records your keystrokes and mouse clicks.
Initially, you need to do is a selection of the text cell that we want to wrap and next click on the wrap text tab in the Home tab. The text is wrapped into the cell.
Yes, we can prevent others from copying the cells. To do so what you need to do is you need to click on the menu bar >Review > Protect Sheet > Password. Creating a password for your sheet can prevent others from creating a copy of your sheet.
We have two languages in MS-excel those are VBA (Visual Basic Applications) and XLM. In the beginning, XLM was in usage, but after the introduction of Excel 5 version, VBM has been in usage.
It is very simple: you can use simply get the sum of the rows and columns in the excel sheet by using the sum function.
Yes, we do have various charts in Ms-Excel which include a bar, columns, pie scatter etc. We can select the different charts from the insert tabs from the charts group. Charts are useful for creating a graphical representation of excel data.
The triangle indicates that there is some comments associated with that particular cell. You can view or read the comment by placing the mouse on it.
Below is the reasons why comments are used. Comments are mainly used to specify the need of the cells. Comments are mainly used to specify a formula used in a cell. Comments help to write notes about a cell. To give a comment in an excel sheet, you need to right-click on the cell menu and select cell menu. And then write your comments.
The Name box plays a vital role in finding the required cell or range name. To find any cell address or name, enter the elements in the name box.
To add a new excel sheet, you need to insert a worksheet tab at the end of the excel sheet.
Below is the functions available to modify the data in excel: Logical Functions – IF, AND, TRUE, FALSE Math and Financial Functions – DEGREE, SQRT, GCD, RAND() Index Match – INDEX MATCH and VLOOKUP Date and Time Functions – DATEVALUE (), NOW(), WEEKDAY(NOW()) Pivot tables
Yes, we can merge cells in MS-Excel, to do so, first of all, you need to select the cells that you wish to merge then in-home tab click on the ‘Merge and Centre’ option from the Alignment group.
To resize a column, the first and foremost thing that you need to do is change the width of one column, and the next step is to drag the boundary to the right side until the size you want have. And you can have another manner to resize the column, i.e., select Format option from the home tab in that you need to click on AUTOFIT COLUMN WIDTH. Once you click on it, the selected cell will be formatted.
We have three types of reports which are: Report Tabular Compact
Format painter in excel tool helps you out in copying the format from one item to another one. For example, you have written something in word, and you have formatted according to the style you want using a specific font, color, type. Using format painter you can copy the same format to another section.
Conditional formatting is an essential feature in Microsoft excel using which you can format to a cell or by selecting the various range of cells based on predefined conditions. Let’s take an example here: You wish to highlight the cells whose value is less than 20 with red colour, then you can do that using conditional formatting.