Loading...

Follow My Online Training Hub on Feedspot

Continue with Google
Continue with Facebook
Or

Valid


My Online Training Hub by Mynda Treacy - 5d ago

Excel infographics, like the one below, are easy to build and there are loads of tools you can use to add some sparkle.

Infographics are suited to one off brochure style reporting designed to catch your eye and be easily digestible and memorable. They’re often found in newspapers or other print publications because they draw you in with the colourful display and fun graphics.

Don’t confuse them with Excel Dashboard reports, which are interactive and aim to quickly convey key business metrics without unnecessary noise and gimmicks that might distract your audience.

Download the file

Enter your email address below to download the sample workbook.

Get Workbook
By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel Workbook. Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download.
Building Excel Infographics

The black and white image below numbers the various elements I used to build my Excel infographic. Continue scrolling past the image for further instructions.

Excel has lots of tools you can bring together to build infographics, including:

  1. Shapes and text boxes – layer Shapes and Text Boxes to align and arrange different elements without being limited to the size of one object.
  2. Icons (available in Excel 2016 Office 365 onward) – Icons convey a visual message about the data that is instantly recognized. They’re fun and add interest to the numbers.
  3. Word Art – add graphic design font elements to give your infographic a professional touch. Use different fonts and font sizes, but try to limit it to two or three at the most.
  4. Custom Cell Formats – I used a repeating asterisk to create separation between some of the metrics. Download the file and inspect the cell number format in the Excel file to see how I did it.
  5. 3D Models (available in Excel 2016 Office 365 onward) – there are lots of 3D models already available. I used one for the rose in the top right, but it was just for fun. Don’t feel they’re necessary.  

    Note: 3D models result in large file sizes. The small rose in my Excel infographic added 18MB to my file size, which is why it’s not in the downloadable file.

  1. Doughnut and Bar Charts – Generally, I avoid pie and doughnut charts because they’re big and bulky and are more difficult to read than bar or column charts. However, in infographics your goal is to grab attention first and convey information second. And for this, pie and doughnut charts work well.

    That doesn’t mean you should pile your pies full of segments. No more than two or three segments is enough.

    When using bar or column charts it’s helpful if you can sort the data in descending or ascending order.

  1. Cell Fill and Borders – I used a fancy line style for the infographic border and applied a contrasting color:

Bonus tip: Set your Excel Theme to a color palette that suits your topic. This will save you time choosing colors because the color palettes will be prepopulated to suit your infographic.

I used the Red Violet color theme, as you can see in the screenshot below.

A word of advice on color; less is more. Try to stick to one color palette column for the majority of your infographic, and maybe use one or two colors from the other side of the spectrum to differentiate elements. The screenshot below highlights the colors I used in green boxes:

Use color consistently; I’ve used blue when encoding male data, stereotypical I know!

Excel Infographics Summary
  • Remember, infographics are for one off brochure style reports. Don’t be tempted to turn your dashboard reports into infographics.
  • Layer and combine different Excel tools like Shapes, Icons and text boxes. But don’t use every shape available, again, less is more.
  • Be consistent with color – data that represents the same location/product/gender etc. should be encoded in the same color.
  • Don’t go crazy with fonts. Two or three different fonts is enough.

Use placement - put your most important information towards the top to draw the reader in. Create a story with the data that flows from one point to the next to keep the reader engaged.

Please Share

If you liked this please click the buttons below to share.

The post Excel Infographics appeared first on My Online Training Hub.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
My Online Training Hub by Mynda Treacy - 2w ago

The Excel Forecast Sheet and charts are a new feature available in Excel 2016. In just a few clicks you can get a forecast done for you, including a snazzy chart like the one below:

This new forecasting tool creates a copy of your data and inserts it on a new sheet in your file, along with the forecast formulas to predict the future values, and all presented in a chart.

Download Workbook and Take a Look

Note, this workbook contains functions only available in Excel 2016 or later. If you don’t have Excel 2016 then you can’t edit the FORECAST.ETS… formulas.

