Step 2: Drag it to the size you want. Add a Field to a Data Label Excel has a powerful feature of adding a cell reference with explanatory text or a calculated value to a data label.
Let us see how to add a field to the data label. Step 2: Right-click on a data label. A list of options will appear. A Data Label Reference window appears. The explanatory text appears in the data label.
Step 6: Resize the data label to view the entire text. It is helpful when you have placed a data label away from a data point. In earlier versions of Excel, only the pie charts had this functionality. Now, all the chart types with data label have this feature. Add a Leader Line Step 1: Click on the data label.
Step 2: Drag it after you see the four-headed arrow. Step 3: Move the data label. The Leader Line automatically adjusts and follows it. Step 2: Click on Format Leader Lines. The Format Leader Lines task pane appears. Now you can format the leader lines as you require. Step 5: Make the changes that you want.
The leader lines will be formatted as per your choices. Excel — New Functions Advanced Excel Several new functions are added in the math and trigonometry, statistical, engineering, date and time, lookup and reference, logical, and text function categories. Also, Web category is introduced with few Web service functions. Functions by Category Excel functions are categorized by their functionality.
If you know the category of the function that you are looking for, you can click that category. The Function Library group appears. The group contains the function categories.
Step 2: Click on More Functions. Some more function categories will be displayed. Step 3: Click on a function category. All the functions in that category will be displayed. As you scroll on the functions, the syntax of the function and the use of the function will be displayed as shown in the image given below.
MATH: Rounds a number up, to the nearest integer or to the nearest multiple of significance. MATH: Rounds a number down, to the nearest integer or to the nearest multiple of significance.
P: Returns the skewness of a distribution based on a population: a characterization of the degree of asymmetry of a distribution around its mean. These add-in or automation functions will be available in the User Defined category in the Insert Function dialog box.
REQUEST: Connects with an external data source and runs a query from a worksheet, then returns the result as an array without the need for macro programming. Web Functions The following web functions are introduced in Excel Further, different analysis features are readily available. This is through the Quick Analysis tool. Quick Analysis Features Excel provides the following analysis features for instant data analysis. Formatting Formatting allows you to highlight the parts of your data by adding things like data bars and colors.
This lets you quickly see high and low values, among other things. Charts Charts are used to depict the data pictorially. There are several types of charts to suit different types of data. Totals Totals can be used to calculate the numbers in columns and rows. You have functions such as Sum, Average, Count, etc. Tables Tables help you to filter, sort and summarize your data. The Table and PivotTable are a couple of examples. Sparklines Sparklines are like tiny charts that you can show alongside your data in the cells.
They provide a quick way to see the trends. Quick Analysis of Data Follow the steps given below for quickly analyzing the data. Step 1: Select the cells that contain the data you want to analyze. Conditional Formatting Conditional formatting uses the rules to highlight the data.
This option is available on the Home tab also, but with quick analysis it is handy and quick to use. Also, you can have a preview of the data by applying different options, before selecting the one you want. Step 2: Click on Data Bars. Step 3: Click on Color Scale. The cells will be colored to the relative values as per the data they contain. Step 4: Click on the Icon Set. The icons assigned to the cell values will be displayed.
Values greater than a value set by Excel will be colored. You can set your own value in the Dialog Box that appears. Step 7: Click on Clear Formatting. Whatever formatting is applied will be cleared. You will have a preview of all the formatting for your Data. You can choose whatever best suits your data.
Recommended Charts for your data will be displayed. Step 2: Move over the charts recommended. You can see the Previews of the Charts. More Recommended Charts are displayed. The little black arrows on the right and left are to see additional options. Step 2: Click on the Sum icon. This option is used to sum the numbers in the columns.
This option is used to calculate the average of the numbers in the columns. Step 4: Click on Count. This option is used to count the number of values in the column. This option is to compute the percent of the column that represents the total sum of the data values selected. Step 6: Click on Running Total. This option displays the Running Total of each column. This option is to sum the numbers in the rows.
Step 8: Click on the symbol. This displays more options to the right. This option is to calculate the average of the numbers in the rows. Step Click on Count.
This option is to count the number of values in the rows. This option is to compute the percent of the row that represents the total sum of the data values selected. Step Click on Running Total.
This option displays the Running Total of each row. Step 2: Hover on the Table icon. A preview of the Table appears. The Table is displayed. You can sort and filter the data using this feature. Step 4: Click on the Pivot Table to create a pivot table. Pivot Table helps you to summarize your data. They provide a quick way to show the trends of your data. The chart options displayed are based on the data and may vary.
Step 2: Click on Line. A line chart for each row is displayed. Excel — Sorting Data by Color Advanced Excel If you have formatted a table column, manually or conditionally, with the cell color or font color, you can also sort by these colors.
The Sort dialog box appears. Step 3: Under the Column option, in the Sort by box, select the column that you want to sort. For example, click on Exam 2 as shown in the image given below. To sort by cell color, select Cell Color. To sort by font color, select Font Color. Step 5: Click on the option Cell Color.
Step 6: Under Order, click the arrow next to the button. The colors in that column are displayed. To move the cell color to the top or to the left, select On Top for column sorting and On Left for row sorting. To move the cell color to the bottom or to the right, select On Bottom for column sorting and On Right for row sorting. Excel — Slicers Advanced Excel Slicers were introduced in Excel to filter the data of pivot table. In Excel , you can create Slicers to filter your table data also. A Slicer is useful because it clearly indicates what data is shown in your table after you filter your data.
Step 1: Click in the Table. Step 3: Click on Insert Slicer. A Insert Slicers dialog box appears. Step 4: Check the boxes for which you want the slicers. Click on Genre. Step 5: Click OK. Slicer tools appear on the ribbon. To choose more than one item, hold down CTRL, and then pick the items you want to show. Excel — Flash Fill Advanced Excel Flash Fill helps you to separate first and last names or part names and numbers, or any other data into separate columns.
Step 1: Consider a data column containing full names. Step 2: Enter the first name in the column next to your data and press Enter. Flash Fill will show you a list of suggested names. Step 4: Press Enter to accept the list. Step 6: Start typing the next name and press Enter. The column will be filled with the relevant last names. Flash Fill works with any data you need to split into more than one column, or you can simply use it to fill out data based on an example.
Flash Fill typically starts working when it recognizes a pattern in your data. This command helps you to create PivotTables automatically. Step 1: Your data should have column headers. If you have data in the form of a table, the table should have Table Header. Make sure of the Headers. Step 2: There should not be blank rows in the Data. Make sure No Rows are blank. Step 3: Click on the Table. Step 4: Click on Insert tab. Step 5: Click on Recommended PivotTables. The Recommended PivotTables dialog box appears.
Step 6: Click on a PivotTable Layout that is recommended. A preview of that pivot table appears on the right—side. The PivotTable is created automatically for you on a new worksheet. Step 2: Click on the Insert tab. Step 3: Click on the PivotTable button. A Create PivotTable dialog box appears. Step 5: Select the Choose Connection option. A window appears showing all the Existing Connections. All the available data connections can be used to obtain the data for analysis.
The option Connections in this Workbook option in the Show Box is to reuse or share an existing connection. Connect to a new external data source You can create a new external data connection to the SQL Server and import the data into Excel as a table or PivotTable.
Step 1: Click on the Data tab. Step 4: Establish the connection in three steps given below. Enter the database server and specify how you want to log on to the server. Enter the database, table, or query that contains the data you want. Enter the connection file you want to create. Step 1: Select the data table. Step 2: Click the Insert Tab. The Create PivotTable dialog box opens. Step 4: Fill the data and then click OK. The PivotTable appears on a New Worksheet.
Step 5: Choose the PivotTable Fields from the field list. The fields are added to the default areas. The Filters area fields are shown as top-level report filters above the PivotTable. The order in which the Fields are placed in the Rows area, defines the hierarchy of the Row Fields. Depending on the hierarchy of the fields, rows will be nested inside rows that are higher in position.
This is because in the Rows area, the field Salesperson appears first and the field Month appears next, defining the hierarchy. You have changed the hierarchy, putting Month in the highest position. Now, in the PivotTable, the field - Salesperson will nest under Month fields.
In a similar way, you can drag Fields in the Columns area also. Step 1: Select the Sales sheet from the worksheet tabs. Step 2: Click the Insert tab. Step 3: Click on the PivotTable button on the ribbon.
Step 6: Click OK. Step 7: Click on ALL. You will see both the tables and the fields in both the tables. Step 8: Select the fields to add to the PivotTable. After a few steps for creation of Relationship, the selected fields from the two tables are added to the PivotTable. You can build a data model, then create amazing interactive reports using Power View. You can also make use of the Microsoft Business Intelligence features and capabilities in Excel, PivotTables, Power Pivot, and Power View Data Model is used for building a model where data from various sources can be combined by creating relationships among the data sources.
A Data Model is created automatically when you import two or more tables simultaneously from a database. The existing database relationships between those tables is used to create the Data Model in Excel. Step 1: Open a new blank Workbook in Excel. The Select Data Source dialog box opens. Step 4: Select Events. Step 6: Tables in a database are similar to the tables in Excel. Then click OK. Select the PivotTable Report option. This option imports the tables into Excel and prepares a PivotTable for analyzing the imported tables.
Step 8: The data is imported, and a PivotTable is created using the imported tables. You have imported the data into Excel and the Data Model is created automatically. Now, you can explore data in the five tables, which have relationships defined among them. Even if you are not sure of the final report that you want, you can play with the data and choose the best-suited report.
In PivotTable Fields, click on the arrow beside the table- Medals to expand it to show the fields in that table. Step 6: Click the dropdown list button to the right of the Column labels.
Step 9: Type 80 in the Right Field. Step Click OK. You could analyze your data from the different tables and arrive at the specific report you want in just a few steps. This was possible because of the pre-existing relationships among the tables in the source database. As you imported all the tables from the database together at the same time, Excel recreated the relationships in its Data Model. If you do not import the tables at the same time, or if the data is from different sources or if you add new tables to your Workbook, you have to create the Relationships among the Tables by yourself.
Create Relationship between Tables Relationships let you analyze your collections of the data in Excel, and create interesting and aesthetic reports from the data you import. Step 1: Insert a new Worksheet. Step 2: Create a new table with new data. Name the new table as Sports. Rename the Sheet1 as Medals and Sheet2 as Sports. A complete list of available tables will be displayed.
The newly added table- Sports will also be displayed. In the expanded list of fields, select Sports. Excel messages you to create a relationship between tables. The Create Relationship dialog box opens. Step 6: To create the relationship, one of the tables must have a column of unique, non- repeated, values. In the Disciplines table, SportID column has such values. The table Sports that we have created also has the SportID column.
In Table, select Disciplines. Step 8: In Related Table, select Sports. Click OK. Adjust the order of the fields in the Rows area to maintain the Hierarchy. In this case, Sport should be first and Discipline should be the next, as Discipline will be nested in Sport as a sub-category. You can use PowerPivot to access and mashup data from virtually any source.
You can create your own compelling reports and analytical applications, easily share insights, and collaborate with colleagues through Microsoft Excel and SharePoint. Using PowerPivot, you can import data, create relationships, create calculated columns and measures, and add PivotTables, slicers and Pivot Charts.
To start, get some more data into your workbook. You can copy and paste data from a Web Page also. Insert a new Worksheet. Step 2: Copy data from the web page and paste it on the Worksheet. Step 3: Create a table with the data. Name the table Hosts and rename the Worksheet Hosts. Hosts Table gets added to the Data Model in the Workbook. The PowerPivot window opens. You will find all the Tables in the Data Model in the PowerPivot, though some of them are not present in the Worksheets in the Workbook.
Step 7: Use the slide bar to resize the diagram so that you can see all tables in the diagram. Step 8: Rearrange the tables by dragging their title bar, so that they are visible and positioned next to one another. Also, DisciplineEvent column in the Events table consists of unique, non-repeated values. Click on Data View in Views Group.
Check DisciplineEvent column in the Events table. Step Once again, click on Diagram View. A line appears between the Events Table and the Medals Table, indicating a relationship has been established.
The line and the fields defining the relationship between the two tables are highlighted as shown in the image given below. To do so, a field with values that uniquely identify each row in the Hosts table is to be found first. Then, search the Data Model to see if that same data exists in another table. This can be done in Data View. There are two ways of doing this. The Data View appears. Step 2: Click on the Hosts table. Step 3: Check the data in Hosts Table to see if there is a field with unique values.
There is no such field in Hosts Table. You cannot edit or delete existing data using PowerPivot. However, you can create new columns by using calculated fields based on the existing data. Adjacent to the existing columns is an empty column titled Add Column.
PowerPivot provides that column as a placeholder. The Add Column is filled with values. Check the values to verify that they are unique across the rows. Step 5: The newly created column with created values is named CreatedColumn1. To change the name of the column, select the column, right-click on it.
Step 6: Click on the option Rename Column. Step 8: Now, Select the Medals Table. Step 9: Select Add Column. Step Rename the Column as Year. Step Select Add Column. Step Rename the column as EditionID. Step Sort the Column in Ascending Order. Relationship using calculated columns Step 1: Switch to Diagram View. Ensure that the tables Medals and Hosts are close to each other. PowerPivot creates a relationship between the two tables. A line between the two tables, indicates the relationship.
Excel — External Data Connection Advanced Excel Once you connect your Excel workbook to an external data source, such as a SQL Server database, Access database or another Excel workbook, you can keep the data in your workbook up to date by "refreshing" the link to its source. Each time you refresh the connection, you see the most recent data, including anything that is new or has been deleted. Let us see how to refresh PowerPivot data.
Step 1: Switch to the Data View. Step 2: Click on Refresh. Step 3: Click on Refresh All. After the refresh is complete, the status is displayed. Step 4: Click on Close. The data in your Data Model is updated. Update the Data Connections Step 1: Click any cell in the table that contains the link to the imported data file. Step 2: Click on the Data tab. Step 3: Click on Refresh All in Connections group. Step 4: In the drop-down list, click on Refresh All.
All the data connections in the Workbook will be updated. Step 1: Click any cell in the table that contains the link to the imported Data file. Step 3: Click on Connections in the Connections group. The Workbook Connections window appears. Step 4: Click on Properties. The Connection Properties Window appears. Click on the Usage tab. The options for Refresh Control appear. Step 6: Select Refresh data while opening the file.
You can use this option to save the workbook with the query definition but without the external data. Step 7: Click OK. Whenever you open your Workbook, the up-to-date data will be loaded into your Workbook. Step 3: Click on the Connections option in Connections group.
A Workbook Connections window appears. A Connection Properties Window appears. Step 5: Click on the Usage tab. Your data will be refreshed every 60 minute that is every hour.
Enable Background Refresh For very large data sets, consider running a background refresh. This returns the control of Excel to you instead of making you wait several minutes for the refresh to finish. You can use this option when you are running a query in the background. However, you cannot run a query for any connection type that retrieves data for the Data Model. Connection Properties Window appears. The Refresh Control options appear. For example, you can expand the source data to include more rows of data.
However, if the source data has been changed substantially, such as having more or fewer columns, consider creating a new PivotTable. Step 1: Click anywhere in the PivotTable. Step 4: Click on Change Data Source. The current Data Source is highlighted. If you want to base your PivotTable on a different external source, it might be best to create a new PivotTable. If the location of your external data source is changed, for example, your SQL Server database name is the same, but it has been moved to a different server, or your Access database has been moved to another network share, you can change your current connection.
Step 4: Click on the option Choose Connection. In the Show box, keep All Connections selected. All the Connections in your Workbook will be displayed. Step 6: Click on New Source. Go through the Data Connection Wizard Steps. Step 3: Click on Select in the Actions Group as shown in the image given below. The entire PivotTable will be selected. Step 5: Press the Delete Key. To do this, follow the steps given below.
Step 1: Right-click on the Worksheet tab. Step 2: Click on Delete. You get a warning message, saying that you cannot Undo Delete and might lose some data.
Since, you are deleting only the PivotTable Sheet you can delete the worksheet. Step 3: Click on Delete. Using the Timeline A PivotTable Timeline is a box that you can add to your PivotTable that lets you filter by time, and zoom in on the period you want. This is a better option compared to playing around with the filters to show the dates.
It is like a slicer you create to filter data, and once you create it, you can keep it with your PivotTable. This makes it possible for you to change the time period dynamically. Step 3: Click on Insert Timeline in the Filter group. An Insert Timelines Dialog Box appears. The timeline for your PivotTable is in place. Step 1: Click the small arrow next to the time level-Months. The four time levels will be displayed.
The Timeline filter changes to Quarters. Step 3: Click on Q1 The Timespan Control is highlighted. The PivotTable Data is filtered to Q1 Step 4: Drag the Timespan handle to include Q2 The timeline is cleared as shown in the image given below. You can even create a PivotChart that is recommended for your data.
Excel will then create a coupled PivotTable automatically. Step 1: Click anywhere on the Data Table. The Insert Chart Window appears. Step 4: Click on the Recommended Charts tab. The charts with the PivotChart icon in the top corner are PivotCharts. A Preview appears on the Right side. Your standalone PivotChart for your Data is available to you. Excel — Power View Advanced Excel Power View is a feature of Microsoft Excel that enables interactive data exploration, visualization, and presentation encouraging intuitive ad-hoc reporting.
Step 1: Click on the File menu and then Click on Options. The Excel Options window appears. Step 2: Click on Add-Ins. Step 3: In the Manage box, click the drop-down arrow and select Excel Add-ins. Step 4: All the available Add-ins will be displayed. Now, you are ready to create the Power View sheet. Step 1: Click on the Data Table. Step 2: Click on Insert tab. Under the Power View Fields you will find Areas. Excel — Visualizations Advanced Excel You can quickly create a number of different data visualizations that suit your data using Power View.
Create Charts and other Visualizations For every visualization you want to create, you start on a Power View sheet by creating a table, which you then easily convert to other visualizations, to find one that best illustrates your Data. Step 1: Under the Power View Fields, select the fields you want to visualize.
Step 2: By default, the Table View will be displayed. As you move across the Table, on the top-right corner, you find two symbols — Filters and Pop out. Step 3: Click on the Filters symbol. The filters will be displayed on the right side. Filters has two tabs.
View tab to filter all visualizations in this View and Table tab to filter the specific values in this table only. Step 3: Click on Matrix. The Matrix Visualization appears. Step 3: Click on Card. The Card Visualization appears. You can use several design options in a chart such as showing and hiding labels, legends, and titles. Charts are interactive. The charts are interactive in a presentation setting also. Step 1: Create a Table Visualization from Medals data. You can use Line, Bar and Column Charts for comparing data points in one or more data series.
In these Charts, the x-axis displays one field and the y-axis displays another, making it easy to see the relationship between the two values for all the items in the Chart. Line Charts distribute category data evenly along a horizontal category axis, and all numerical value data along a vertical value axis. Step 3: Create the same Table Visualization below. Step 6: Click on Line.
Step 7: Click on the Line Chart Visualization. Step 9: Click on the Stacked Bar option. That portion of the bar is highlighted. Only the row containing the Data specific to the selected bar is displayed in the table above.
You can use the column charts for showing data changes over a period of time or for illustrating comparison among different items. In a Column Chart, the categories are along the horizontal axis and values are along the vertical axis. Step Click on Stacked Column. Step 1: Click on the Table Visualization as shown below.
Step 3: Click on Pie as shown in the image given below. You can also make your Pie Chart Visualization sophisticated by adding more features. Step 1: Add Field Gender to the Table above.
Step 2: Click on Pie Chart Visualization. In Scatter charts, the x-axis displays one numeric field and the y-axis displays another, making it easy to see the relationship between the two values for all the items in the chart. In a Bubble Chart, a third numeric field controls the size of the data points.
Step 3: Click on Scatter. The Data points are little circles and all are of same size and same color. The data points are circles of the size represented by the values of Data points. The data labels are the Category Values. You can pause at any point to study the data in more detail. All the bubbles of that color will be highlighted and other bubbles will be grayed out.
Maps You can use Maps to display your data in the context of geography. Maps in Power View use Bing map tiles, so you can zoom and pan as you would with any other Bing map. To make maps work, Power View has to send the data to Bing through a secured web connection for geocoding. So, it asks you to enable the content. Adding locations and fields places dots on the map. The larger the value, the bigger the dot. When you add a multi- value series, you get pie charts on the map, with the size of the pie chart showing the size of the total.
Step 2: Drag a numeric field such as Count to the table. Step 4: Click on Map in the Switch Visualization group. The Table Visualization converts into Map Visualization. Power View creates a map with a dot for every geographic location.
The size of the dot is the value of the corresponding numeric field. Step 5: Click on a dot. The data, viz. The Dots are converted into Pie Charts. Each Color in the Pie representing the category of the Medals. The Dot gets highlighted and zoomed. The details of the Pie Slice are displayed. Step 9: Place the cursor on one of the Dots and click on it.
That Pie Slice is highlighted. The other Slices in the Pie and all other Pie Dots will gray out. You can have Multiples arranged side by side, making it easy to compare many different values at the same time. Multiples are also called Trellis Charts.
Step 1: Start with a Pie Chart. Click on the Pie Chart. Step 2: Drag a Field to Vertical Multiples. Step 5: Click on Grid Width and select a number. Vertical Multiples expand across the available page width and then wrap down the page into the space available.
If all the multiples do not fit in the available space, you get a vertical scroll bar. The horizontal multiples expand across the page. If all the multiples do not fit in the page width, you get a horizontal scroll bar. Step 7: Click on Multiples. You have created Horizontal Multiples of the Line charts. Visualization — Tiles Tiles are containers with a dynamic navigation strip. You can convert a Table, Matrix or Chart to Tiles to present data interactively. Tiles filter the content inside the Tile to the value selected in the Tab Strip.
You can have a single Tile for each possible field value so that if you click on that Tile, data specific to that Field is displayed. All the content in the container is filtered by the selected Tile value. The Tile container has two navigation strip types: tile flow and tab strip. What you have created above is the tab strip. Tab strip displays the navigation strip across the top of the visualization. Step 3: Click on a Tile. Step 5: Click on Tile Type in the Tiles group.
Step 6: Click on Tile Flow. The selected Tile is always centered. When you Scroll, the Tiles go on being selected. Step 7: Click on Map in the Switch Visualization group. Step 8: Drag Medal to Color. Step 9: De-select the Field Gender. You got Map Visualization with Tile Flow. Likewise, you can have any data visualization with Tiles. Power View — Additional Features Advanced Excel Power View in Excel provides an interactive data exploration, visualization, and presentation experience for all skill levels as you have seen in the previous section.
It offers you everything you need to grasp basic Excel functions, such as creating and editing worksheets, setting up formulas, importing data, performing statistical functions, editing macros with Visual Basic-and beyond. Written by expert Greg Harvey, who has sold more than 4.
From generating pivot tables and performing financial functions to performing error trapping and building and running macros-and everything in between-this hands-on, friendly guide makes working with Excel easier than ever before. This site comply with DMCA digital copyright.
We do not store files not owned by us, or without the permission of the owner.
0コメント