1.

How to Create a Pivot Table

Select the cells you want to create a PivotTable from. Select
Insert > PivotTable. Under Choose the data that you want to
analyze, select Select a table or range. In Table/Range, verify
the cell range.

Pivot tables are one of Excel's most powerful features. A pivot table allows you to extract the
significance from a large, detailed data set.
Our data set consists of 213 records and 6 fields. Order ID, Product, Category, Amount, Date
and Country.

One possible way to sum this long list of numbers by one or several conditions is to use
formulas as demonstrated in SUMIF and SUMIFS tutorials. However, if you want to
compare several facts about each figure, using a Pivot Table is a far more efficient way.
In just a few mouse clicks, you can get a resilient and easily customizable summary
table that totals the numbers by any field you want.

The screenshots above demonstrate just a few of many possible layouts. And the steps
below show how you can quickly create your own Pivot Table in all versions of Excel.

How to make a Pivot Table in Excel

Many people think that creating a Pivot Table is burdensome and time-consuming. But
this is not true! Microsoft has been refining the technology for many years, and in the
modern versions of Excel, the summary reports are user-friendly are incredibly fast. In
fact, you can build your own summary table in just a couple of minutes. And here's how:

2..How to use Goal Seek Function in Excel

On the Data tab, in the Data Tools group, click What-If
Analysis, and then click Goal Seek. In the Set cell box, enter
the reference for the cell that contains the formula that you
want to resolve. In the example, this reference is cell B4. In
the To value box, type the formula result that you want.
g.

What is the Goal Seek Feature?

Goal Seek is a powerful built-in function in Microsoft Excel that helps you
determine the input value required to get the desired output result. It works by
varying an input value until the desired result is achieved.
Goal Seek helps you find the input value needed to achieve a specific goal. It
is beneficial for financial planning, budgeting, and forecasting.
Where Can I Find Goal Seek in Excel?
To locate Goal Seek in Excel, follow these steps -
 Open Excel and navigate to the “Data” tab.
 Look for the “Forecast” or “What If Analysis” group.
 Click the “What If Analysis” button.
 Select “Goal Seek” from the drop-down menu.
A dialogue box will appear, allowing you to enter the desired target, target cell,
and changing cell. Using Goal Seek in Excel lets you solve complex problems
and optimise your data analysis effectively.
Example: Step-by-Step Instructions for Using Excel
Goal Seek
In a few simple steps, here’s how to use Excel’s Goal Lookup feature:
Dataset: Let's use a simple sales data example. Imagine you have a table with
the following columns:
 Price: Selling price per unit
 Quantity Sold: Number of units sold
 Total Revenue: Calculated as Price x Quantity Sold
Goal Seek Scenario: You want to find out how many units of Product A
you need to sell to achieve a Total Revenue of $2500.

Step 1: Go to the Data tab in the ribbon. Click What-If Analysis and choose
Goal Seek.
Step 2: Set the goal:
 Set cell: Enter the cell reference for Total Revenue (e.g., B3).
 To value: Enter the desired value, which is $2500 (Do not mention the $
sign in the formula)
Step 3: Enter the cell reference for Quantity Sold for Product A (e.g., B1).
Step 4: Click OK. Excel will start iterating and adjusting the value in the
Quantity Sold cell until the Total Revenue reaches the goal of $2500.

Step 5: The new value in the Quantity Sold cell (e.g., B1) will show you how
many units you need to sell to achieve your goal. Check if this solution is
feasible and adjust your goal or other parameters as needed.

Tips for using Excel Goal Seek

Here are some other tips to help you get the most out of Excel’s Goal Seek
feature :
 Use Goal Seek and other Excel tools like data tables and the scenario
manager to create more complex models.
 Be careful when using Goal Seek with formulas that have circular
references, as this can cause errors in your spreadsheet.
 Consider using Goal Seek for sensitivity analysis, which involves testing
different input values to see how they affect the output value.
 Always check the results to make sure they make sense and are accurate.
Conclusion
Goal Seek in Excel is a powerful feature that can save you time and effort to
achieve a specific result in your spreadsheets. You can start using Goal Seek
to improve your financial planning, budgeting and forecasting by following the
simple steps suggested in this article.

3.How To Set, Edit Validate Criteria for Data Entry

in a Cell Range Like: Whole Number, Decimal, List

Select Data >Data Validation. On the Settings tab, under

Allow, select an option: Whole Number - to restrict the cell to
accept only whole numbers. Decimal - to restrict the cell to
accept only decimal numbers.

Home > Validation > Rules > Criteria

Data Validation Criteria Examples

