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

]]>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 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:

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

Enter your email address below to download the sample 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.

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.

- Select the data in columns B and C > Insert tab > Scatter Chart. It should look like this:
- Right-click the chart > Select Data > Edit the series name so it points to cell B71 that contains the year name:

- 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).
- 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.
- Turn off the vertical axis (just select it and press DELETE).
- Turn off the vertical gridlines (select them and press DELETE). Optionally also turn off horizontal gridlines.
- 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):
- 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.

Or you can get Jon's Excel add-in that can create dot plots and more:

Peltier Tech Chart Utility – available for PC and Mac

- 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:

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:

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

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

- Right-click chart > Select Data > Add Legend Series
- Select the second set of data for the X series, in my case it’s 2016 data. The Y series are the Spacing values:
- 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):

- 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.

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.

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/

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

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

]]>The post Visualizing Parts to a Whole in Excel Charts appeared first on My Online Training Hub.

]]>Being outnumbered 3:1 by football (soccer) fans in my household, I get to see my fair share of “the beautiful game” on TV. To pass the time I often find myself looking at the match statistics posted on the screen.

Football stats typically focus on possession, shots, corners, fouls and other comparisons that often bear no correlation to the outcome of the game, although I’m not complaining as they help to pass the time

Using football as inspiration, let’s look at our options for visualizing parts to a whole data using this standard set of football statistics:

We’ll start with the obvious and most favored among data visualization novices; the pie chart.

If you know me then you’ll know that I’m not a fan of pies, unless they contain apple and are edible

That said, recently I’ve relaxed a little and used the occasional 2 or 3 segment (max) pie chart, but in doing so it only reinforces my reasons for disliking them.

My biggest gripe with pie charts, aside from when used poorly, is that they’re space hogs. Just take a look at the examples below (note: I would normally use colors from the football club logos in my charts, but the red is a bit harsh and the other colors clashed, so I abandoned them. If in doubt, go neutral.):

I even simplified them by extracting the legend and placing it at the top of the charts. After all, I don’t need to repeat it in each pie.

Sure, I could try to make them even smaller, but that’s just more fiddling about and I’m way too busy for that. I need my pies like fast food, ready to consume with one or two clicks.

The other downside of pies when used in multiples is they make it difficult to compare one metric to the next, not that it’s always relevant to do so, but if it was then pies make it hard work.

Doughnut charts aren’t any better. Like their namesake they're certainly not slimming; they're big, chunky, space hogs:

My preferred way to visualize this type of data is using a 100% stacked bar chart. You can also use column charts, but if your category axis labels are long then you’ll want to stick with bars.

- The main advantage of the bar chart is the ability to convey a lot of information in a very small space, which is essential when building Dashboards.
- If you’re wanting to compare multiple items, then the bar chart makes is a lot quicker.
- In Excel charts the bars are contained in one chart object, so if you want to make formatting changes you only have to do it once. For example, I could easily make those bars even smaller with a simple left click and drag of the corner.

However, if you or your boss insists on using pie or doughnut charts, then you should follow some rules:

- No more than 3 segments. Anymore and the comparison ability is lost, plus your reader will spend too much time trying to map the legend to the segments to understand the components.
- Make sure your segments are ordered from largest to smallest starting at 12 o’clock. You’ll notice that Excel doesn’t do this for you, you must sort your data. Case in point; the Tackles pie and doughnut charts.
- Don’t use labels on sticky-outy leader lines. If the labels don’t fit in the segments, use a different chart.

If you run into problems 1 or 3 above, use a clustered (not 100% stacked) bar chart instead.

Oh, and if you're ever tempted to create an exploding pie chart like this:

Don't! Instead consider a layered bar chart like this:

We're not limited to pies, doughnuts and bars for visualizing parts to a whole. Here are some other options available in Excel.

**Stacked area charts** – show data over time:

Although I think a good-old line chart is better for allowing comparisons in the data:

**Treemaps** (available in Excel 2016 Office 365) – use with hierarchical data

**Sunburst **(available in Excel 2016 Office 365) – also use with hierarchical data:

*Note**: Treemap and Sunburst charts aren’t available as Pivot Charts, yet!*

The issue I have with Treemaps and Sunbursts, aside from their sheer size, is that small segments are lost. If you’re not interested in the small segments then that’s ok, but if you want to know what they are, then use a bar chart:

Or a layered bar chart :

**Marimekko** – categorical data with two variables.

*Image credit; *www.peltiertech.com

There’s no built-in Excel chart type for the Marimekko, but Jon Peltier of Peltier Tech has an Excel add-in that can do it:

Peltier Tech Chart Utility – available for PC and Mac

