Excel

Excel Data Tools Tutorial

Data validation
Validating existing data
Remove duplicates
Subtotal
Text to columns
Consolidate multiple data sources
Retrieve geographic data automatically
Retrieve stock price data automatically
Automatic calculation
Format as table
Conditional formatting
Clear format/content

Solver

Excel Functions Tutorial

Top 10 commonly used functions

CHOOSE
DATE
DAYS
FIND
INDEX
IF
IFS
LOOKUP
MATCH
VLOOKUP

Financial functions

NPV (and XNPV)

Other functions

ABS
ACOS
CONVERT
SUM

Excel chart

Common chart

Funnel chart
Line chart
Pie chart
Radar chart
Scatter with only markers
Stacked area chart
Stock candlestick chart
Waterfall chart

Hierarchical chart

Treemap chart
Sunburst Chart

Statistical chart

Box and whisker chart
Histogram
Pareto chart

Examples

How to generate Fibonacci numbers in Excel
How to convert Fahrenheit to Celsius
How to convert between mile, kilometer and meter
How to count the number of times data sign changes (from + to -, or vice versa) in a column?

Comments

Post a Comment

Popular posts from this blog

Excel - Solver examples

Image
Excel Solver is used to solve optimization problems, which is like solving equations. In this tutorial, we will give a very simple example on how to use Excel Solver. Basic example Suppose your car is low on gasoline. The gasoline is $3/gallon. You have a budget of $50. How much gasoline can you buy? Of course, for a problem this simple, we do not even need a Solver to solve the problem. However, the purpose is to teach the users how to use Excel Solver, so we make the problem extremely simple. We have the following framework: Goal: maximize quantity of gasoline Relationship: cost=quantity * unit price Constraint: cost<=budget 1. In cell B1, we enter unit price "3", in cell B4, we enter the budget 50. In cell B2, we just enter an arbitrary number as the number of gallons. We entered 5. In cell B3, we enter the formula "=B1*B2" to let it be the cost, which equals to 15. 2. Click Solver under the Data menu. 3. A Solver dialog window pops up. In "Set objective...
Read more

Excel - Pivot Table

Image
Pivot table is a powerful tool to summarize data and examine data patterns in Excel. Suppose we have 200 rows of data from a farmer, which sells fruit (apple, banana, orange) and vegetable (carrot, spinach) to clients in the US or Canada market, like below: 1. Select Insert > PivotTable 2. Click on PivotTable, then select the data range and where to place the PivotTable. When selecting the data range, the header must be selected. 3. Drag "Category" to Row labels, "Item" to Column labels, "Amount" to Values, and at last, drag "Market" to Report Filter. Note: You can user different fields for filter, row label or column label, depending on your business logic. You do not have to fill all the 4 fields, even only dragging the "Amount" into the "Values" field still produces a nice PivotTable. Again, all depend on your business logic. 4. The result is below. Notice that some cells are blank in the PivotTable. The reason is obvio...
Read more

Excel - VLOOKUP function

Image
This post is written by Yisong Geng, PhD at Calc LLC. If you need consulting services with Excel, please  contact us . VLOOKUP is one of the most commonly used Excel functions. VLOOKUP function asks for 4 things: The value you want to look up, such as name of a person The data range you want to search If the value is found in the data range, which column contains the value you want the function to return? Whether it is a approximate match or not. The default option is approximate match. This argument is optional and let's ignore it for now. The following table contains first name, last name and age of 4 people. Your task is to find the age of Jan Doe. It can be easily done using VLOOKUP function. The value you want to look up is "Jane", which is at cell "A3" The range of data you want to search, which is "A2:C5" Which column contains the value you want to retrieve? The "Age" is column C, so this argument is 3. The 4th argument is optional and...
Read more