Enter your email address below to download the sample workbook.

Get Workbook
By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel Workbook. Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download.
Data Required for Excel Forecast Sheet

All you need is some historical time-based data in a tabular format. Excel needs a column containing the dates/times at consistent intervals, and a column containing the values.

For example, below is the data I’m using, which is visitor numbers to Hawaii:

Data source: HTA and DBEDT

Note: Your date/time data can be missing up to 30% of the data points or have several numbers with the same time stamp. Excel will average the data with the same time stamp. However, summarizing data before you create the forecast will produce more accurate forecast results.

Inserting Excel Forecast Sheets

To insert a Forecast chart, start by selecting your data, or if it’s a contiguous range just select a cell in the table > Data tab > Forecast Sheet:

This opens the Create Forecast Worksheet dialog box, shown below, where you can choose the chart type (1) and when to end the forecast (2):

Clicking on ‘Options’, below the ‘Forecast End’ field, reveals more settings. These are explained as follows (numbers below correspond to image above):

 
Forecast Options Description
Forecast Start (3) This is the date the forecast begins. By default, it’s the end of your data, but if you want to exclude data from the forecast then you can choose a date before the end of the historical data. This will only include data prior to the ‘start date’ in the forecast (this is sometimes referred to as "hindcasting").

Tips:

  • Starting your forecast before the last historical point allows you to get a feel for the prediction accuracy as you can compare the forecasted series to the actual data. However, if you start the forecast too early, the forecast generated won't necessarily represent the forecast you'll get using all the historical data. Using all your historical data gives you a more accurate prediction.
  • For seasonal data it is recommended to start the forecast before the last historical point.
Confidence Interval (4)

The confidence interval is the range surrounding each predicted value, in which 95% of future points are expected to fall, based on the forecast (with normal distribution). These are the two finer lines either side of the forecast line (or if you choose the column chart option they are the error bar values).

The confidence interval can help you get a feel for the accuracy of the forecast. A smaller interval implies more confidence for the specific point.

Change the default level using the up or down arrows, or turn confidence intervals off by unchecking the box.

Seasonality (5)

Seasonality is a number for the length (number of points) of the seasonal pattern and is automatically detected. For example, in a yearly sales cycle, with each point representing a month, the seasonality is 12. You can override the automatic detection by choosing Set Manually and then entering a number.

When the seasonality is not significant enough for the algorithm to detect, the prediction will revert to a linear trend.

The default value of 1 means Excel detects seasonality automatically for the forecast and uses positive, whole numbers for the length of the seasonal pattern. 0 indicates no seasonality, meaning the prediction will be linear.

Note: When setting seasonality manually, avoid a value for less than 2 cycles of historical data. With less than 2 cycles, Excel cannot identify the seasonal components.

Include Forecast Statistics (6)

Excel can automatically generate a table of statistics using the FORECASE.ETS.STAT function, including measures such as the smoothing coefficients (Alpha, Beta, Gamma), and error metrics (MASE, SMAPE, MAE, RMSE).

Check this box if you want this additional statistical information on the forecast included in a new worksheet.

Timeline Range (7) By default, the timeline range includes all dates in your table, but you can change it here. Note; the range must match the Values Range.
Values Range (8) By default, the Values range includes all values in your table, but you can change it here. Note; the range must match the Timeline Range.
Fill Missing Points Using (9) Excel uses interpolation to handle missing points, which means missing point will be completed as the weighted average of its neighboring points, where fewer than 30% of the points are missing. If you prefer, you can treat the missing points as zeros by selecting Zeros in the list.
Duplicate Aggregates Using (10) Excel will average values that contain the same date/time. You can choose another calculation method, such as Median, from the list.
  Excel Forecast Sheet

The new Forecast sheet contains a Table and Chart. The bottom of the Table contains a series of FORECAST formulas (see colored boxes in image below):

This is all created for you automatically.

Forecast Statistics

If you choose to include the Forecast Statistics in the Options when creating the forecast sheet, Excel will include a table of different statistics, shown below, beside the forecast table.