Use Data Validation to allow specific entries in cells on a

worksheet. For example, show a list, limit the cells to whole
numbers, or text of specific length. This tutorial shows the
available settings, with an example of each setting.

Whole Number

Decimal

List

Date

Time
Text Length

Get the Sample File

More Data Validation Tutorials

Whole Number

If you allow Whole numbers, you can set or exclude a range of

numbers, or specify a minimum number or maximum
number.

To set the allowed values, you can

1. Type the values into the Data Validation dialog box
2. OR, Refer to cells on the worksheet
3. OR, Use formulas to set the values.
For example, in this example, the MAX and MIN functions set
the minimum and maximum values, based on values in cells
J5:J9.
 Minimum: =MIN(J5:J9)
 Maximum: =MAX(J5:J9)
Decimal
 If you allow Decimals, you can set or exclude a range of
numbers, or specify a minimum number or maximum
number.

.4. How to Create a Column Chart

To create a column chart:
1. Enter data in a spreadsheet.
2. Select the data.
3. On the Insert tab, select. Insert Column or Bar Chart and choose a column
chart option. You can optionally format the chart further: Note: Be sure to
select the chart first before applying a formatting option.

The steps below create a basic column chart. This is a plain,

unformatted chart that displays your data, a basic legend,
and a default chart title.
1. Highlight the range of cells that contain your data.
2. Select Insert.
3. In the Charts group, select the Insert Column or Bar
Chart to open a list of available chart types.
4. Hover over a chart type to read a description of the chart and
see a preview of how the chart will look with your data.
5. In the 2-D Column section of the list, choose Clustered
Column to add this basic chart to the worksheet.
5 How to Create a Bar Chart
Add a bar chart right on a form.

1. In the ribbon, select Create > Form Design.

2. Select Insert Modern Chart > Bar > Clustered Bar.
3. Click on the Form Design grid in the location where you want to
place the chart. Resize the chart for better readability.
4. In the Chart Settings pane, select Queries, and then select the
query you want.

In the example, select QuarterlyExpensesQry.

5. To configure the chart, select options under the following sections.
In the example, set:
 Axis (Category) to "Quarter".

 Values (Y axis) to "Expenses (Sum)".

 Values (Y axis) to "Actual (Sum)".

 Values (Y axis) to "Projected (Sum)".

6. Select Format > SumofProjected > Display Data Label.

7. To add a data label to a bar, select the Format tab in the Chart
Settings pane, select "SumofProjected" under Data Series, and
then select Display Data Label.
8. To format the numbers, Press F4 to open the Property Sheet, and
then set:
 Primary Values Axis Format to "Currency".

 Primary Values Axis Font Color to "White".

 Primary Values Axis Display Units to "Thousands".

9. To see the completed chart, right-click on the Form tab and select
or Form View.
6 How to Create a Line Chart.

Select Insert Chart > Line > Line. Click on the Form
Design grid in the location where you want to place
the chart. Resize the chart for better readability. In
the Chart Settings pane, select Queries, and then
select the query you want.

Excel line chart (graph)

A line graph (aka line chart) is a visual that displays a series of data points connected
by a straight line. It is commonly used to visually represent quantitative data over a
certain time period.

Typically, independent values such as time intervals are plotted on the horizontal x-axis
while dependent values such as prices, sales and the like go to the vertical y-axis.
Negative values, if any, are plotted below the x-axis.
The line's falls and rises across the graph reveal trends in your dataset: an upward
slope shows an increase in values and a downward slope indicates a decrease.

7 How to Create a Pie Chart.

4. Click on the pie to select the whole pie. Click on a slice to drag it away from the center.

Result:
Note: only if you have numeric labels, empty cell A1 before you create the pie chart. By
doing this, Excel does not recognize the numbers in column A as a data series and
automatically creates the correct chart. After creating the chart, you can enter the text Year
into cell A1 if you like.
Let's create one more cool pie chart.

5. Select the range A1:D1, hold down CTRL and select the range A3:D3.
6. Create the pie chart (repeat steps 2-3).

7. Click the legend at the bottom and press Delete.

8. Select the pie chart.

9. Click the + button on the right side of the chart and click the check box next to Data
Labels.
10. Click the paintbrush icon on the right side of the chart and change the color scheme of
the pie chart.
8 How to Create a Combined chart like: Column
and Line Column and Area
The steps to create a Combo Chart are as follows: Step 1:
First, we must select the data table prepared, then go to the
“Insert” tab in the ribbon, click on “Combo,” and then select
the “Clustered Column – Line.” Once the clustered chart is
selected, the combo chart will be ready for display and
illustration
Bar charts in think-cell are simply rotated column charts, and can be
used exactly as column charts. In addition, you can create butterfly
charts by placing two bar charts “back-to-back”. To do so, apply the
functions rotation (see 3.3 Rotating and flipping elements) and same
scale (see 8.1.4 Same scale). Then remove the category labels for one
of the charts.

