Loading...

Follow My Online Training Hub on Feedspot

Continue with Google
Continue with Facebook
or

Valid

There are many Excel Calendar templates available and if you look at them closely, you’ll see they have some mind-boggling date formulas.

Adam, one of our members, sent me in a calendar he has been using for 10+ years and asked if I could explain how some of the formulas worked. The original source of the calendar appears to be a Microsoft Template (I use a similar one), but Adam has enhanced it by adding a table for holiday dates that conditionally format the calendar cells with a fill colour.

The calendar allows you to choose the year in cell A3 which updates the calendar and holiday dates accordingly:

Download Workbook

Download the Excel file and follow along:

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

There are 3 main formulas that generate the calendar:

Formula 1: Month Sundays

There are 12 named formulas (JanSun1, FebSun1 etc.)  that find the first day of each month, minus the weekday number of that date, plus 1. You can see them in the name manager:

September and December are the only two months in 2019 where the first of the month is a Sunday. Let’s look at the SepSun1 named formula more closely:

=DATE('Any Year Calendar'!$A$3,9,1) - WEEKDAY(DATE('Any Year Calendar'!$A$3,9,1))+1

Which evaluates as follows:

=DATE(2019,9,1) - WEEKDAY(DATE(2019,9,1)) +1
=September 1, 2019 - WEEKDAY(September 1, 2019) +1
=September 1, 2019 - 1 + 1
=September 1, 2019
English Translation

Remember, cell A3 contains the year, which currently has 2019 selected. Therefore, the DATE function simply returns the date; September 1, 2019. The WEEKDAY function returns a 1 if the date returned by DATE is a Sunday, whereas if the day is Monday it will return a 2 and so on. September 1, 2019 is a Sunday. +1 is then added to the result.

If we take January’s named formula, JanSun1 as another example we get the following:

=DATE('Any Year Calendar'!$A$3,1,1) - WEEKDAY(DATE('Any Year Calendar'!$A$3,1,1)) +1
=DATE(2019,1,1) - WEEKDAY(DATE(2019,1,1)) +1
=January 1, 2019 - WEEKDAY(January 1, 2019) +1
=January 1, 2019 - 3 +1
=December 30, 2018

January 1 is a Tuesday, which is the third day in the week according to the WEEKDAY function.

Formula 2: Calendar Formulas

Let’s set those named formulas aside for a moment and look at the next formula in the calendar cells. We’ll call these the Calendar Formulas.

Looking at cell C6 in the image below you can see the calendar formula uses the IF function and references the JanSun1 named formula.

The objective of these formulas is to determine what day of the week each date falls on. Starting in the first cell (C6) it tests if JanSun1 returns the first of the month, if true it would return the result of JanSun1, otherwise it returns a blank as denoted by "":

Let’s step through the formula:

=IF(AND(YEAR(JanSun1) = $A$3, MONTH(JanSun1) = 1), JanSun1, "")

Looking at the JanSun1 named formula first, it evaluates as follows:

=DATE('Any Year Calendar'!$A$3,1,1) - WEEKDAY(DATE('Any Year Calendar'!$A$3,1,1)) +1
=January 1, 2019 - WEEKDAY(January 1, 2019) +1
=January 1, 2019 - 3 +1
=December 30, 2018

When we insert the JanSun1 result into the IF formula we get:

=IF(AND(YEAR(December 30, 2018) = $A$3, MONTH(December 30, 2018) = 1), December 30, 2018, "")

Which evaluates as follows:

=IF(AND(2018 = 2019, 12 = 1), December 30, 2018, "")
=IF(AND(FALSE, FALSE), December 30, 2018, "")
="" i.e. blank

And we can see that a blank has been returned in the calendar image above.

Looking at the next cell, D6 for Monday, the formula is:

=IF(AND(YEAR(JanSun1+1)=$A$3,MONTH(JanSun1+1)=1),JanSun1+1, "")

Notice the formula is the same as the formula in cell C6 except it has +1 added to the JanSun1 result, as shown in the formula bar of the image below:

Let’s step through how it evaluates:

=IF(AND(YEAR(December 30,2018+1)=$A$3,MONTH(December 30,2018+1)=1), December 30,2018+1, "")

Note: The +1 adds one day to the date, as shown below.

=IF(AND(YEAR(December 31,2018)=$A$3,MONTH(December 31,2018)=1), December 31,2018, "")
=IF(AND(2018=2019, 12=1), December 31,2018, "")
=IF(AND(FALSE, FALSE), December 31,2018, "")
=""

And lastly, let’s look at the formula in cell E6 for Tuesday:

=IF(AND(YEAR(JanSun1+2)=$A$3,MONTH(JanSun1+2)=1),JanSun1+2, "")

Notice the formula is now adding 2 days to the result of JanSun1, as shown in the image below:

It evaluates like so:

=IF(AND(YEAR(December 30,2018+2)=$A$3,MONTH(December 30,2018+2)=1), December 30,2018+2, "")
=IF(AND(YEAR(January 1,2019)=$A$3,MONTH(January 1,2019)=1), January 1,2019, "")
=IF(AND(2019=2019, 1=1), January 1,2019, "")
=IF(AND(TRUE, TRUE), January 1,2019, "")
= January 1,2019

Finally, we have a date that returns TRUE for the year and month logical tests.

Tip: The date returned by the formula has a custom number format applied that only displays the day portion of the date:

The formula in each subsequent cell has an extra day added to the JanSun1 result. For example, cell F6 for Wednesday contains this formula:

=IF(AND(YEAR(JanSun1+3)=$A$3,MONTH(JanSun1+3)=1),JanSun1+3, "")

If you inspect the formulas in the cells for the other months, you’ll see they follow the same pattern and use a named formula specific to that month. For example, here is February’s formula in cell C16:

=IF(AND(YEAR(FebSun1)=$A$3,MONTH(FebSun1)=2),FebSun1, "")

Tip: The Month Sundays named formulas (JanSun1, FebSun1 etc.) are used to simplify the IF formulas, but they could just as easily be inserted in the IF formulas instead e.g. this formula (with line breaks so it’s easier to see the pattern):

=IF(
AND(
YEAR(
JanSun1) = $A$3,
MONTH(
JanSun1) = 1),
JanSun1, "")

Is the same as this formula:

=IF(
AND(
YEAR(
DATE('Any Year Calendar'!$A$3,1,1) - WEEKDAY(DATE('Any Year Calendar'!$A$3,1,1)) +1) = $A$3, MONTH(
DATE('Any Year Calendar'!$A$3,1,1) - WEEKDAY(DATE('Any Year Calendar'!$A$3,1,1)) +1) = 1), DATE('Any Year Calendar'!$A$3,1,1) - WEEKDAY(DATE('Any Year Calendar'!$A$3,1,1)) +1, "")

I think you’ll agree it’s more succinct to use the named formulas. Named formulas can also yield efficiency improvements, although it’s not an issue in this file.

Formula 3: Finding Holiday Dates

The other clever formulas in this file derive the holiday dates, many of which fall on different days depending on the year. You’ll see them in column AK of the HolidayDates table in the file.

If we take Mother’s Day as an example, in some countries it always falls on the second Sunday in May. To find the date of the second Sunday in May in any give year, you can use this formula:

=DATE(A3,5,1)+7+CHOOSE(WEEKDAY(DATE(A3,5,1)),0,6,5,4,3,2,1)

Where cell A3 contains the year, which is currently set to 2019.

It evaluates like so:

=DATE(A3,5,1)+7+CHOOSE(WEEKDAY(DATE(A3,5,1)),0,6,5,4,3,2,1)
=May 1, 2019 +7+CHOOSE(WEEKDAY(May 1, 2019),0,6,5,4,3,2,1)
=May 8, 2019 +CHOOSE(4,0,6,5,4,3,2,1)
=May 8, 2019 + 4
=May 12, 2019

The logic here is that the second Sunday will be at least 7 days after the first of the month, hence the +7 in the formula.

WEEKDAY then tells us what day number the first of the month falls on. In this case it’s a Wednesday, which is the 4th day of the week. We pass this value to the CHOOSE function, which returns 4 i.e. the 4th item in the list {0,6,5,4,3,2,1}. i.e. Sunday is 4 days after Wednesday, which takes it to May 12.

The image below illustrates the steps taken in the formula:

Conditional Formatting

The Holiday Dates in column AK are then used in Conditional Formatting rules to automatically set fill colour in the calendar cells where they are the same (duplicates) as the dates in the ‘HolidayDates’ table.

Thanks

A big thank you to Adam for sharing the calendar file and inspiration for this post.

The post Excel Calendar Template Date Formulas Explained appeared first on My Online Training Hub.

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

One of my bugbears is opening an Excel workbook that has Split Panes set when it should have Freeze Panes set. They’re easily confused, so I thought I’d explain the difference.

Excel Freeze Panes

Freeze Panes is used when you want to fix the top row(s) and or left most column(s) in place and you still have plenty of room to scroll through the remaining columns and rows. In the image below I’ve used Freeze Panes on columns A through D and row 1:

You can see the effect in the animated image below:

Excel Split Panes

Whereas Split Panes is typically used when you have a lot of columns on the left or rows on the top that you want to remain visible and you can’t freeze them all in place and still have room to scroll through the columns to the right or rows below.

In the image below, Split Panes are at row 8 and column D:

Notice there are two horizontal scroll bars on the bottom of the screen. There are also two vertical scroll bars out of view. This allows you to scroll the panes independently of one another as shown in the animated image below:

Because each pane is considered a separate view of the same sheet if you scroll to the top left you end up with the same view in each pane, as shown below. This can be super annoying, which is why I rarely use Split Panes, and much prefer Freeze Panes.

Unfortunately, you can’t split panes and freeze panes at the same time.

Setting Up Freeze Panes

You’ll find the Freeze Panes tools on the View tab of the ribbon:

You can either freeze the top row or the fist column, or you can select a cell that’s below and to the right of the rows and columns you want to freeze, and then select ‘Freeze Panes’.

To turn off Freeze Panes go back to the View tab > Freeze Panes > Unfreeze Panes:

Setting Up Split Panes

Split panes requires you to specify where you want the split inserted by selecting the cell below and to the right of the split location. Then on the View tab > Split:

You can adjust the location of the split with your mouse. Position the cursor over the split line until the double headed arrow appears, then left click and drag to adjust it.

To remove the split, either go back to the View tab and click the Split icon, or position your mouse over the Split lines until a 4 headed arrow appears, then double click:

Tip: You can remove a single split line by double clicking the line when the double headed arrow appears.

The post Excel Freeze Panes vs Split Panes appeared first on My Online Training Hub.

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

I've written about web scraping before using VBA, but never about interacting with the website to do things like filling in forms.

We had a forum question asking how to do exactly that so I tried using the same approach as I had previously with the HTML Object Library, but when it came to grabbing elements from the web page, the results were inconsistent. Sometimes I'd get what I wanted, sometimes not.


Then I remembered Selenium which is software that automates browsers.

You can write code that instructs Selenium to do things like open a web page, fill in a form, or click a button, and it's really easy to use.

You can use it to automate browsers like IE, Firefox, Chrome, Safari and Opera and it works with many programming languages such as (but not limited to) C#, JavaScript, Perl, PHP, Python, Java and R.

To use it with Excel you need to use the SeleniumBasic library which allows you to drive Selenium with VBA.

SeleniumBasic supports a smaller range of browsers than the full Selenium implementation, I chose to use Chrome. Please note that up to date versions of Firefox are not supported, and you will need to downgrade to a much older version of FF if you really want to use it.

Setting Up SeleniumBasic and Chrome
  • Download and install the latest release of SeleniumBasic
  • Download and install ChromeDriver
  • Create a reference in your VBA project to the SeleniumBasic library
Installing SeleniumBasic

You can download SeleniumBasic from GitHub.

Run the .exe file and follow the instructions.

Installing ChromeDriver

Get the latest stable release from here.

Clicking on the stable release link under Downloads takes you to this high-tech looking site.

Download and unzip the version that works with your operating system. I'm running Windows 10 Pro 64-bit so the win32 file is the one I want.

Unzipping will give you just one file chromedriver.exe, you need to copy this to the folder where SeleniumBasic was installed. In doing this you will be copying over the version of chromedriver.exe that came with SeleniumBasic.

In my case the installation location was C:\Users\pgt\AppData\Local\SeleniumBasic. It will be different for you and will depend on what version of Windows (or other OS) you are using.

Creating a Reference to the SeleniumBasic Library

With your new Excel workbook open go into the VBA editor (ALT+F11) and select the workbook.

From the menu, go to Tools->References, find Selenium Type Library and check the box beside it. Then click the OK button.

You can now write VBA that accesses Selenium.

Practical Use - Filling in Forms and Looking Up Multiple Records

Let's say we need to look up VAT (Value Added Tax) records for companies from Great Britain. We can do this from this website http://ec.europa.eu/taxation_customs/vies/

Choose a Member State from the dropdown, so 'GB-United Kingdom' in this instance.

Then enter the VAT Number, and click on Verify.

The result gives us various information, but we are only interested in the company name, and need to extract that from the web page and put it into Excel.

We have a list of VAT numbers on our sheet and want to automate the process of looking up the company name associated with each one.

Interacting With A Web Page

In order to do something like fill in a form or extract data from a web page, we need to understand the structure of the web page.

We can use the Developer Tools in the browser to inspect the underlying HTML that constructs the page.

In Chrome you can either type CTRL+SHIFT+I or right click on the page and click on Inspect. Other browsers are similar, right click on the web page in those browsers to bring up the menu that will give you access to the Dev Tools/Inspector.

This will open a pane at the bottom of the browser like this

As we are interested in entering the Member State and VAT Number we need to know how to find those things in the HTML.

If you put your mouse pointer over the Member State dropdown and right click, then click Inspect, the Inspector window will highlight the HTML that creates this dropdown.

Notice that the highlighted element has an id countryCombobox. We'll need that later.

If we right click on the top most VAT number box, and Inspect that you'll find it has an id called number. We'll remember that for later too.

The last thing we need to look at on this page is the Verify button. Right click it and Inspect and you'll see it has an id in HTML called submit. Store that id for later use too.