They all use the FORECAST.ETS.STAT function as you can see in column I below, which displays the formula from column H. Notice that the 3rd argument is what specifies the statistic type:

Sharing Excel Forecast Sheets

You can share Excel workbooks containing forecast sheets and charts with users who have earlier versions of Excel. They will see the table and chart, but they can’t edit the formulas.

Modifying the Excel Forecast Sheet

So, you’ve created your forecast sheet and then you realise you want to change something.

Firstly, there is no link back to the original source data, so if you make changes to your source data you need to recreate the forecast sheet, or also edit the data in the forecast sheet table.

Also, there’s no way to re-open the ‘Create Forecast Worksheet’ dialog box and make changes to the Options and settings of the forecast. However, you can modify the FORECAST formulas directly in the Table.

For example, if you want to change the seasonality, you’d edit the ‘seasonality’ argument in the forecast formulas. Taking the example below, we’d alter the 12:

You can also edit the ranges being referenced to tweak the forecast. For example, the data below is on a consistent upward trend, but you can see it then takes a dip in the first forecast period:

Maybe you’d prefer it continued the upward trajectory, in which case you could alter the timeline and values ranges to forecast based on more recent data:

Don’t forget to also alter the confidence bound formulas in columns D and E.

Functions Used in Forecast Sheet

The Forecast Sheet uses some of the new forecast functions available in Excel 2016, as follows:

Forecasted Values - FORECAST.ETS Function:

=FORECAST.ETS(target_date, values, timeline, [seasonality], [data_completion], [aggregation])

Confidence Interval – FORECAST.ETS.CONFINT Function:

=FORECAST.ETS.CONFINT(target_date, values, timeline, [confidence_level], [seasonality], [data_completion], [aggregation])

Forecast Stats – FORECAST.ETS.STAT Function:

=FORECAST.ETS.STAT(values, timeline, statistic_type, [seasonality], [data_completion], [aggregation])

They all contain similar arguments, which are described below:

Target_date - The next data point you want to forecast. This can be date/time or numeric

Values – The range of cells containing the historical values you’re basing your forecast on. This range must be the same size as the Timeline argument’s range.

Timeline - The range of cells containing the dates that relate to the historical ‘values’. The timeline values must have a consistent step between them and can’t be zero. The ranges of the timeline and values must be the same size, or Excel will return the #N/A error.

The timeline doesn’t need to be sorted, as Excel will sort it implicitly for calculations. If a constant step can't be identified in the timeline range, Excel will return the #NUM! error. If timeline contains duplicate values, Excel will return the #VALUE! error.

[Seasonality] – An optional numeric value. The default value of 1 means Excel detects seasonality automatically for the forecast and uses positive, whole numbers for the length of the seasonal pattern. 0 indicates no seasonality, meaning the prediction will be linear. Positive whole numbers will indicate to the algorithm to use patterns of this length as the seasonality. For any other value, Excel will return the #NUM! error.

Maximum supported seasonality is 8,760 (number of hours in a year). Any seasonality above that number will result in the #NUM! error.

[Confidence_level] - An optional numerical value greater than 0 and less than 1, indicating a confidence level for the calculated confidence interval. For example, for a 95% confidence interval, a 95% confidence level will be computed (95% of future points are to fall within this radius from prediction). The default value is 95%. If your confidence interval value is zero or less, or 1 or more, Excel will return the #NUM! error.

[Data completion] – This is an optional argument and although the timeline requires a constant step between data points, Excel supports up to 30% missing data, and will automatically adjust for it. 0 will indicate the algorithm to account for missing points as zeros. The default value of 1 will account for missing points by completing them to be the average of the neighboring points.

[Aggregation] - Optional argument. Although the timeline requires a constant step between data points, Excel will aggregate multiple points which have the same time stamp. The aggregation parameter is a numeric value indicating which method will be used to aggregate several values with the same time stamp. The default value of 0 will use AVERAGE, while other options are SUM, COUNT, COUNTA, MIN, MAX, MEDIAN.