**Labels** – don’t feel that you must always display proportions visually. If you only have one headline figure to highlight, why not just show the number in a label:

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.

Please enter a valid email address.

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

The post Visualizing Parts to a Whole in Excel Charts appeared first on My Online Training Hub.

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

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

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.

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.

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.

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.

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

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

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?

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

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.

]]>The post Plot an Equation in Excel Using Python appeared first on My Online Training Hub.

]]>I know there are ways to do this that involve producing a set of data points, and then using those to create a chart, but I want to be able to just say, here's my equation, plot it.

I turned to Python to do this as it has the capability to plot equations quite easily. The issue was how do I get Python to run in Excel?

There are a number of packages that allow you to run Python in Excel: xlwings, PyXLL, and DataNitro are three options.

xlwings is free and open source.

DataNitro offers a 30 day free trial, but will keep working after this. If you purchase one of their membership options you get added benefits like ongoing support and updates, and the ability to write UDF's in Python.

PyXLL also offer a 30 day free trial, and then paid options after that.

For this, I chose to look at DataNitro, but will look at the others in the future.

Python is a powerful, easy to learn programming language - I've started helping my 11 year old son learn it.

It is used in myriad fields like web and internet development, databases access, scientific and numeric work, and software and game development. That is a very short list of examples of what it is capable of.

These days it is mentioned a lot in relation to data science and machine learning. Microsoft have thrown their support behind Python with a product called Machine Learning (ML) Server that supports both Python and R. ML Server is built in to SQL Server 2017.

Visual Studio also has great support for developing in Python.

But let's not get too far ahead of ourselves. Microsoft's adoption of Python (and R) is fantastic and I for one feel like a kid in a candy store.

Unfortunately the documentation for DataNitro is a bit out of date and I had a few issues installing and getting it to work.

When I tried to run some Python scripts it complained that the NumPy library was missing. NumPy is a software package for Python that includes mathematical and scientific functions.

I knew it wasn't missing as I had Python already installed on my PC with NumPy and other libraries included. However, reinstalling NumPy fixed the issue.

Once it was up and running I really liked the way the thing works. DataNitro installs as an add-in in Excel and will appear on your Ribbon.

To run a Python script you first need to import the script to Excel. From the Data Nitro area of the Ribbon, click on the import icon, locate your script and then click on Run to, uh, run it

Once you've imported the script you can click Run to run it again, no need to import it more than once.

What I really wanted to do was to have a Python script where I could just write out my equation, and have Python plot it.

Here's the Python code, you can see I've written y = 3*x**2 + 4*x + 2 for my equation.

In Python if you want to raise a number/variable to a power e.g. x^{2}, you write it as x**2.

Here's the graph this produces, which is inserted as an image in the worksheet called polynomial.

I can click on the Editor icon in the Ribbon which allows me to edit the Python code. Changing and saving the code allows me to run that changed code right away in Excel.

If I change my equation to 2*x**3 - 3*x**2 + 4*x + 2, I get this

To plot a different equation it's as simple as changing one line of code and re-running the Python script.

So if I wanted to plot Sin(x) my equation is simply y = np.sin(x) and I get this plot

You'll notice that the peaks and troughs of the curve are a little jagged. This is because I've only specified 50 points along the length of the curve between the values -10 and 10.

x = np.linspace(-10, 10, 50)

If I increase the number of points (to 150) then the curve will look smoother

x = np.linspace(-10, 10, 150)

which gives me this

Let's say we want to plot a sine wave that decays over time. We'll use this Python code

The code that describes the decaying sine wave is s = np.sin(np.pi*t)*np.exp(-t*0.05) which means sin(π*t) * e^{(-t*0.05)}

The curve we get is

We can write the values we're plotting to the Excel sheet with a couple of lines of code. To see the values for t we write Cell("P2").vertical = t and for s we write Cell("Q2").vertical = s.

This will list the values for t in column P starting at P2. The values for s will be in column Q starting at Q2.

The code looks like this

I've only scratched the surface of what you can do with Python in Excel.

A couple of things that I was looking at but didn't include in this post are, given a fixed set of points, interpolating a curve to fit that data. In this plot the data points are blue and the interpolated points are orange.

and finding the roots of polynomials.

The plots you see here haven't had any fancy formatting, labels etc applied to them. But this is all possible. If you've never used Python, or just used it a bit, I encourage you to explore what it's capable of.

Or if you are an old hand at Python, try linking it to Excel and see what you can do.

Either way, if there's anything you'd like me to look at, let me know.

All the Python code I have used has either come from the DataNitro examples, or from the matplotlib site.

