13 Excel Tips for Developers

November 13, 2014
13 Excel Features that Developers Should Know
(Hosted by SQL PASS BA VC)
The Baker’s Dozen
Business Intelligence
13 SQL Server /
Business Intelligence
Productivity Tips
Kevin S. Goff
Microsoft SQL Server MVP
Kevin S. Goff – Brief BIO
• Developer/architect since 1987 / Microsoft SQL Server MVP
• Columnist for CoDe Magazine since 2004,
“The Baker’s Dozen” Productivity Series”, 13 tips on a SQL/BI topic
• Wrote a book, collaborated on a 2nd book
• Frequent speaker for SQL Server community events and SQL Live!360
• Email: [email protected]
• My site/blog: www.KevinSGoff.Net (includes SQL/BI webcasts)
• Releasing some SQL/BI video courseware in 2015
13 Excel Features for Developers
Today’s Topic
• Why Excel?
• Business Users rely on Excel for all sorts of
Custom Reporting - good application developers
should have general awareness of “how” people
are using the data
• Sometimes Excel is a good prototyping tool for
reporting, and even for sanity-checking data
• Sometimes developers even use for their own
personal projects!
13 Excel Features for Developers
Topics for today
What-If/Goal Seeking
Named Ranges
Basic Pivot Tables against OLTP/OLAP Data Sources
Pivot Table Options (Filters, Calculations, Visual Slicers)
Pivot Charts and Sparklines
Dynamic Coloring and Macros
Power Pivot
Power Pivot KPIs and DAX
Power View
A special Pivot Chart
Recommended Reading
13 Excel Features for Developers
1-What-If/Goal Seeking
• If I scored above 85 on the first three tests….
• What is the lowest score I can get on the fourth
test and still have an 85 average overall?
13 Excel Features for Developers
Back to TOC
1-What-If/Goal Seeking
Set payment function
first, then do a goal seek
on the payment, to see
how it changes the rate
Want a loan for 20K
Want to pay back in 5 years
We can afford $400 a month
What interest rate should we
look for?
13 Excel Features for Developers
Back to TOC
• Specify:
– The value we
want to lookup
– The table range
– The index
column for the
return value
– Whether it’s an
exact or
13 Excel Features for Developers
Back to TOC
3-Named Ranges
Don’t use regular range,
copy/paste won’t freeze
absolute cell references
13 Excel Features for Developers
Lock References you want
to freeze with the $ , or
create a named cell Range
Back to TOC
4-Basic Pivot Tables against data sources
1. Can create Pivot Tables or Pivot Charts Against:
Relational Databases or Views
OLAP Cubes
2. “All the data” stays in the source, only the results of a query come into
the Pivot Table or Pivot Chart
3. For years, the standard way to create analytics in Excel against data
4. (We’ll see Power Pivot and how that changes things)
13 Excel Features for Developers
Back to TOC
5-Pivot Table Options
Can sort
each level
by $$$
We can redefine
Reseller Sales as a
% of the Row
Slices (Visual
13 Excel Features for Developers
Back to TOC
5-Pivot Table Options
Can implement a “top 5 and all other” manually,
behind the scenes, with a copy of the first five rows,
and then a formula:
=GETPIVOTDATA(A4, "Total") - SUM(F4:F8)
13 Excel Features for Developers
Back to TOC
6-Pivot Charts and Sparklines
Visual Sparkline
Dynamic coloring for high
month and low month for
each country. Build as a
macro for one row and then
13 Excel Features for Developers
Back to TOC
7-Dynamic Coloring and Macros
Start recording a macro
Set Dynamic Conditional Formatting
from the Home Menu dropdown
Stop recording the macro
Do it for both Top 1 and Bottom 1,
and then concatenate one macro
into the other
13 Excel Features for Developers
Back to TOC
8-Power Pivot
Compressed Star
Schema Model “in
the basement” of
the Excel Sheet
1. Users can point Excel to database
2. Can create the equivalent of a
“mini-cube”, compressed using
xVelocity compression
3. The Power Pivot Data Model lives
“inside” the Excel Sheet
4. Users can create many Pivot
Tables or Pivot Charts off the
5. Users can also create Power View
report visualizations off the data
13 Excel Features for Developers
Back to TOC
8-Power Pivot
Must create
relationships if source
was views
Model can come from
physical relational
tables or database views
This is somewhat like building
SSAS OLAP cubes, except it
doesn’t support advanced
fact/dimension relationships
Can implement
dimensional hierarchies
13 Excel Features for Developers
Back to TOC
8-Power Pivot
KPI scorecards in
Excel, similar to other
dashboarding tools
Garrett’s sales as % of Quota was 80.18%. That’s “OK”, so
status is yellow. But his sales one year ago was 85.13% of
quota – so his % of quota is trending down, and that’s not good
User can
look at
sales and
quotas by
for a year
or a
The % of Quota last year represents a DAX formula to express
the % of quota for “same time period last year”
13 Excel Features for Developers
Back to TOC
8-Power Pivot
Monthly Sales + 12
month moving average,
plotted as a line chart
(Requires a set of DAX
13 Excel Features for Developers
Back to TOC
9-Power Pivot KPIs and DAX
Calculate a ratio on the fly:
not too bad
Express in terms of a year ago:
arguably a bit more involved
DAX formula to express
% of Quota in terms of
one year ago
13 Excel Features for Developers
Back to TOC
9-Power Pivot KPIs and DAX
More complicated DAX code to calculate a Moving Average
Must determine, for any one month, the 12 month range (start month and end month of range)
Must average the internet sales over the span of that range
DAX is sometimes advertised as “easier” than the MDX language used in SSAS/OLAP
applications, but sometimes DAX can be just as involved
13 Excel Features for Developers
Back to TOC
10-Power View
• Report Visualization Tool for Power Users
• Great for storyboard-type reporting, “facestyle” reporting where a page or subset of a
page tells a story
• Not intended for full blown detail reports
• Not as much developer functionality as
Reporting Services
13 Excel Features for Developers
Back to TOC
10: Power View
Power View visualization
against the Power Pivot
Data Model
Scatter chart
plotting city
observations of
Sales revenue and
# of orders
Can use year as “Play
axis” to show that while
Beaverton is top city in
Oregon across all
years, it wasn’t top city
in 2007
13 Excel Features for Developers
User can
filter on
– State
Back to TOC
10: Power View
While this has nice interactive features, advanced users
might want to show a linear regression line, and also the
correlation coefficient (impact of order count on sales)
Here is where tools like SSRS or even Excel Pivot Charts
are a better option – Power View does not have these
We can even select a single city
and plot the progression of
annual sales for a city over time
13 Excel Features for Developers
Back to TOC
10: Power View
Cross filtering – I can click on the
pie slice for Australia, and the
bar chart above shades the
monthly sales just for Australia
13 Excel Features for Developers
Back to TOC
11-Custom MDX in OLAP Pivot Tables
When using OLAP cubes and we need to
write custom MDX calculations, can use a
free add-in: Excel OLAP PivotTable
13 Excel Features for Developers
Back to TOC
12-A special Pivot Chart
Y-axis: revenue by
Pearson correlation: measures the correlation or
strength of linear dependence.
Observation points in a scatter graph:
each marker represents a city, their #
of customers (bottom axis) and
Internet sales (left axis). Chart shows
relationship between the 2 variables
.85 to 1 = strong correlation
.75 to .85 = moderate correlation
< .75 = weak correlation (not very reliable)
We can create this spreadsheet
in Excel against OLAP data,
deploy to SharePoint, and then
use in a PPS dashboard, and
take advantage of PPS filters
Regression trendline: shows slope calculation (every 1
customer results in $1508.7 + $)4640.9 in revenue)
R-squared represents “goodness of fit” of plotted
points relative to trendline (closer the line passes
through the points, closer to 1 is the value)
Bottom axis: Customer Count by City
13 Excel Features for Developers
Back to TOC
13-Recommended Reading
• Great blog content for Excel, Power Pivot, DAX, Power BI, etc.
• They are great resources for newer features in Power BI (Power
Maps, Power Query, etc)
• Chris Webb's Blog
• Alberto Ferrari's Blog
• Marco Russo's Blog
13 Excel Features for Developers
Back to TOC

similar documents