Statistic_type – This is numeric value between 1 and 8, indicating which statistic will be returned for the calculated forecast. See table below for descriptions:

 
statistic_type Description
1 Alpha parameter of ETS algorithm - Returns the base value parameter—a higher value gives more weight to recent data points.
2 Beta parameter of ETS algorithm - Returns the trend value parameter—a higher value gives more weight to the recent trend.
3 Gamma parameter of ETS algorithm - Returns the seasonality value parameter—a higher value gives more weight to the recent seasonal period.
4 MASE metric - Returns the mean absolute scaled error metric—a measure of the accuracy of forecasts.
5 SMAPE metric - Returns the symmetric mean absolute percentage error metric—an accuracy measure based on percentage errors.
6 MAE metric - Returns the symmetric mean absolute percentage error metric—an accuracy measure based on percentage errors.
7 RMSE metric - Returns the root mean squared error metric—a measure of the differences between predicted and observed values.
8 Step size detected - Returns the step size detected in the historical timeline.
 

Tip: These new Forecast functions can also be used independently of the forecast sheet, just like regular functions.

Forecasting Pre Excel 2016

If you’re using Excel 2013 or earlier, you can create your own Forecast sheet manually using the FORECAST function (with some limitations).

For example, the chart below shows the result of the FORECAST function in the dashed line. You’ll notice it doesn’t take seasonality into account, this is the first limitation, as the FORECAST function uses linear regression and doesn’t have the ability to factor in seasonality.

The other limitation is that there’s no equivalent function to calculate the confidence intervals using exponential triple smoothing (ETS) that the new FORECAST.ETS.CONFINT function uses. The closest you’ll get is to use the CONFIDENCE.NORM function.

Please Share

If you liked this please click the buttons below to share.

The post Excel Forecast Sheet appeared first on My Online Training Hub.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

There are hundreds of Excel keyboard shortcuts but we don't use them all frequently.

There'll be some that you use more often than me, but in this post I'm listing the keyboard shortcuts I use all the time.

It was hard to pick just a few, it was like being asked to pick your favourite child.

I've also compiled a list of 239 Excel Shortcuts. Learn as many as you can to improve your efficiency and how fast you work.

Navigation

Let's start with some obvious ones, but still ones that you can't do without.

  • The arrow keys move you around the cells in the sheet
  • PAGE UP/PAGE DOWN to move up and down a screen at a time
  • CTRL+HOME/END to move to the first cell/last cell with data

CTRL+S

Save the current workbook. You can't use this often enough.


CTRL+N

Create a new workbook.


CTRL+F4

Close the current workbook.


CTRL+TAB

Cycle through open workbooks.


CTRL+PageUp/PageDown

Move through sheets in active workbook.


ALT+SHIFT+F1

Insert new worksheet


Cut/Copy/Paste

CTRL+X : Cut

CTRL+C : Copy

CTRL+V : Paste


CTRL+ALT+V

Open the Paste Special dialog box. You can then select the paste option you want.


CTRL+Z and CTRL+Y

Undo and Redo.


CTRL+T

Create a table. If you haven't discovered tables yet where have you been! Start using them now for all the benefits they bring.


CTRL+SHIFT+L

Toggle autofilters. Do ad-hoc analysis of your data by using auto-filters. Quickly turn them on and off with this shortcut.


F2

Edit the cell.


F4

Toggle absolute and relative references. Essential when writing formulas.


F4

Repeat last action.


ALT+F11

Open the VBA editor.


ALT+F8

Open the list of macros.


ESC

Cancel selection, cancel editing, cancel dialog box etc.


F3

Show names. If you have created named ranges, use F3 to see a list of them, then quickly insert one into your formula.


CTRL+1

Format. Use this to format cells, charts, all sorts of things.


Use the Quick Access Toolbar

You can customize the QAT and add commands to it that are accessible by using ALT+Num where Num is 1, 2, 3 etc.


How many of these do you use, and what other ones do you use regularly?