If we now look at the results page, we are interested in the company name. Right click it, Inspect and you'll find where it is located in the HTML.

Notice here that the element storing the company name doesn't have an id, so we'll have to use another way to identify it in our VBA so we can get the name into Excel.

We can do this using a method called FindElementByXPath.

Read more about using XPath in Selenium.

What this allows you to do is to refer to a HTML element based on its position within the HTML. You can use various tag names or attributes to indicate what piece of information you want.

In this case, we are after some text in a HTML table. Inside the table is a tag called <tbody>, which contains several <tr> tags.

The company name we want is located in the 2nd <td> tag in the 6th <tr> tag.

We can describe the location of the company name like this //table/tbody/tr[6]/td[2]. Note that there is only 1 table in the results page HTML. If there were more I'd have to specify which table I wanted by using, for example, table[1] etc.

Now we know how to access the parts of the HTML pages we need, we can write our VBA.

VBA To Drive Selenium

Start by declaring and creating the Selenium driver.

I'm using a WHILE loop to work my way down Column A and read off the VAT numbers one by one. I'm using a variable called count to keep track of what row I'm on.

When the cell has 0 length (there's no VAT number in it), the WHILE loop ends.

Next the code tells Selenium to Get (load) the website.

The next 3 lines use a method called FindElementById to interact with the bits of the web page we found earlier using the Inspector.

Using the SendKeys method you can send keystrokes or text to the selected element.

So I send the country code GB, then the VAT number read from Column A of the sheet.

Finally the code Clicks on the submit element (the Verify button).

The results page will now load and we can get the company name with this line, and store it in a cell in Column B. So if count has reached 3, we store the company name in B3.

Repeat the process until all the VAT numbers have been checked.

Download the Example Workbook

Get a copy of the code used in this post.

Enter your email address below to download the 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 workbook. Note: This is a .xlsm file. Please ensure your browser doesn't change the file extension on download.

Summary

Once you get everything installed, using Selenium is pretty easy. I've used it to fill in just a couple of things in a form, but it would not be hard to expand on this to complete more complicated forms, even over several pages.

The post Web Scraping – Filling in Forms appeared first on My Online Training Hub.

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

Previously we looked at scraping data from the web by example, where the website paginated the data. That is, it spread the results over multiple pages. This week I’m going to show you how you can automatically scrape data from multiple web pages with Power Query using a custom function.

This approach works with URLs that have a predictable structure. Looking at the URL from the WorldCat website used in the previous post we can see in the image below there is a reference for the ‘start’ number highlighted in yellow:

This start number is referring to the first record of 10 returned on the page. As we move through the pages of results the start number in the URL increments by 10.

Note: Not all URLs will follow this pattern. Some might use a sequential numbering system that refers to the page number, others might use text strings that relate to different regions etc. Either way, the process is virtually the same.

Steps for Automating Scraping of Data from Multiple Web Pages

There are 4 steps required to scrape data from multiple web pages with Power Query. We did the first step in the previous post where we created a query that returns the first page of results.

In this post we’re going to look at the next 3 steps:

  1. Convert the query to a function that can be executed for each page of results
  2. Generate a list of page start numbers
  3. Execute the function for each page start number
Step 1: Convert Query to a Function

Starting off where we left the last example, we need to go to the Query Editor Home tab and open the Advanced Editor. Here we see the M code Power Query wrote for us.

We’re only interested in the first row that contains the URL for the web page. In this URL is the start number of the first record being returned on the page. You can see it magnified in the image below:

Each page returns 10 results, therefore the URL for the second page will have a start number of 11, and page 3 will have a start number of 21 and so on. We need to replace this hard-keyed value with a variable and convert the query to a function.

To convert the query to a function we add a line of code at the top for the variable name:

(StartPage as text) =>

I’ve called the variable ‘PageStart’. Then we replace the 1 in the URL with the variable name. Notice you need to append it with a double quote and ampersand on either side:

Important: Power Query is case sensitive therefore you must enter your variable name with the case that matches the variable name. e.g. pagestart would return an error.

When you click ‘Close’ on the Advanced Editor you’ll see that the query is now converted to a function. I like to give my functions a name prefixed with ‘fn’ to differentiate them from other queries, as you can see in the Properties below:

Step 2: Generate Page Start Numbers

For this website we need a series of numbers for the variable which we’ll generate using Power Query, but other websites might use text strings. If so, you can import a table that contains your list of text string variables.

Ok, we’ll create a new query to automatically generate the list of page start numbers. Right-click in the queries pane > New Query > Blank Query:

In the formula bar we’ll generate a list of numbers 1 through to 21610 with the following formula:

= {1..21610}

This will return a list that we can convert to a table: List Tools Transform tab > To Table:

Add a Modulo column to detect every 10th number in the list (remember we only need the page start number and there are 10 records per page):

Filter the Modulo column for rows containing 1:

This will leave us with a list of PageStart numbers in Column1 that we need for our variable:

The Modulo column has done its job, so you can click on the column header and press the Delete key to get rid of it.

Now, double click the header for ‘Column1’ and give it a proper name. I called mine ‘PageStartNumbers’.

We now have 2,161 page start numbers! Loading 2,161 web pages to scrape their data will take a very long time, so for the purpose of this example I’m going to filter them to the first 20 with a Filter for numbers less than 200:

Next, I need to change the data type for the PageStartNumbers column to text, as it’ll be inserted into the URL, which is a text string. Click on the ABC123 icon in the left of the column header > Text:

Now we’re ready to invoke the custom function we created in step 1. Step 3: Invoke Custom Function Add Column tab > Invoke Custom Function:

At the Invoke Custom Function dialog box select the function from the Function query list and the PageStartNumbers column for the PageStart variable:

Now we can expand the columns: click on the double headed arrow in the fnEysenckBooks column > Expand. Be sure to deselect ‘Use original column name as prefix’:

You should see a list of books with title, author, type and publisher information.

You can delete the ‘PageStartNumbers’ column as this is no longer needed.

One of the great things about Power Query is that it maintains a connection to the web page which enables you to refresh the connection and get updates as needed. Download Power BI Desktop 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 Power BI Desktop File. Note: This is a .pbix file please ensure your browser doesn't change the file extension on download.
Learn Power Query

If you’d like to learn more of Power Query’s wonders, please consider my Power Query Course. Or check out what Power BI has to offer, in my Power BI Course.

The post Scrape Data from Multiple Web Pages with Power Query appeared first on My Online Training Hub.

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

Getting data from the web is possible with Power Query in both Excel and Power BI. However, Excel is a little behind in Power Query features, therefore in this post I’m going to show you a new feature available in Power BI Desktop using Power Query to get data from the web by example.

In a few months we should see this feature available in Power Query for Excel.

Getting data using ‘Web by Example’ is super handy for data on web pages that aren’t structured in HTML tables, or where the structure is messy, like this book catalogue site, WorldCat:

From this site I want to extract a list of books by, or about author H J Eysenck. The website allows me to enter a search term that filters the data. You can see in the image above it has returned 21,610 results with the first page showing just 10.

Here is the URL if you want to take a look at the page: https://www.worldcat.org/search?q=Eysenck&fq=&dblist=638&start=1&qt=previous_page

The first challenge is getting the following data from the web page:

  • Book Title
  • Author
  • Type
  • Publisher

It’s a challenge because the data on the page is not formatted in a proper HTML table that Power Query can easily find.

Power Query Get Data from Web by Example Step 1: Connect to the web page

Let’s start in Power BI Desktop -  Home tab > Get Data > Web:

Step 2: Enter the URL

At the ‘From Web’ dialog box, enter the web page URL:

Tip: At the privacy setting choose ‘Anonymous’.

Step 3: Provide Examples

At the Navigator dialog box select ‘Add table using examples’:

Then in the bottom half of the window enter in the column labels you want and provide examples until Power Query detects the pattern:

Step 4: Edit or Load

The navigator dialog box will now have a Custom Table and you can click ‘Load’ if you’re ready to load it to your model, or ‘Edit’ to perform further transformations to the data in the Power Query editor:

The result is a table containing the columns you specified:

I know what you’re thinking…and no, you can’t copy the M code from the Advanced Editor in Power BI Desktop and paste it into Excel because Excel doesn’t yet recognise the functions Web.BrowserContents and Html.Table. Excel’s Power Query typically sees updates a few months after they are ‘generally available’ in Power BI Desktop.

The Second Challenge – Getting Data from Multiple Web Pages

Remember the first web page only returned 10 results. There were a further 21,600 results available, spread over a further 2,160 pages! In the next tutorial we’ll look at how we can automate pagination to get all results.

Download Power BI Desktop 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 Power BI Desktop File. Note: This is a .pbix file please ensure your browser doesn't change the file extension on download.
Learn Power Query

If you’d like to learn more of Power Query’s wonders, please consider my Power Query Course. Or check out what Power BI has to offer, in my Power BI Course.

The post Power Query Get Data from Web by Example appeared first on My Online Training Hub.

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

If you work with PivotTables, then you’ve probably found that you can’t include grand totals in Pivot Charts, or subtotals for that matter.  And if you’ve ever created a stacked column chart then you’ll have likely wanted to include grand totals as column labels, like this:

And then remembered you can’t.

One workaround is to create a regular chart from a PivotTable, then you can include the Grand Totals in the source data range.

Another option is to use CUBE functions to connect to the PivotTable source data. The nice thing about CUBE functions is you can get the PivotTable to create them for you and they can retain connectivity to Slicers. That’s right, you don’t even need to learn how to write CUBE formulas!

My data, shown below, is formatted in an Excel table called, Table1. It’s product sales by date and region:

Download Workbook

Download the workbook and follow along:

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.
Automatically Create CUBE Formulas

The key to CUBE formulas is that your data needs to be referenced from Power Pivot*, aka the Data Model.

*Power Pivot is available in for Excel 2010, Excel 2013/2016 Office Professional Plus, Office 2016 Professional, any version of Excel 2019 or Office 365, or the standalone edition of Excel 2013/2016. Click here for the full list.

Step 1: Load data to the Data Model

Insert a new PivotTable and at the dialog box check the ‘add this data to the Data Model’ box:

Note: Excel 2010 users will need to load the data to Power Pivot via the Power Pivot tab > Add Linked Table. Then from the Power Pivot window Home tab > Insert PivotTable.

Step 2: Build the PivotTable

Create the PivotTable that will support your Pivot Chart. I like to insert a chart at the same time to make sure the PivotTable layout is going to result in a chart that looks the way I want.

I’m using a stacked column chart, therefore I need the series names in the column labels and the dates in the rows, so they form my horizontal axis labels:

Tip: My dates are grouped into years and months: to do this, right-click the date in the PivotTable > Group.

Optional: If you’d like to filter your chart with a Slicer, insert it now. I’ve inserted a Slicer for the Region field. More on this in step 6.

Step 3: Convert the PivotTable to CUBE Formulas

Select any cell in the PivotTable > PivotTable Analyze tab > OLAP Tools > Convert to Formulas:

If you inspect the cells in what was your PivotTable, you’ll see they’re now CUBE formulas, as shown below:

The CUBE formulas are directly referencing the Data Model. Any changes to the Power Pivot Data Model will be reflected in the CUBE formulas.

Step 4: Insert a Regular Chart

Now that you’ve converted the PivotTable to CUBE formulas you can insert a regular chart i.e. not a PivotChart. I’m using a stacked column chart.

Step 5: Format the Chart

The Grand Total value is the top segment of the stacked column chart. We need to hide this, but first let’s select the grand total series and add Data Labels > Inside Base:

Next, with the grand total series still selected go to the Format tab > Shape Fill > No Fill

Hide the gridlines and vertical axis, and place the legend at the top (be sure to delete the legend entry for ‘Grand Total’; select it in the legend and press the Delete key):

Step 6: Connect CUBE Formulas to Slicers (Optional)

My data set allows me to filter the data by region and I’m going to do this with the Slicer I inserted in step 2.

First, right-click the Slicer > Slicer Settings and find its ‘name to use in formulas’:

Now, edit the CUBE formulas in the values area to include the Slicer ‘name to use in formulas’ in the next ‘member_expression’ argument, like so:

=CUBEVALUE("ThisWorkbookDataModel",$F$17,$G19,H$18,Slicer_Region)

Be sure to copy the formula to all the values area cells:

Step 7: Format and Arrange Slicer and Chart

Now all that’s left is to align the Slicer to the chart and make it look nice:

Notice the Slicer acts as a header for the chart and informs the user what regions it relates to without the need for an extra heading.

Tip: This CUBE formula technique also works with PivotTables based on an OLAP data source like SSAS Cubes.

Learn Power Pivot

Power Pivot is hugely versatile and enables you to work with a lot of data and or, data spread across multiple tables. If you’d like to learn Power Pivot please consider my Power Pivot course. There is more information on what Power Pivot can do on the course page linked to above.

The post Include Grand Totals in Pivot Charts appeared first on My Online Training Hub.

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
My Online Training Hub by Philip Treacy - 1M ago

One of our most popular blog posts is Excel IF AND OR Functions Explained which has over 800 comments at time of writing.

The vast majority of questions are along the lines of

I want to multiply a value by a percentage. In some cases, we need to enter the word "Special" instead of the calculation.

I am getting the #Value! error, I am not sure if this is correct:

=IF(OR(D4*I8), "SPECIAL")

So I want it to enter the total of the multiplication from D4*I18 in cell D13, or if the cell has the word "Special" in it, I want it to show Special in D13 instead.

This is a basic problem that is easily solved using IF and OR. And that's cool, I'm not here to make fun of a lack of knowledge. Quite the opposite. We try to help people to learn so I thought how can I make it easier for people to learn how to use the IF function?

So I came up with this IF Formula Builder.

By entering a test and the results you want for this test, my workbook builds the IF formula for you.

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 IF Formula Builder Workbook. Note: This is a .xlsm file please ensure your browser doesn't change the file extension on download.

Let's start with a basic IF and look at the syntax.

=IF(Test,Result_If_True,Result_If_False)

With some real values this looks like

=IF(A1>10,True,False)

What this means is IF A1>10 then return the Boolean value True. Otherwise return the Boolean value False.

More examples:

=IF(A1>10,A1,A2)

IF A1>10 then return the value in cell A1. Otherwise return the value in cell A2.

=IF(SUM(A1:A5)10,5,"Cool")

IF the value in A1>10 then return the number 5. Otherwise return the string "Cool".

Building an IF Formula

Open the IF Builder workbook and on the sheet you will see this

There are sections where you can enter your test, then specify the result you want when the test is true, and when it is false.

Your formula is then created for you.

You can choose the operator for the test from a data validation list, which is using mathematical comparison operators.

The test doesn't have to be something simple. Let's try the result of another function as our test, and I'll use strings as the True and False results of the test.

Why not go the whole hog and use functions as the test and the True and False results.

Getting Your Formula

Once you've built your formula, you can move it into your workbook using copy/paste special.

  1. Copy the formula
  2. Right click, Paste Special->Values
  3. With the new formula cell selected, press F2
  4. Press Enter

IF AND OR

We get a lot of questions about using IF with AND and OR. I think the problems I see people having with the functions AND and OR are because these functions do not follow the same logic/structure as an English sentence.

In English you would say IF (A1 > 10) AND (A2 > 50) but in Excel you must write IF (AND (A1 > 10, A2 > 50)).

Once you understand this and treat AND and OR as functions rather than a conjunction to join two parts (or more) of a sentence, it becomes easy.

On the sheet IF AND OR in my workbook you will find the IF AND OR function builder.

Nested IF Formulas

This is where it can get very messy. If you need to nest more than 3 IF's, you're probably better off using something like VLOOKUP.

But if you have just a few IF functions, nesting them is ok.

Where people go wrong here is getting confused with what to enter for the True and False results, and the way Excel displays the formula as you enter it does not help.

I get confused myself sometimes trying to work out how many closing parentheses I need.

What you are doing with a nested IF is saying, 'I have a number of different inputs, and for each one, I have a different output'.

If we use real values, we could represent different inputs and outputs by this table:

InputOutput
Ice CreamCold
CandySweet
PopFizzy
AppleHealthy

Or in psuedo-code

    If (Ice Cream THEN Cold) 
    ELSE
    If (Candy THEN Sweet) 
    ELSE
    If (Pop THEN Fizzy) 
    ELSE
    Healthy

Notice that we don't need to explicitly test for the last input. If we have already tested for 3 of the 4 possible inputs and haven't yet found a match, then the final input must be the only remaining one : Apple, so the output must be Healthy.

You could visualize this in a diagram like so

Nested IF Builder

Looking at the Nested IF Builder we can construct the IF formula like this

I've only written the Nested IF builder so that we are replacing the False results with another IF. There's nothing to stop you replacing the True result with an IF, or using some other function for the True or False results.

If you didn't already, hopefully now you'll be able to understand how to do this yourself.

Note

I've used an old Excel 4 macro EVALUATE to calculate the result of the constructed formulae. I was trying to avoid using VBA. If you have any issues with EVALUATE not working for you, let me know and I'll work on a VBA version of the IF Builder workbook.

The post IF Formula Builder appeared first on My Online Training Hub.

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

Sometimes we get requests to provide a VBA solution to a problem. But when we look at the problem, VBA is not the best answer, using a formula is.

What this tells me is that people don't know how to use Excel's functions and when they come up against a problem they think is difficult, or just don't know how to solve it, they go looking for a VBA solution.

By making yourself familiar with basic functions like IF and VLOOKUP (just to name two) you can make your life a lot easier.

Function or Formula?

You'll often see the terms function and forumla used interchangeably. Strictly speaking however, a function is the actual piece of code written into Excel's core programming that gives you a result based on inputs you give it.

For example, IF is a function but when you use it like this

=IF(A1>10,True,False)
you've written a formula using the IF function. How Does IF Work Again?

Let's go over how the IF function works before we get any further. It really is very simple and can help understand how other functions work too.

The syntax is IF (Test_Condition, Result_If_True, Result_If_False)

where Test_Condition is some test that can be evaluated to see if it is true or false e.g.

  • A1>10 : Is A1 > 10?
  • A1*0.5=A2 : Is A1 x 0.5 equal to the value in A2?
  • A1>=A2 : Is the value in A1 greater than or equal to the value in A2?
  • A1="Hello" : Does A1 contain the string "Hello"?
  • AND(A1>0,A21000 : Is the SUM of the values in cells A1 through to A10 greater than 1000?

Easy. So once we have our test, IF will determine if it is TRUE or FALSE. If it TRUE then the result returned by the IF function will be whatever you specify in Result_If_True.

If the result of your test is not TRUE then the result of the IF function will be whatever you specify by Result_If_False.

Result_If_True and Result_If_False can be things like a number, a text string, a Boolean Value or even another function. For example:

=IF(A1

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

A while ago I wrote about to how perform the equivalent of an Excel exact match VLOOKUP formula with Power Query which, by the way, is dead easy. Ever since then I’ve been asked to explain how to do a Power Query approximate match VLOOKUP formula. So, here it is. It requires a few more steps. Nothing too difficult though, I promise.

First let’s look at what it means to do an approximate match VLOOKUP. Taking the Orders Table below I want to apply a bonus for each row based on the Order Value bands in the blue ‘lookup table’:

The Order Value in the Bonus table represents the minimum order amount for the corresponding bonus rate e.g. The order on row 8 will not receive a bonus because it’s below the minimum order value of $10,000. And the order on row 7 will attract a 10% bonus because it’s above $10,000 and below the next band of $20,000.

Using VLOOKUP I’d write the formula like so with ‘TRUE’ as the final argument:

=VLOOKUP(C5,$E$5:$F$8,2,TRUE)

The end result looks like this:

Let’s look at how to achieve the approximate match VLOOKUP with Power Query.

Power Query Approximate Match VLOOKUP

Step 1: Load both the Order Table and Bonus Rates Table to Power Query. In Excel 2016 onward – Data tab > From Table/Range. In Excel 2013 and earlier – Power Query tab > From Table/Range:

Note: If you don’t see the Power Query tab in Excel 2010 or 2013 you can download it here.

Step 2: Merge the Tables; Home Tab > Merge Queries > As New

This opens the Merge dialog box where you select the two tables from the drop-down lists, then click on the column from each table that the lookup should be performed on. On this case it’s the Orders column from the Orders table and the Order Value column from the BonusRates table:

You can see the selected columns highlighted in green. Be sure to choose ‘Full Outer’ in the Join Kind drop-down list at the bottom of the Merge dialog box.

Step 3: Expand the BonusRates table by clicking on the double headed arrow on the column header:

Select ‘Expand’ and deselect ‘Use original column name as prefix' as we don’t need it. It should look like this:

Notice the first row contains null values for Manager, Date and Orders. This is because we don’t have any order values that match the BonusRate Order Value of $20,000. That’s ok. You’ll see why soon.

Step 4: Add a Conditional Column that pulls in the value from the Orders column or if null, then the Order Value from the Bonus Rate table.

Add Column tab > Conditional Column. Note: in the Output and Otherwise fields select the Table from the drop-down to the left. This will enable you to select the table name from the list.

Step 5: Change the data type for the Bonus Band column to decimal number. Click on ABC123 in the column header > Select 1.2 Decimal Number from the list:

Step 6: Sort the Bonus Band column; Select the Bonus Band column header > Home tab > Sort A to Z.

Step 7: Fill down the Bonus Rate; Select the Bonus Rate Column > Transform tab > Fill > Down. It should look like this:

Step 8: Now you can delete the Bonus Band and Order Value columns as they’ve done their job. Select the column headers and press the Delete key.

Step 9: Filter out the null rows in the Manager column as these are redundant Bonus Bands. Click on the drop-down beside the Manager column > deselect ‘null’ from the list:

Now the table contains the Bonus Rates for each row:

For Bonus Points (no pun intended!)

Step 10: If you want to go the extra mile you can add a calculated column for the Bonus amount. Select the Orders column then hold down the CTRL key and select the Bonus Rate column. On the Add Column tab > select Standard > Multiply:

Step 11: Rename the column; double click the header and type in a new name. I’ve called the column ‘Bonus’:

Power Query Approximate Match VLOOKUP Dates

This approach will also work with dates. For example, the blue table below lists fiscal quarters:

I can easily assign them to the Order table by merging them and following the same steps above.  Download the workbook to see the complete example.

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

The post Power Query Approximate Match VLOOKUP appeared first on My Online Training Hub.

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

If you've worked with merged cells you may know that they can cause issues with things like copying and pasting, sorting, and counting cells.

Merged cells can cause VBA to fall over too, so it is best to avoid them. Using Center Across Selection is a better option.

CTRL+1 is the shortcut to open Format Cells -> Alignment and choose Center Across Selection from the Horizontal dropdown list.

I've written a couple of simple VBA routines that will highlight any merged cells on the sheet, and you can run the other macro to unmerge any merged cells.

Download The Sample 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 .xlsm file please ensure your browser doesn't change the file extension on download.

Let's say we have a sheet like this that is used to create invoices.

Running my routine highlights any merged cells which shows this

We can then remove any merged cells and with a little bit of reformatting we end up with this invoice - and no merged cells in sight.

The Code

The first macro searches the active sheet for merged cells and highlights them in green.

I've assigned the shortcut sequence CTRL+SHIFT+M to the macro and running it repeatedly toggles the highlighting on/off.

If you want to unmerge the merged cells then press CTRL+SHIFT+U to run this macro

The post Find and Unmerge Merged Cells with VBA appeared first on My Online Training Hub.

Read Full Article

Read for later

Articles marked as Favorite are saved for later viewing.
close
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

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