I have modified them and you can download these modified files, and the workbook I used below.

Don't forget you'll need to install something like DataNitro to be able to run the Python code.

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

By submitting your email address you agree that we can email you our Excel newsletter.

Please enter a valid email address.

You can download this post as a beautifully formatted PDF.

Just enter your email above and the download link will appear.

The post Plot an Equation in Excel Using Python appeared first on My Online Training Hub.

]]>The post Excel PivotTable Error Handling appeared first on My Online Training Hub.

]]>Thankfully Excel PivotTable error handling is easy to control via the PivotTable Options; right-click the PivotTable > PivotTable Options >

On the Layout & Format tab check the ‘For error values show’:

Instead of the error you can show text, like ‘N/A’, a number or leave it blank. I usually leave it blank, as you can see in the image below, because this is most likely to be suitable for all errors. Whereas if you put a value like 0 or 1, then that may not be correct in every scenario.

In the PivotTable below, column F; ‘% Change’ is a ‘Show Values As’ calculation for ‘% Difference From…’, which is uses the following formula:

=(current month – previous month) / previous month

When the ‘previous month’ is zero we get the #DIV/0! error, and when the current month has no data we get a #NULL! error, as you can see below:

**#DIV/0! **errors are triggered by dividing by zero. It’s the most common PivotTable error and is typically found in calculated fields or calculated items, or a calculation from the Show Values As options.

**#NULL!, **errors are triggered when a formula references an item that is blank. In the example above, you can see the source data for October is an empty cell (B15). You can fix this by replacing blank cells with zeros...but then you might end up with #DIV/! errors

**Blanks** – Notice Jan and Nov have blank results in the % Change column?

A blank is returned when the previous month is blank. And while this isn’t an error as such, some of you may prefer to see 100% or ∞, or some other value in there. After all, if you’ve gone from nothing to something (positive) then isn’t that an improvement?

Well yes, but that doesn’t mean you can represent the improvement in percentage terms.

From time to time someone will ask me how they can override these blanks and display 100% change when the prior period was zero.

The answer to this question is always, “I’m not telling”! Not because I’m being mean, but because it would be wrong to show the percentage change from zero as 100%, or even 0%.

I don’t just leave them hanging with “I’m not telling”, I told you I’m not mean. I go on to explain why, like so:

Let’s say yesterday you had $0 and today you have $10. If you were to say that today you have 100% more $ than yesterday, then you’d be saying that you still have $0 because $0 + (100% x $0) = $0, when in reality you now have $10.

Whereas if yesterday you had $5 and today you have $10, then you can say you have 100% more $ today because $5 + (100% x $5) = $10.

In other words, you can only calculate the percentage change from ‘something’, and zero is nothing. i.e. something is a number other than zero.

So, the accepted answer when calculating the change from zero is ‘not meaningful’ or leave it blank.

And before you think you can sneak 100% in the ‘For empty cells show’ field in the PivotTable Options, I’m happy to say that won’t work for the % Difference From empty fields.

Forward this tutorial to those you know who insist the percentage change from nothing to something is 100%?

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.

Please enter a valid email address.

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

The post Excel PivotTable Error Handling appeared first on My Online Training Hub.

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

]]>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.

**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:

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.

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.

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’:

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.

Please enter a valid email address.

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

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

]]>The post Excel 4.0 Macro Functions appeared first on My Online Training Hub.

]]>Even though these XLM macros are quite old they still work in Excel 2016. To use them you call them like a regular function e.g. =GET.CELL(64,A1) but entering this into a cell will give you an error.

What you need to do is combine them with defined names and then you can do some cool stuff.

You can do things that might normally require writing VBA, so if you don't fancy doing any coding check these out. Listing files in a folder, highlighting cells containing formulas, or getting the background color (ColorIndex) of a cell. Once you know a cell's background color, you could then do math with cells of the same color like sum, average etc.

Note: the last link to Jan Karel Pieterse site has other examples too.

The trouble I had was that I couldn't easily find any reference to the Excel 4 macros. Because they were written for such an old version of Excel, the help file for the macros is no longer a supported format as of Windows 10.

Even if you did have a Windows 7 or 8 PC you'd have to download an executable file from Microsoft, install this, then update Windows to allow you to open the help file. I know because I built a Windows 7 virtual machine on Azure and did all of this. It was just too hard.

So I decided to put together this 653 page reference eBook. It basically contains the official Microsoft Excel 4 macro functions help file, but in a useful PDF format.

A complete reference for all Excel 4 macros including syntax and examples.

Enter your email address below to download this free PDF.

By submitting your email address you agree that we can email you our Excel newsletter.