The post 23 Essential Excel Keyboard Shortcuts appeared first on My Online Training Hub.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Format your dashboards and reports fast with these pro Excel chart formatting tips.

Pro Tip 1 – Select Multiple: Hold the SHIFT or CTRL key to select/de-select multiple charts or objects.

Pro Tip 2Select All: Select one chart then press CTRL+A to select all. Note: This will select all Objects so if you have shapes or images in your worksheet it will select them as well.

With all charts selected you can move, resize, align, group, delete, copy, right-click and set properties including size, locking and more:

Pro Tip 3Snap to Grid: Hold down the ALT key while resizing and moving to snap to the grid:

Bonus tip: You can move and resize multiple charts at the same time.

Pro Tip 4Distribute Evenly: Select 3 or more charts > Format tab > Align > Distribute Vertically or Horizontally:

While you’re there you can also align them all left or right, or top/bottom.

Bonus tip: This is great for aligning any object, e.g. form controls, shapes etc.

Pro Tip 5 – Lock Alignment while Moving: Hold the SHIFT key while you left-click and drag to keep your chart aligned to its original horizontal or vertical position (useful it you’re not using the grid for alignment):

Pro Tip 6 – Repeat Formatting: Let’s say you decide that you want to remove the chart border from all your charts.

Make the formatting change to one chart, then select the next chart and press F4. Rinse and Repeat for remaining charts. This works for other formatting too.

Pro Tip 7 – Themes: Change all formatting in one go with Excel Themes. Choose from the built-in themes:

Or customize your own including colors, font styles and shape effects. Click here to learn how to use Excel Themes.

Pro Tip 8 – Duplicate/Copy Charts: Copy an existing chart with keyboard shortcut CTRL+D or left-click to select the outer edge of the chart > hold the CTRL key until the mouse pointer displays a + symbol, then left click and drag while holding CTRL.

Bonus tip: hold SHIFT at the same time to keep the new chart aligned to the one you’re copying:

Pro Tip 9 – Chart Templates: Got a chart you’ve spent considerable time formatting to just the way you like it and now use it all the time. Make it a chart template so it’s on call when you need.

Pro Tip 10 – Move Chart with Arrow Keys: Hold CTRL while left clicking the outer edge of your chart. The pull handles will be small dots which indicates that you can move the chart with your arrow keys:

Pro Tip 11 – Prevent Charts Resizing: By default, charts will resize and move when you adjust column width and row height, but you can prevent this in the Properties. Right-click the chart > Format Chart Area > Properties:

Pro Tip 12 – Don’t Use Built in Chart Styles: All but the default, Style 1, is generally a bad idea. They’re full of noise like unnecessary formatting and fill:

Pro Tip 13 – Select Chart Elements: Sometimes selecting the element you want can be tricky, like the ‘Small Values’ series in the chart below:

There are two options for selecting those teeny tiny chart elements:

  1. Select an element that’s easy to click on, like the ‘Big Values’ column > hold the CTRL key and press the up/down arrows to toggle through the other elements in the chart until you get to the one you want.
  2. Select the chart > Chart Tools: Format tab > choose the element from the drop down:

Pro Tip 14 – Copy All Formatting: If you want to copy the formatting from one chart to others, you can simply select the chart you want to copy formatting from > CTRL+C, then select the chart you want to copy formatting to > go to the Home tab > Paste > Paste Special > Formats:

Please Share

If you liked this please click the buttons below to share.

The post Excel Chart Formatting Tips appeared first on My Online Training Hub.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
Dumbbells and Lollipop Charts

Excel Dot Plots, dumbbells and lollipop charts are good for comparing one, two or three points of data. For example, year on year, before or after or A vs B.

They make a nice change from a column or bar chart (like the one below) and are less cluttered:

Column and bar charts also require the horizontal axis to begin at zero. This is because we instinctively compare the length of the columns/bars and make judgements based on the difference in size. If we don’t start the bar lengths at zero we can falsely exaggerate the difference and mislead our audience.

Take the following example where the horizontal axis starts at $600M:

Department A’s 2017 sales target appears to be double that of 2016, but in fact it’s only 23% more. Department C is even more misleading.

This is why we must always start bar and column chart axes at zero.

However, dot plots:

…and dumbbell charts (below), aren’t bound by this rule because the dots aren’t connected to the vertical axis base line:

And so, our eye isn’t drawn to make comparisons in the distance from the vertical axis. Instead we judge them based on the position along the horizontal axis.

This allows us to emphasise the difference between the dots, whether that be two points as in the dumbbell charts above, or the single points in the dot plot.

Lollipop Charts

Lollipop charts get their name from the leader line that draws your eye to the dot, and because of this I think you should start your axis at zero for the same reasons we do with bar and column charts:

Dot Plots, Dumbbells and Lollipops - Which is Best

All charts are useful, and your choice will depend on the points you want to emphasize:

Bar/Column Charts – quickly compare the size of one department to the next and compare from one period to the next within that department

Lollipop Charts – a less cluttered take on the bar chart. Make sure the axis starts at zero.

Dumbbell Charts – emphasize the change from one point to the next with some comparison between departments

Dot Plot Charts – allow comparison between departments with more emphasis on the difference

Download the workbook

Enter your email address below to download the sample workbook.

Get Workbook
By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel Workbook. Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download.
Building Excel Dot Plot Charts

Start with your data structured like so:

Tip: The spacing simply assigns each department to a row in your chart so they’re nicely vertically distributed. You can change the spacing to suit your needs.

  1. Select the data in columns B and C > Insert tab > Scatter Chart. It should look like this:
  2. Right-click the chart > Select Data > Edit the series name so it points to cell B71 that contains the year name:
 
  1. Edit the vertical axis (right-click > format or left-click > Ctrl+1) > Set the maximum to 2.5 (or to match the maximum spacing value in your data set).
  2. If you want to emphasize the difference between the dots you can set the horizontal axis minimum to something closer to the lowest value in your data set. I’ve set mine to $600M.
  3. Turn off the vertical axis (just select it and press DELETE).
  4. Turn off the vertical gridlines (select them and press DELETE). Optionally also turn off horizontal gridlines.
  5. Select the dots > CTRL+1 to format > Marker > Marker Options > set the marker options, type and size (see my settings below -# 1). Set the fill to white (#2 & #3) and make the border thicker (#4 & #5):
  6. Add labels > align them left and format them to display the X value. If you have Excel 2013 or Excel 2016 you can also include the Department names using the ‘Values From Cells’ reference as shown below:

Note: If you have an earlier version of Excel then you can use the technique described by Jon Peltier here to assign your department names to the vertical axis labels.

 
  1. If you want to keep the horizontal gridlines, then you can fill the labels with white (select labels > Format tab) so that the line doesn’t strike through the label:
  Excel Lollipop Charts

Lollipop charts require the same steps as the Dot Plot, but you delete the horizontal gridlines and replace them with error bars. To add error bars:

  1. Select the dots > Chart Tools > Design > Add Chart Element > Error Bars > More Error Bar Options
  2. Set the direction to Minus, End Style to No Cap and Error Amount is Percentage at 100%:
Excel Dumbbell Charts

Dumbbell Charts (sometimes called DNA charts), require the same steps as the Dot Plot. Then you simply add a second series:

  1. Right-click chart > Select Data > Add Legend Series
  2. Select the second set of data for the X series, in my case it’s 2016 data. The Y series are the Spacing values:
  3. Add error bars (note: the Error Bars are based on the difference between 2017 and 2016 and you can see the calculation in column E) – Select the 2016 dots in the chart > Chart Tools > Design tab > Error Bars > More Error Bar Options. This will open the Error Bar formatting dialog box or pane (shown below):
 
  1. Click on ‘Specify Value’ and select the Positive Error Values from the table:

Tip: If you have negative error values (like the example below), then you’ll also need to add a column to your table to calculate them and then reference those cells in the ‘Negative Error Value’ field shown in the dialog box above.

Be sure to download the Excel file to see the example above.

Camera Tool Alternative

If you’re familiar with Excel’s camera tool, then a quick and dirty way to create a dot plot is to insert a line chart with only markers and use the Camera tool to rotate it on it’s side.

However, often the image in the camera tool isn’t as crisp as you might like, and if you insert too many of them then Excel might have a tantrum and crash.

  References

Jon Peltier: https://peltiertech.com/dot-plots-microsoft-excel/

Naomi Robbins: http://www.b-eye-network.com/newsletters/ben/2468

Stephanie Evergreen: http://stephanieevergreen.com/easy-dot-plots-in-excel/

Please Share

If you liked this please click the buttons below to share.

The post Excel Dot Plot Charts appeared first on My Online Training Hub.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

My last post looked at using Python to plot equations in Excel with DataNitro.

In this one I want to look at writing UDFs in Python.with xlwings.


Why Use Python for UDFs ?

This is a question not just about UDFs but why use Python at all in Excel?

I guess this depends on what you are doing. VBA will be fine in a lot of (most) cases but if you want to take advantage of the numerous libraries for Python to perform things like scientific computing, machine learning, web scraping, and more, then go with Python.

Bear in mind that Python is cross platform but VBA is tied to MS Office. If you can write an Excel UDF in Python, then take that code, and with with some minor modifications, use it elsewhere like in SQL Server, that's useful.

Of course if you already know Python, and don't want to learn VBA, that's a valid reason too.

Installing xlwings

Before we go any further let's install xlwings.

The first thing you need is an installation of Python and xlwings recommends using a distribution like Anaconda which includes a lot of the extra libraries you'll need.

Note

xlwings is available for Excel 2016 on Mac and allows you to write macros in Python, but it does not support UDFs.

You can use xlwings without installing the add-in, but the files that need to be distributed for your workbook to work are much bigger.

Installing the add-in is the recommended way to go and that's what I did.

Once installed, you get a new section on your Ribbon

Next, in Excel, go into File -> Options -> Trust Center -> Trust Center Settings -> Macro Settings and check the box beside 'Trust access to the VBA project object model' and confirm the change.

Writing Python UDFs

Create a workbook using the Python command line method xlwings quickstart my_udf where my_udf is the name of your new workbook.

xlwings will create a new workbook called my_udf.xlsm and a Python file called my_udf.py.

You can now write your Python code in my_udf.py and import this to your workbook.

Sample Python UDFs

xlwings provide a number of sample UDFs and the syntax of a UDF is explained in their documentation.

I decided to write my own UDFs, none of them will make you gasp in awe. They're simply exercises in how to write a Python UDF in Excel.

The first one returns the cube of a number

The second returns a friendly greeting, based on two string arguments passed to it, which are the time of day, e.g. afternoon, and someone's name.

The third function queries a SQL database for some data and returns that data. This is a very basic SQL query and you can do a lot more powerful things than this.

Please note that the right hand edge of this image is truncated just so I can fit it onto the screen. The full code will be available to download towards the bottom of the post.

Getting the UDFs into Excel

Once you've written and saved the Python code, go back to Excel and from the xlwings area on the Ribbon, click on Import Functions

This will create some references to the functions in a VBA module called xlwings_udfs. If you go into the VBA editor (ALT + F11) and look for that module, you can see this code

Calling the UDFs

You use them just as you would with any other function in your sheet

Summing Up

It might seem a bit complicated to get this set up, but like a lot of things, once you've done it the first time, it's pretty straight forward to continue writing more Python UDFs.

Undoubtedly using Python for certain things has its advantages over VBA, and with the world seemingly scrambling to learn Python (and R) for data science and machine learning applications, why not start by writing your own Python UDFs?

File Downloads

Enter your email address below to download the Excel workbook and Python code with my UDFs.

Get Files
By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Excel Workbook my_udf.xlsm
Python UDFs my_udf.py

The post Writing UDFs in Excel With Python appeared first on My Online Training Hub.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

PivotTables can make quick work of summarising and analysing data and they have some handy built in percentage calculations available via the Show Values As menu.

The Excel PivotTable Percentage Change calculation is achieved with the % Difference From option and is useful for quickly identifying if this month/quarter/year is better or worse than last month/quarter/year.

Add in some Conditional Formatting to your PivotTable, and throw in a Slicer and we’ve got a super quick, visually appealing, interactive report at the click of a few buttons.

Let’s look at how to build the month on month percentage change PivotTable above.

Excel PivotTable Percentage Change

Step 1: Start with a regular PivotTable, and add the field you want the percentage change calculation based on, to the values area twice:

Step 2: Right-click any values cell in the Sum of Sales2 column > select Show Values As > % Difference From…:

Note to Excel 2007 users: The Show Values As options are in the Value Field Settings dialog box:

Tip: You don’t need the Sales field in the Values area twice to show the % Difference From. If you only want to show the percentage change and not the actual Sales amounts, then you can simply add the ‘Sales’ field to the Values area once and then set that field to % Difference From.. via the ‘Show values as…’ menu.

Step 3: In the Show Values As dialog box set the Base field to Month and the Base item to (previous):

Your PivotTable should now look like this:

Tip: Give the ‘Sum of Sales2’ field a better name. Simply type a new name in cell C3, making sure it’s not the same as any of the field names in your PivotTable source data. I’ll just call mine %. You’ll see why in a moment:

Add some Conditional Formatting

We can make the % change percentages easier to read with some Conditional Formatting visual indicators. I like to place these in a separate column, but if you’re happy for them to share column C then you can skip steps 4 and 5.

Step 4: For this we’ll need to add the ‘Sales’ field to the Values area again:

Step 5: Right-click the Sum of Sales2 column > Show Values As > % Difference From, and then same as before; Base field is Month and Base item is Previous.

Also give the column a new name. I’ll call mine ‘Change’, as you can see below:

Step 6: With any cell in the ‘Change’ column values area selected, go to the Home tab > Conditional Formatting > Icon sets. Here you can choose from different icons, but I’ll stick with the directional triangles:

This will apply the formatting to the selected cell.

Step 7: To apply the formatting to the whole ‘Change’ column, click the drop down beside the cell and select ‘All cells showing “Change” values for “Month”, as shown below:

It should now look like this:

Notice that there are some neutral/yellow icons. We want to change the formatting to simply show green up triangles for positive change and red down triangles for negative change.

Step 8: With any cell in the ‘Change’ column selected, go to the Home tab > Conditional Formatting > Manage Rules. This opens the Conditional Formatting Rule Manager dialog box (shown below). Select the icon set rule and click ‘Edit Rule’.

Tip: You can also double click the rule to open the rule editor window, shown below:

Edit the settings as shown in the image below. Note: I’m choosing to only show the icon in this column because column C already has the percentages displayed.

Your PivotTable should now look like this:

Tip: I’ve centered the Conditional Formatting icons using the cell alignment on the Home tab.

For the icing on top, add a Slicer and allow your user to interact with the PivotTable, as I’ve done for the Category field:

Tip: Just in case some months don’t have any data, I’ve set my Month Field settings to ‘show values with no data’ to ensure all months are listed:

Note: Months with no data will result in a #NULL! error for the % Difference From calculation. It’s not an issue here, but something to be aware of if you see #NULL! errors in your PivotTables.

Handling Errors in PivotTables

Now, obviously we don’t want our PivotTables littered with errors, especially if we’re presenting them in a report, that would just create unnecessary questions and we’re busy enough.

Thankfully we can supress errors in the PivotTable options; right-click the PivotTable > PivotTable Options > on the Layout & Format tab check the ‘For error values show’:

Download the Workbook

Enter your email address below to download the sample workbook.

Get Workbook
By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel Workbook. Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download.
Please Share

If you liked this please click the buttons below to share.

The post Excel PivotTable Percentage Change appeared first on My Online Training Hub.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Separate tags by commas
To access this feature, please upgrade your account.
Start your free year
Free Preview