For the steps to create a stacked clustered chart, see 7.2 Clustered
chart.
To change the column width, select a segment and drag one of the
handles at half the height of the column.

9 How to Display an image in Chart Area.

Click the chart area of the chart. On the Insert tab, in the
Illustrations group, click Pictures. Locate the picture that you
want to insert, and then double-click it. Tip: To add multiple
pictures, hold down CTRL while you click the pictures that you
want to insert, and then click Insert.
 10. How to perform following function in Excel: SUBSTRACT

 MULTIPLY

 DIVIDE

 SQUARE ROOT

 MIN

 & MAX

For simple formulas, simply type the equal sign followed by

the numeric values that you want to calculate and the math
operators that you want to use — the plus sign (+) to add, the
minus sign (-) to subtract, the asterisk (*) to multiply, and the
forward slash (/) to divide.

Microsoft Excel formulas - the basics

In MS Excel, formulas are equations that perform various calculations in your
worksheets. Though Microsoft has introduced a handful of new functions over the years,
the concept of Excel spreadsheet formulas is the same in all versions of Excel 2016,
Excel 2013, Excel 2010, Excel 2007 and lower.

 All Excel formulas begin with an equal sign (=).

 After the equal symbol, you enter either a calculation or function. For example, to add up
values in cells B1 through B5, you can either:
o Type the entire equation: =B1+B2+B3+B4+B5
o Use the SUM function: =SUM(B1:B5)
 Press the Enter key to complete the formula. Done!
 Elements of Microsoft Excel formulas

When you make a formula in Excel, you can use different elements to supply the source
data to the formula and indicate what operators should be performed on those data.
Depending on the formula type that you create, it can include any or all of the following
parts:

 Constants - numbers or text values that you enter directly in a formula, like =2*3.
 Cell references - reference to a cell containing the value you want to use in your Excel formula,
e.g.

=SUM(A1, A2, B5).

To refer to data in two or more contiguous cells, use a range reference like A1:A5. For
example, to sum values in all cell between A1 and A5, inclusive, use this formula:

=SUM(A1:A5).

 Names - defined name for a cell range, constant, table, or function, for
example =SUM(my_name).
 Functions - predefined formulas in Excel that perform calculations using the values supplied in
their arguments.
 Operators - special symbols that specify the type of operation or calculation to be performed.
 Operators in Excel worksheet formulas
To tell Microsoft Excel what type of operation you want to perform in a formula, you use
special symbols that are technically called operators. There exist 4 types of operators
in Excel:

 Arithmetic - to perform basic mathematical operations.

 Comparison (logical) - to compare values.
 Concatenation - to join text values into a single string.
 Reference - to make ranges and separate arguments in Excel functions.

Using arithmetic operators in Excel formulas

These operators are used to perform basic mathematical operations such as addition,
subtraction, multiplication, and division.

Operator Meaning Formula example

+ (plus sign) Addition =A2+B2

- (minus sign) =A2-B2

Subtraction
=-A2 (changes the sign of the
Operator Meaning Formula example

Negation (reversing the sign) value in A2)

* (asterisk) Multiplication =A2*B2

/ (forward slash) Division =A2/B2

=A2*10%
% (percent sign) Percentage
(returns 10% of the value in A2)

=A2^3
^ (caret) Exponential (power of) (raises the number in A2 to the
power of 3)

For example, if you have an item price in cell A2 and VAT in cell B2, you can calculate
the VAT amount by using the following percentage formula: =A2*B2

Comparison operators in Excel formulas

In Microsoft Excel formulas, comparison, or logical, operators are used to compare two
values. The result of the comparison is always a logical value of TRUE or FALSE. The
following logical operators are available in Excel:
Comparison operator Meaning Formula example

= Equal to =A2=B2

<> Not equal to =A2<>B2

> Greater than =A2>B2

< Less than =A2<B2

>= Greater than or equal to =A2>=B2

<= Less than or equal to =A2<=B2

For example, formula =A1=B1 returns TRUE if cells A1 and B1 contain the same value
(number, text or date), FALSE otherwise.

For more information and examples of using comparison operators in MS Excel

formulas, please check out the following tutorial: Excel logical operators - equal to, not
equal to, greater than, less than.

Text concatenation operator