Please enter a valid email address.

You may not find all of these macros will be useful, but some certainly are.

This is provided for reference and if you are curious to see what you can do.

Don't go building mission critical models with these, support for them could be withdrawn in the future.

The post Excel 4.0 Macro Functions appeared first on My Online Training Hub.

]]>The post Extract Date or Time from a Date and Time appeared first on My Online Training Hub.

]]>We can use the INT function to extract the date portion, and the MOD function to extract the time portion.

See examples below.

The INT function returns the integer or whole number portion of the date time serial number. You can also use the TRUNC function with the same results, but that requires two more characters and we busy Excel users don't have time for that!

The Excel MOD function allows us to retain the decimal or time portion of the date-time serial number.

The syntax for MOD is:

=MOD(number, divisor)

Your date time serial number is the 'number' argument and the divisor is 1. This returns the decimal or time portion of the date-time serial number

Easy!

Learn more about how Excel handles dates and time in our comprehensive guide to working with Excel Date and Time, or download the files below.

Everything you need to know about Date and Time in Excel - Download the free eBook and Excel file with detailed instructions.

Enter your email address below to download the comprehensive Excel workbook and PDF.

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 and PDF. Note: This is a zip file including an Excel workbook with detailed instructions and a PDF version for your reference.

The post Extract Date or Time from a Date and Time appeared first on My Online Training Hub.

]]>The post Excel MOD Function appeared first on My Online Training Hub.

]]>Anyhow, just because I find MOD confusing, doesn’t mean it’s not one of the most useful and versatile functions available to us. And to be fair, it’s not a complicated function, but the description of what the MOD function does is as clear as mud, just take a look:

“MOD returns the remainder after a number is divided by a divisor.”

Unless you’ve heard of MOD before, it might as well read; blah, blah, blah, blah.

=MOD(number, divisor)

** Number** – the number to be divided

** Divisor** – the number to divide by

Let’s look at some MOD Function examples and you’ll (hopefully) see it’s actually quite simple:

I’ll explain the way I understand what MOD does. Let’s take the examples above, starting with row 5:

**Row 5**: 3 goes into 9, 3 times. There is nothing left, so MOD returns 0. In other words, 9 is divisible by 3. I can calculate the result of MOD like this:- 9 - (3 x 3) = 0

**Row 6**: 3 goes into 10, 3 times. There is 1 left. In other words, 10 is not divisible by 3. I can calculate the result of MOD like this:- 10 - (3 x 3) = 1

**Row 7**: 4 goes into 6, 1 time. There are 2 left. In other words, 6 is not divisible by 4. I can calculate the result of MOD like this:- 6 - (4 x 1) = 2

Good Things to Know about MOD:

- The result of MOD will always match sign of the divisor. Compare examples on rows 7:10.
- Using 2 as the divisor will always return 1 for odd ‘numbers’ and 0 for even ‘numbers’. See examples on rows 11:14. We can exploit this pattern and use it in formulas that use Boolean TRUE/FALSE arguments. For example, Conditional Formatting.
- We can use MOD with 1 as the divisor to return the decimal portion of a number. See row 15. This is handy for extracting the time from a date-time serial number.
- If the divisor is 0, MOD will return a #DIV/0! error.

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.

Please enter a valid email address.

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

The post Excel MOD Function appeared first on My Online Training Hub.

]]>The post Excel DATE Function appeared first on My Online Training Hub.

]]>Syntax: |
=DATE(year, month, day) |

year |
The value of the argument can include one to four digits from 1900 to 9999.year |

month |
A positive or negative integer representing the month of the year from 1 to 12 (January to December). |

day |
A positive or negative integer representing the day of the month from 1 to 31. |

The example below illustrates how you can gather day, month and year components from separate cells and use the DATE function to return a date serial number:

**Tip: **We can use the DATE function to add or subtract months or years to a date. See below.

**Tip:** We can also use the EDATE function to roll dates forward by a set number of months.

**Note: **If we add days and they exceed the number of days in the month, the DATE function will add the excess days to the start of the following month. See examples below.

**Tip 1: **The Year argument must be a positive value, but month and day can be negative! If ** month** is greater than 12,

**Tip 2: **If ** month** is less than 1,

Learn more about how Excel handles dates and time in our comprehensive guide to working with Excel Date and Time, or download the files below.

Everything you need to know about Date and Time in Excel - Download the free eBook and Excel file with detailed instructions.

Enter your email address below to download the comprehensive Excel workbook and PDF.

By submitting your email address you agree that we can email you our Excel newsletter.

Please enter a valid email address.

The post Excel DATE Function appeared first on My Online Training Hub.

]]>