Excel Tutorial
Using VLOOKUP() function (step by step tutorial)
How to use VLOOKUP() function in Excel |
| using VLOOKUP function |
Showing posts with label Step by Step Tutorial. Show all posts
Showing posts with label Step by Step Tutorial. Show all posts
Calculate growth formula in Pivot Table
Excel Tutorial
Calculate growth formula in pivot table
How to calculate growth formula in PivotTable
Close All file on a single click in Excel 2013 and 2016
Excel Tutorial
Close All file on a single click in Excel 2016
In Excel 2013 & 2016 All the excel files open in separate window so it's not possible to close all the opened file at once. This is by default, there is no option to close all excel workbook/files at once in version 2013 & 2016.Previous version of Excel workbook/file opened in one window and we can easily have closed all the files just using the Alt+F4. Excel 2013 each excel file open on separate window and continue in 2016 version.
For example, if we have 10 files open we must close all the files separately or hit the Alt+F4 command 10 times.
How to Enable PowerPivot Tab in Excel 2013
Excel Tutorial
How to Enable PowerPivot Tab in Excel 2013
In defult PowerPivot tab is not enable.
Add a Total row in Excel Table
Excel Tutorial
Add a Total row in Excel Table
In default Excel Table Row total is not visible.
Create a table in Excel
Excel Tutorial
Create a table in Excel worksheet
Tables make it easy to sort, filter, and format data within a sheet. Also you can organize, manage and analyze related data, table also included the built-in filtering, sorting, and row shading.
Create a Forecast Chart in Excel 2016
If you have time based on the historical data it can be used
to create a forecast. When a forecast is created, a new worksheet is created
with a table of historical and projected values and a graph showing this. A
forecast can help predict things like future sales, inventory requirements, or
consumer trends
Using of SUMIFS function in Excel
SUMIFS function
Adds the cells in a range that meet multiple criteria. For
example, if you want to sum the numbers in the range A1:A9 only if the
corresponding numbers in B1:B9 are greater than zero (0) and the corresponding
numbers in C1:C9 are less than 10, you can use the following formula:
=SUMIFS(A1:A9,
B1:B9,"=A*", C1:C9,1)
Find last date of the month in Excel using EOMONTH function
EOMONTH function is to calculate maturity dates or due dates
that fall on the last day of the month. It
used to return the last day of the month depending on the start date specified.
EMONTH()
Return the serial number of the
last day of the month before or after a specified number of months
Syntax:
Note:
If start_date is not a valid date, EOMONTH returns the #NUM! Error
value.
If start_date plus months yields an invalid date, EOMONTH
returns the #NUM! Error value
Let’s do some task
Date of the last day of the current month, next month last date
and previous month last date:
Click the current month last date and type the formula =EMONTH(D5,0).
D5 is the current month date and 0 is the number of the month or current month
+/-.
Click the Next month last date and type the formula =EMONTH(D5,1).
D5 is the current month date and 1 is the number of the month or next month. (Current
month plus one month).
Click the Previous month last date and type the formula =EMONTH(D5,-1).
D5 is the current month date and -1 is the number of the month or last month (Current
month less one month).
For formatting the number convert date (Shortcut Key Ctrl+Shift+$),
Select the cell to display the
number as a date, and then on the Home tab, in the Number group, click the
arrow next to the Number format and Short Date or click a Long date.
How to handle Excel Formula errors
IFERROR function is the best solution to handle this kind or formula
errors
Description:
Returns
a value you specify if a formula evaluates to an error; otherwise, returns the
result of the formula. Use the IFERROR function to trap and handle errors in a
formula.
Change Negative Numbers to Positive in Excel
When you need to change
the negative numbers to positive or vice versa numbers.
This Quick tricks you
can easily change all the negative numbers to positive numbers.
Fill flash is a new feature in Excel 2013
You
have the information in Excel is not formatted the way it needs to be, and
going through the whole list manually to correct is daunting. Sometimes it is
necessary to introduce a lot of repetitive information in Excel, such as dates,
and can be very tedious. But the AutoFill feature can help. AutoFill and Flash Fill
both are huge time savers.
Automatically update ranking list in Excel
Auto update the ranking list in Microsoft Excel using of
INDEX(), MATCH() and LARGE() function
Print Excel Sheet with comments box
How to print excel sheet with comments box
Ans: In Microsoft
Excel comments box not print as default.
Here is the easy step by step tutorial
with snap shots
Here is the sheet you want to print the data with comments
box
First step select the data with the comments.
Using LARGE Funtion in Excel
Returns the k-th largest value in a data set. You can use this function to select a value based on its relative standing. For example, you can use LARGE to return the highest, runner-up, or third-place score.
=LARGE(array, k)
The LARGE function syntax has the following arguments:
Array Required. The array or range of data for which you want to determine the k-th largest value.
K Required. The position (from the largest) in the array or cell range of data to return.
Note:
=LARGE(array, k)
The LARGE function syntax has the following arguments:
Array Required. The array or range of data for which you want to determine the k-th largest value.
K Required. The position (from the largest) in the array or cell range of data to return.
Note:
- If array is empty, LARGE returns the #NUM! error value.,
- If k ≤ 0 or if k is greater than the number of data points, LARGE returns the #NUM! error value.
For example we make a number list that contain 1 to 5 in large function find the largest value after the array or selection you must be define the largest if we type 1 the result appear the first largest data i.e., 5, if we type 2 the result is 2nd largest number i.e, 4.
Clean excess cell formatting on a worksheet
Formatting cells in your spreadsheet can make the right information easy to see at a glance, but the cells that are not used (rows and columns particular integer) formatting can cause the size of your file workbook to grow rapidly. This can slow down not only Excel, but the overall performance of your computer when you have a spreadsheet formatted too open. Excessive formatting can also cause printing problems in Excel.
How to enable INQUIRE Tab in Ribbon on Excel 2013
How to Show / Add Developer tab in Excel 2013
The Developer tab is not displayed by default, but you
can add it to the ribbon
How to link update automatically in Excel 2013 without any notification when file open
Often when we link one workbook with another workbook and
when we close and re-open the linked workbook a message appear on screen (see
below snapshot)
Subscribe to:
Posts (Atom)
LinkWithin