Text concatenation operator in Excel is the ampersand symbol (&). You can use it to
join two or more text strings in a single string.

For example, if you have country codes in column A and telephone numbers in column
B, you can use the following formula to get the telephone numbers combined with the
country codes:

=A1&" "&B1
 In the above formula, we concatenate a space " " in between to make the numbers
better readable:

The same result can be achieved by using the CONCATENATE function, and the
following tutorial explains all the details: How to combine text strings, cells and columns
in Excel.

11 Creating a scenario in Excel and how can we

provide the input
Use Scenario Manager
1. Select the cells that contain values that could change.
2. Click the Data tab on the ribbon.
3. Click the What-If Analysis button.
4. Select Scenario Manager. ...
5. Click the Add button to add a new scenario.
6. Type a name for the new scenario. ...
7. Click OK. ...
8. Update any values you want to see for the given scenario.

12.How to perform edit operation in scenario.

To edit a scenario, click within the grid cells in the Edit
Member Properties grid to edit text or to view a drop-down
menu from which you can choose member properties. Go to
 step 4. Note: Each column in the grid represents a member
property.
Editing Scenarios
You edit scenarios in two places in Bentley StormCAD :

 The Scenario Manager lists all of the model’s scenarios in a hierarchical tree format, and displays the
Base/Child relationship between them.
 The Property Editor displays the alternatives that make up the scenario that is currently highlighted
in the Scenario Manager, along with the scenario label, any notes associated with the scenario, and
the calculation options profile that is used when the scenario is calculated.

To edit a scenario:

1. Select Analysis > Scenarios to open the Scenario Manager, or click the Scenario Manager tab.
2. Double-click the scenario you want to edit to display its properties in the Property Editor.
3. Edit any of the following properties as desired:

Related Topics

 Base and Child Scenarios

 Running Multiple Scenarios at Once (Batch Runs)
 Scenario Manager

Parent topic: Scenarios

13 How to change cell style in Excel.

To modify the cell style, click Format. On the various tabs in
the Format Cells dialog box, select the formatting that you
want, and then click OK. In the Style dialog box, under Style
Includes, select or clear the check boxes for any formatting
that you do or do not want to include in the cell style.
 14. How to merge cell
Select the first cell and press Shift while you select the last
cell in the range you want to merge. Important: Make sure
only one of the cells in the range has data. Select Home >
Merge & Center. If Merge & Center is dimmed, make sure
you're not editing a cell or the cells you want to merge aren't
inside a table.
Things You Should Know
 To merge two or more cells into one big cell, select the cells, then click "Merge &
Center" on the toolbar.
 Merging cells is helpful if you want one cell in your sheet to be larger, such as a cell
that contains a title.
 To merge cell values into a single cell, use the CONCAT function or the & operator.
1
Method

Merging Cells

Download Article

1
Select the cells you want to merge. To do so, click one cell, then drag your
cursor to select the other cells you want to merge. The cells you merge must
be touching each other. For example, you can merge A1 with B1 but not A1
with only F5.
  Merging cells is a great way to add formatting to an Excel spreadsheet. For
example, you could put “July Calendar” at the top of an Excel calendar in a
larger cell without changing the width of the columns below.
 Merging cells only retains the value of the leftmost cell and results in a larger
cell on the sheet. If you want to merge two or more existing cell values into a
single cell, see Merging Values.

15. How to use Format Painter

Use the Format Painter
1. Select the text or graphic that has the formatting that you want to copy.
2. On the Home tab, select. Format Painter in the Clipboard group.
3. The cursor changes to a. paintbrush icon.
4. Use the brush to paint over a selection of text or graphics to apply the
formatting. ...
5. To stop formatting, press ESC.

Format Painter
The format painter is a command which lets you copy formatting from
one cell to another.

It is a great tool, which saves you lots of time!

The Format painter can be used to copy to single cells or ranges.

Format Painter is used by clicking on its button in the Ribbon, found in

the Clipboard group.
How To Use the Format Painter
1. Select the cell that you want to copy
2. Click the Format Painter button
3. Select a cell or range

Let's try some examples and copy formats with Colors and font
Characteristics, such as bold, italic and underline:

Copy the red color from A1 to B1.

Step by step:

1. Select A1
2. Click the Format Painter button
3. Select B1
Notice the dotted border around A1. This indicates that the format is
copied and ready for pasting. Let's paste it to B1!

Great job!

Copy and Paste Color to Multiple Cells

Copy B2 to C2. That is right, let's try to paste to a range of two cells!
1. Select A2
2. Click the Format Painter button
3. Select B2 and hold an drag to C2