Step by Step Instructions for Easy EXCEL Dashboards

Step by Step Instructions for Easy EXCEL Dashboards
By David Wetton

x

Introduction to Pivot Tables, Charts, and Dashboards in Excel

By Jon Acampora of  ExcelCampus.com

Part 1
How to create an interactive dashboard using Pivot Tables and Pivot Charts.
Works with Excel 2003, 2007, 2010, 2013 for Windows & Excel 2011 for Mac

Download file used in the video



Part 2
In this video I explain some of the different pivot table calculation types. Learn how to calculate the average, count, sum, and percentage of total. I explain how to update your pivot table with more data, and also how to refresh your pivot tables.

The video contains a lot of Excel tips, like how to quickly make a duplicate copy of a sheet, how to select the visible cells only, and a lot of useful keyboard shortcuts.

Download file used in the video




Part 3
This is part 3 in the series on Pivot Tables and Dashboards. In this video we bring it all together to create the interactive dashboard.

You will learn how to:
- Group dates into months and years to create a time series trend chart.
- Group amounts to create a distribution (histogram) chart.
- Add slicers to your pivot tables and charts to make them interactive.
- Lots of tips and shortcuts for formatting your charts.

Download file used in the video

Built in Functions

MS Excel has many built in functions which we can use in our formula. To see all the functions by category choose Formulas Tab » Insert Function. Then Insert function Dialog appears from which we can choose function.




Functions by categories
Let us see some of the built in functions in MS Excel.

Text Functions

• LOWER : Converts all characters in a supplied text string to lower case
• UPPER : Converts all characters in a supplied text string to upper case
• TRIM : Removes duplicate spaces, and spaces at the start and end of a text string
• CONCATENATE : Joins together two or more text strings
• LEFT : Returns a specified number of characters from the start of a supplied text string
• MID : Returns a specified number of characters from the middle of a supplied text string
• RIGHT : Returns a specified number of characters from the end of a supplied text string
• LEN : Returns the length of a supplied text string.
• FIND : Returns the position of a supplied character or text string from within a supplied text string (case-sensitive)

Date & Time

• DATE : Returns a date, from a user-supplied year, month and day
• TIME : Returns a time, from a user-supplied hour, minute and second
• DATEVALUE : Converts a text string showing a date, to an integer that represents the date in Excel's date-time code
• TIMEVALUE : Converts a text string showing a time, to a decimal that represents the time in Excel
• NOW : Returns the current date & time
• TODAY : Returns today's date

Statistical

• MAX : Returns the largest value from a list of supplied numbers
• MIN : Returns the smallest value from a list of supplied numbers
• AVERAGE : Returns the Average of a list of supplied numbers
• COUNT: Returns the number of numerical values in a supplied set of cells or values
• COUNTIF : Returns the number of cells (of a supplied range), that satisfy a given criteria
• SUM : Returns the sum of a supplied list of numbers

Logical

• AND : Tests a number of user-defined conditions and returns TRUE if ALL of the conditions evaluate to TRUE, or FALSE otherwise
• OR : Tests a number of user-defined conditions and returns TRUE if ANY of the conditions evaluate to TRUE, or FALSE otherwise
• NOT : Returns a logical value that is the opposite of a user supplied logical value or expression i.e. returns FALSE is the supplied argument is TRUE and returns TRUE if the supplied argument is FALSE)

Math & Trig

• ABS : Returns the absolute value (ie. the modulus) of a supplied number
• SIGN : Returns the sign (+1, -1 or 0) of a supplied number
• SQRT : Returns the positive square root of a given number
• MOD : Returns the remainder from a division between two supplied numbers

From tutorialspoint.com

Pivot Charts Excel 2010

Pivot Charts
A pivot chart is a graphical representation of a data summary displayed in a pivot table. A pivot chart is always based on a pivot table. Although Excel lets you create a pivot table and a pivot chart at the same time, you can’t create a pivot chart without a pivot table. All Excel charting features are available in a pivot chart

Pivot charts are available under Insert tab » PivotTable dropdown » PivotChart

Pivot Chart Example
Now let us see Pivot table with the help of example. Suppose you have huge data of voters and you want to see summarized view of the data of voter Information per party in the form of charts then you can use Pivot chart for it. Choose Insert tab » Pivot Chart to insert pivot table.

MS Excel selects the data of the table.You can select the pivot chart location as existing sheet or new sheet. Pivot chart will depends on automatically created pivot table by the MS Excel. You can the the generated pivot chart in the below screen-shot



From TutorialsPoint.com...