I AM THE KING It Lab
I AM THE KING It Lab
I AM THE KING It Lab
Q1. What are pivot table? How to develop it and How does it help to study the data?
Ans A pivot table is a powerful data summarization tool that can automatically sort, count, and sum up data
stored in tables and display the summarized data.
A pivot table is a summary of your data, packaged in a chart that lets you report on and explore trends based
on your information. Pivot tables are particularly useful if you have long rows or columns that hold values
you need to track the sums of and easily compare to one another.
A PivotTable is an interactive way to quickly summarize large amounts of data. You can use a PivotTable to
analyze numerical data in detail, and answer unanticipated questions about your data. A PivotTable is
especially designed for: Querying large amounts of data in many user-friendly ways.
The purpose of pivot tables is to offer user-friendly ways to quickly summarize large amounts of data. They
can be used to better understand, display, and analyze numerical data in detail — and can help identify and
answer unanticipated questions surrounding it.
Here are seven hypothetical scenarios where a pivot table could be a solution:
Ans2
Go to data => Data Tools => What-if Analysis => Goal Seek
In the Goal Seek Dialogue Box, use the following details.
Click OK. This will open the Goal Seek Status dialogue box
Once you accept the solution
1. Set up your data so that you have a formula cell and a changing cell dependent on the formula cell.
2. Go to the Data Tab > Forecast group, click the h=what if analysis button, and select Goal Seek.
3. In the Goal Seek dialog box, define the cells/values to test and click OK :
4. The Goal Seek Status dialog box will appear and let you know if a solution has been found. If it
succeeded, the value in the “Changing Cell” will be replaced with a new one. Click OK to keep the
new value or Cancel to restore the original one.
Scenarios allow workflow developers, scenario administrators, and others with the correct permissions to
preconfigure and save values for form fields that apply in specific situations.
This means you can send messages more quickly since you don't have to fill in all the form fields with the
same values every time. You can set up multiple scenarios for a single form to account for the variety of
real-world incidents.
For example, you could use the same Incident Response form as the foundation for two different scenarios:
• Scenario A: Sets the priority to Critical and targets a special Major Incident Triage team for a SEV-1
incident that's impacting customers,
• Scenario B: Sets the priority to Medium and targets the applicable service team when your
monitoring systems notice that an internal service is getting bogged down.
Ans Data validation in Excel is a technique that restricts user input in a worksheet. It is often used to limit
user entry.
The settings tab is where you enter the validation criteria. There are eight options available to validate for
user input:
• Whole Number - It allows only whole numbers. For example, you can specify that the user must enter the
number between 0 to 30.
• List - The user will have to create a drop-down list to choose from.
1. Select the cell you want to validate. Go to the Data tab > Data tools, and click on the Data Validation button.
2. On the settings tab, specify your validation criteria.
3. You can enter the input message if you want. This step is optional.
4. You can also set your custom error message. This step is optional.
5. Click OK. Now, if you try entering a value outside the specified range (10, 50), it will result in an error
Q5 How do we set and validate for data entry in cell range like whole
number,decimals, date time trace precedent,dependent cells?
Data Validation (using the data validation dialog box) that restricts data entry to a decimal number within limits?
Click the Settings tab (in the Data Validation box). In the Allow box, select 'Decimal'. In the Data box, select
'between', and set the minimum and maximum.
Q6 What is Macro and its advantages and how to create a Macro explain using
an example?
A macro is a name given to a block of C statements as a pre-processor directive. Being a pre-processor, the block of
code is communicated to the compiler before entering into the actual coding (main () function). A macro is defined
with the preprocessor directive, #define.
The advantage of using macro is the execution speed of the program fragment. When the actual code snippet is to be
used, it can be substituted by the name of the macro. The same block of statements, on the other hand, need to be
repeatedly hard coded as and when required.
The disadvantage of the macro is the size of the program. The reason is, the pre-processor will replace all the macros
in the program by its real definition prior to the compilation process of the program.
- The speed of the execution of the program is the major advantage of using a macro.
- It saves a lot of time that is spent by the compiler for invoking / calling the functions.
If you have tasks in Microsoft Excel that you do repeatedly, you can record a macro to automate those tasks. A macro
is an action or a set of actions that you can run as many times as you want. When you create a macro, you are
recording your mouse clicks and keystrokes.
ASSINGMENT-2
Ans- Create a chart (graph) that is recommended for your data, almost as fast as using the chart wizard that is no
longer available.
Create a chart
If your chart is on a separate worksheet, dragging might not be the best way to add a new data series. In that
case, you can enter the new data for the chart in the Select Data Source dialog box.
• On the worksheet that contains your chart data, in the cells directly next to or below your existing
source data for the chart, enter the new data series you want to add.
• Click the worksheet that contains your chart.
• Right-click the chart, and then choose Select Data.
• The Select Data Source dialog box appears on the worksheet that contains the source data for the
chart.
• Leaving the dialog box open, click in the worksheet, and then click and drag to select all the data you
want to use for the chart, including the new data series.
• The new data series appears under Legend Entries (Series) in the Select Data Source dialog box.
• Click OK to close the dialog box and to return to the chart sheet.
To remove a chart's data series, click “Chart Filters” and then click “Select Data.” Select the series in
the Legend Entries (Series) box, and then select “Remove.” Click “OK” to update the chart.
Excel includes an impressive graphing capability that can turn the dullest data into outstanding charts,
complete with all sorts of whiz-bang do-dads to amaze your friends and confound your enemies. While
Excel can automatically handle many of the mundane tasks associated with turning raw data into a chart,
you may still want to change some elements of your chart.
For instance, you may want to change the scale Excel uses along an axis of your chart. (The scale
automatically chosen by Excel may not represent the entire universe of possibilities you want conveyed in
your chart.) You can change the scale used by Excel by following these steps:
1. Right-click on the axis whose scale you want to change. Excel displays a Context menu for the axis.
2. Choose Format Axis from the Context menu. (If there is no Format Axis choice, then you did not
right-click on an axis in step 1.) Excel displays the Format Axis dialog box.
3. Make sure the Scale tab is selected. (See Figure 1.
Ans- If your chart values consist of large numbers, you can make the axis text shorter and more
readable by changing the display unit of the axis. For example, if the chart values range from
1,000,000 to 50,000,000, you can display the numbers as 1 to 50 on the axis and show a label
that indicates that the units express millions.
Q5- How to format column, bar, Pi, Slices, plot area chart are to display an image
After you've created a chart in Excel, there are several ways to format the chart to make it more readable
or stand out more.