The key to setting up a good userform is structure. The items in your userform should ideally be logically laid out so that when you go to write the coding to send the userform information to your data set a sequential loop can be used rather than something which is un-ordered. For example,

Textbox1 in your userform should be the information in Column 1 of your database. Textbox2 should be in column 2 and so on. Sounds logical but you would be surprised how few userforms are set up with this simple logic in mind. Maybe they start out with good intentions and the project changes with time?

The following is our demonstration userfom.

In this example we are going to add some very neat logic. If you enter a valid Registration number (textbox1) and press ENTER, the userform will pre populate.

In the above example I entered 101 into Registration number and hit enter and the form pre populated. The procedure to do this is as follows.

Option Explicit

Private Sub editstudent1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

If KeyCode=vbKeyReturn Then

Findit

End If

End Sub

Where the procedure called Findit is called after Enter is clicked.

Private Sub Findit() 'Find and populate the records with Excel VBA

Dim fnd As Range

Dim Search As String

Dim sh As Worksheet

Dim i As Integer

Set sh=Sheet2

Search=editstudent1.Text

Set fnd=sh.Columns("A:A").Find(Search, , , xlWhole)

If fnd Is Nothing Then

MsgBox "No Person Found", , "Error"

editstudent1.Text=""

frmeditrecord.Hide

Else

For i=2 To 13 'There are 13 items in the userform.

frmeditrecord.Controls("editstudent" & i).Text=sh.Cells(fnd.Row, i).Value

Next i

End If

End Sub

The above Findit procedure checks for the items in texbox1 which is called editstudent1. If the items is found then it populated the userform with the records from that unique registration number. If it fails to find the registration number an error message appears.

After you have edited the userform the form has an Update button. This button when clicked will send the data back to the sheet with the data on it and update it with any changes you have made.

Private Sub cmdUpdate_Click() 'Push data back to the tab with Excel VBA

Dim fnd As Range

Dim Search As String

Dim sh As Worksheet

Dim i As Integer

Dim ctl As Object
Set sh=Sheet2

Search=editstudent1.Text

Set fnd=sh.Columns("A:A").Find(Search, , , xlWhole)

For i=2 To 13

sh.Cells(fnd.Row, i).Value=frmeditrecord.Controls("editstudent" & i).Text

Next i

'Clear Form controls

For Each ctl In Me.Controls

If TypeName(ctl)="TextBox" Then ctl.Value=Null

Next ctl

End Sub

As you can see the above procedure is simply the revers of the procedure to pull the information into the userform.

The attached file shows a working example of this procedure. It should be adaptable to so may other userform examples as the coding is simple and relatively easy to follow in my opinion.

]]>

Baseline Infographic which I used to transform into a dashboard.

The following Excel dashboard is the change from the above.

New Dashboard with changes made to create a larger space and generate more metrics on a single page.

The reason the above is a dashboard is that it is able to change given a change in the underlying source data and this change is brought about by the change in the slicer. The underlying data is a lot larger but the principles of creating a financial visual remain the same:

● The consistent Use of colour in your outputs.

● Alignment to make the eye travel from element to element seamlessly.

● Clean clear headings using the same font where ever possible.

● Liberal use of white space so none of the elements overlap.

After going through the extensive exercise of creating this Excel dashboards I can say one I was very pleased when I saw the final working result. It is very satisfying to get something up and running that tells the story I am looking for. Happy dashboarding peeps.

You can find more dashboards to download (40+) on the main body of the site:

]]>Excel has the ability to sort data into an order of your choosing. The custom sort can be achieved with the assistance of a customised list. Many will be familiar with the list the automates months. If you type Jan in a cell and drag that cell it will produce the months of the year. This is because of a custom list.

When creating a custom sort the first thing you need to create is a customised list, a list which displays the order you want your data to appear in. To set up a custom sort in Excel;

On the Home tab choose Sort & Filter

Click on the** Custom Sort **icon. This will take you to the following dialog.

On the drop down on the right of screen choose Custom List...

On the right of screen click Add and where it says List entries, type your new list.

Click OK and the list will be transferred from the List Entries box, to the Custom Lists Box above.

You will notice after you click OK the custom list will appear in the Order box to the right. Choose the Column you wish to sort by and click OK.

That will result in my data being sorted first by Projects, then Risks, then ROI and last of all will be ROA.

**Excel Manage a Custom List **

You may wish to manage a custom list from a list in Excel. This will make the custom list more dynamic and a lot easier to manage. You can add to the list and change the order of the list as the requirements of your business change. The way to achieve this task is to more formally add to the custom list function.

Choose File - Options - Advanced

Click on the Edit Custom List button.

Click the up arrow and select cells with your custom list order.

In the Import List from Cells box type or refer the cells with the Red reference box. Click OK and your in cell custom list will be added.

]]>Photo by Victor Garcia on Unsplash

This Excel VBA procedure will remove all of the duplicate values in multiple columns. Since Excel 2010 we have been able to remove the duplicates in a column. With the assistance of VBA we can automate this task to remove all of the duplicates in all of the columns or you can choose which columns to remove the duplicate values from.

The following is the Excel VBA code which remove all duplicates in all of the columns of a particular sheet.

Option Explicit

Sub DeDupeCols()

Dim rng As Range

Dim i As Integer

Dim Cols As Variant

Set rng = [A1].CurrentRegion

ReDim cols(0 To rng.Columns.Count - 1)

For i=0 To UBound (cols) 'Loop for all columns

cols(i)=i + 1

Next i

rng.RemoveDuplicates Columns:=(cols), Header:=xlYes

End Sub

As I have used the current region I have made the assumption that headers are on the top row (fair assumption as this is where they should be) . If for some new age reason you do not have headers then remove the last part.

If you wanted to remove the duplicates in say columns (1, 2, 3) then the following would help.

Option Explicit

Sub DeDupeColSpecific()

Cells.RemoveDuplicates Columns:=Array(1, 2, 3), Header:=xlYes

End Sub

Change the columns to suit - ie. 1, 2, 3 becomes the columns you are interested in deleting.

]]>Source - Brisbane Times - Article

I happened across this article on the Brisbane Times website which outlined the dominance of Federer, Nadal and Djokovic in recent memory. It really does show how comprehensive their reign has been. Imagine if there was only one person doing all this? One day it may happen that someone comes along and dominates for 15 years but for now it has primarily been split 3 ways. Not many other people have had a look in over this period.

So I thought I might isolate the period from when Roger started winning and really focus on the period after 2003. Firstly I thought of achieving the result in a bar chart but that did not seem to fit well. Then I tried a normal column chart and bingo – that was the ticket. I was able to generate 4 different charts and stack the results.

As with most things in Excel how the data is laid out is key. The Excel table has to be set out to give a 1 value against each player for a grand slam win. See below:

Using Wimbledon as the example this is the way the data gets laid out in the sheet. With the data in this format it will chart nicely.

Then it is just a matter of adding the other slams to the sheet and making 3 copies of the chart and stacking the charts on top of one another. I managed to get the data in a single stacked column chart. The US Open not occurring caused a bit of a problem but this was worked around. There are two example in the attached file, a stacked Column chart and four column charts stacked. The second is just to show the build up as this was my first attempt.

Attached is the Excel workbook which has this chart and the data laid out within the Excel file. Happy charting.

]]>I recently gave an interview for a blog regarding my Excel journey. Here are some snippets from that Blog.

**Q. Tell us a little bit about the journey to creating an Excel-based business. **

It started when I got a job at PricewaterhouseCoopers (PwC). It became fairly apparent early on that I was adept at automating things. (Now, I think this just comes from me being fairly lazy because I realized pretty early on that Excel can do a lot of the heavy lifting.) Once I automated a few processes for PwC, they gave me a whole heap of processes to automate. Eventually, I went and worked for some investment banks where I was dealing with very large amounts of data. I was able to increase my knowledge of how to manage large data sets with very limited resources.

I came back to Australia and started my website out of love and as a hobby. It wasn’t my intention to start a business. I just wanted to give something back to the communities that have given me so much.

**Q. I was struck by something else you said early on about the sense of gratitude you had for the people who helped you. On your website, you write about how, in giving, you receive.**

I often had time on my hands at work, and I would solve Excel problems online in these large online communities. I knew I was an Excel enthusiast because I’d do it at work and then when I got home. I’d be using Excel up until midnight quite frequently for months and years on end.

You think about how the people you’re helping are helping you. You want to answer the question in the quickest way you possibly can, but you’re also thinking, ‘Is this the best way to answer this question?’ So by giving people the information they’re requesting, they’re actually giving you something as well. They’re giving you an improved skill set. Once I realized that, I understood this was a two-way street.

You can read the entire Smallman interview here.

]]>My good friend Liam Bastick is running The Excel Summit South again. I went last year and it was a genuine blast. Great presenters and a lot to be learnt for the Excel enthusiast. This two-day conference held in late July and early August, presents Microsoft MVPs and other acknowledged experts to answer questions and demonstrate the future of Excel and how it will make your life easier, personally and professionally. I got a great deal out of the experience. It is a small investment in yourself and I personally have never been disappointed when I invest in myself. The conference is in Sydney, Melbourne, Brisbane and Perth.

Use the above discount code on the SUMPRODUCT site for a 10% reduction in the entry fee. You will not be disappointed with the investment in this remarkable Excel conference.

Speakers include:

All accomplished presenters and Excel aficionados. Enjoy the experience - you are in safe hands.

I highly recommend going on a holiday. You come back with fresh ideas and enthusiasm for the journey. I plan on repeating the dose this northern winter.

To see more dashboards like the one above and download many excel spreadsheet examples go to the Dashboard section of thesmallman.com.

]]>The wildcard character and SUMPRODUCT are like oil and water, they just don’t gel well together. The beauty of SUMPRODUCT is somewhat tarnished with its inability to play nicely with the Wildcard * character. It works well with SUMIFS and other Excel Functions but not with SUMPRODUCT. The focus of this article will be to demonstrate how to work around this constraint using a number of examples.

Take the following data set for example, we have countries in Column A and Departments in Column B.

Ideally we Sum by Country by the Department. If we want ALL departments the natural choice is SUMIFS. Where the following gets the job done:

=SUMIFS(C2:C19,A2:A19,E2,B2:B19,"*")

The wildcard character is your friend and works beautifully with SUMIFS. The SUMPRODUCT formula is a different beast and will need formulas such as LEN to take the place of the wildcard.

I recently stumbled across an idea, what if we tested the length of the characters in a given range, with the LEN formula, then summed all of the instances where the length of the cells were greater than 1 character long. This would be the same as the wildcard character. You could then string a range of criteria matches after this point embedded in the SUMPRODUCT formula.

=SUMPRODUCT((A2:A19=E2)*((LEN(B2:B19)>0))*(C2:C19))

It works nicely and summarises the data well.

The SUMPRODUCT formula above cuts through the data set and summarises the data well. However, in the above example where there is only one column to sum then the SUMIFS is the best option. It is a lighter touch and the formula is simpler.

Whenever faced with the problem of summing a single column with criteria or the entire column (the sum of the parts) the SUMIFS and the INDEX and MATCH in a nested formula has helped by solving the problem in the past or the use of a helper column which sums year to date and then use as SUMIFS formula. This is a tad complex though. Also if you have 10,000 lines of data you will need an additional 10,000 formula to get YTD just to use a single SUMIFS formula to calculate your summary.

Enter the SUMPRODUCT formula in conjunction with the LEN formula (this example makes the assumption of a full dataset with information being filled for both country and department - no blanks).

To generate a summary on data based in the country of USA and all year to date data prior to March we need USA as criteria, our LEN formula for all Departments and less than or equal to <= March as criteria. This should produce a result.

=SUMPRODUCT((A2:A19=G2)*((LEN(B2:B19)>0))*(C1:E1<=H2)*(C2:E19))

The above works well on all criteria. It sums USA, ALL departments and every transaction greater than or equal to March for a seamless YTD calculation.

The following file should help crystallise the concept for you

]]>The Excel model contains both the raw data and calculations for each of the cities the organisation operates within. It was an enjoyable model to put together.

Some of the areas covered in this Excel dashboard include

Revenues

Expenses

Projects

Risks

Return on Assets (ROA)

Return on Investment (ROI)

Footfall

Leads

Turnover

Efficiency

Net Clients

The model is mostly a trending model with less detail than the larger Excel dashboard models on the site.

The image above gives a closer look at some of the details and shows more of the trends. Ideally the data would have numbers but that is easy enough to add. This was a bit of fun and looks the part in my opinion of course.

The model is spun on a monthly combo box and will concertina based on the month chosen. This gives the dashboard the ability to go forward or backwards and have the charts snap to the month shown in the combo box.

]]>Creating an excel timesheet is easier with a template to start with. This blog post provides the template which includes lunch breaks and grand totals along with the instructions for using it.

Àll of the BLUE text above is data entry. So you need to enter all hours in the HH:MM format (short for Hour and Minute). The total hours column will calculate the hours considering a lunch break which will not be calculated. If the lunch is left out it only takes into account hours worked.

It is a straight forward template to manage and use and will give you a solid idea of hours worked.

Enjoy my version of this classic Excel problem.

]]>Not that kind of trim!!!

Photo by Alex Mihai on Unsplash

The trim function in Excel is a formula to take out leading and training spaces. It usually works amazingly well, you simply point the formula at the cell with the issue and it will produce a clean piece of text without any leading or training spaces. This article will focuses on what to do when the TRIM formula does not work as expected. It looks at alternatives to solve the problem.

Recently I was trying to cleanse a data set of trailing spaces. I knew they were there so I went to the TRIM formula. I was shocked to find it did not work. So looking into the matter it seems others have had the same problem. If the text was in A2 my formula looked like this.

=TRIM(A2)

However the formula failed. So I used the following

=TRIM(CLEAN(A2))

The above formula worked like a charm. The Excel CLEAN function looks at a text string and returns text that has been made clean of line breaks and any other non-printable characters

So when trim doesn’t work consider using the it in conjunction with the CLEAN formula.

]]>To access the shapes click on the Insert Menu.

On the Insert Menu choose 3D models – from Online Source. This will give you access to Microsoft’s online 3D library. The following is what the file looks like with a 3D spinning option.

The image can be moved in any direction, the spinning tool above is very intuitive and easy to use.

Here are the results from some playing with the above image.

You can see how the images are different. There is a large range of images in the library.

From an infographics perspective I wish this feature was available when I was building the infographics section of this website. There is so much awesome content. Have a play around with them. It is a lot of fun!!!

Traditionally I have used the OFFSET function for dynamic ranges within Excel. It is solid and gets the job done on a given range. One thing to remember when using OFFSET is to ensure you include a big enough row count for vertical ranges. It works really nicely on a tabular data sets. If you are unfamiliar the following is an example of a dynamic range with a AVERAGE formula:

=AVERAGE(OFFSET(B2,,,COUNTA(B2:B90)))

However, there is another method for trapping dynamic ranges, let’s say we want the AVERAGE for the cells in column B and the likelihood that column B will grow is extreme. The same formula can be written as follows:

=AVERAGE(B2:INDEX(B2:B90,COUNTA(B2:B90)))

While the formula is longer (by 4 characters) it has the added advantage of being a non volatile formula. If there are too many Volatile formula in a spreadsheet it can start to chug a bit.

Breaking the formula down cell B2 is where the formula range starts, the INDEX formula comes to the party where it creates the second cell reference in Column B. It looks at the range between B2 and B90 and is looking for a specific Row number in the first instance. Enter the COUNTA formula which acts as the solution for which Row number. Counta counts all of the cells that have data in a given range. Blanks are not included. So if the data stops in A15 then all of the data from B2:B15 are included in the AVERAGE. It is a master stroke and keeps things non volatile.

Try it with your own data. Here is a workbook that displays both techniques.

Formulas are the building blocks of any Excel spreadsheet. The formulas you use will ultimately determine how flexible your spreadsheet is. The effective use of Excel formulas allows advanced users to summarise large amounts of data, analyse the outputs and make recommendations based on the results. These are the must have formulas:

COUNTIFS

Editing formulas / Error checking

ARRAY formulas

Sound use of formulas and knowing which one to use in any given situation is the foundation block of Advanced Excel knowledge.

Being able to use the right chart in the right situation is key. When you are comparing datasets there are certain charts that work well. Once you understand what message you're trying to tell with the data you have, it's time to select the best method for displaying that information on a page. Different chart types cater best to different methods. To create a comparison chart, use these types of graphs:

Circular Area Chart

Line Chart

Scatter Plot Chart

Bullet Chart

Combination Charts

Sparklines

All of these suggestions will get you started, but there are no hard and fast rules for how data should be presented, it should be clear, communicative, and need no further explanation. If you find that you're restrained by common chart types, then by all means branch out to more experimental techniques. There's no reason not to let your inner designer sit down with your inner statistician—together the two of you can come up with some intelligent and informative methods to present information, and you won't have to fall back on pie charts and bar graphs to do it.

Conditional formatting is an inbuilt Excel feature when used effectively can be visually and operationally extremely powerful. Conditional formatting highlights either areas that exceed, meet or fall short of expectations most notably with a traffic light effect or the use of colour. There are inbuild icons which make conditional formatting fun and attractive on the page. They are remarkably simple to apply and adjust to add your own criteria. Used intelligently conditional formatting can be a great way to flag issues in business or processes which can prompt a decisive action.

An Excel table is a way of structuring data in a dynamic way inside of an Excel spreadsheet. Advanced Excel users know how to create and use Excel tables to manipulate and manage their data. In a table the headings will hold the reference to its own column so it is important as per normal to name the column wisely. Tables are an genuinely powerful resource which allows you to generate more data dynamically. The list can grow and any formulas that are associated with a table will grow as well. The formula length will adjust as the table either increases or decreases in size. Tables are a wonderful addition to Excel and well worth adding to your arsenal.

A pivot table an inbuilt Excel program that allows you to seamlessly summarise selected datasets to obtain a neat report in a matter of moments. A pivot table looks at the dataset you desire and summarises information based on the column heading. Advanced pivot table functions include one of my favourite things in Excel – the use of slicers to summarise data, other key features include grouping, using different features such as summing, counting, average, min, max.

Power Pivot is the big brother of the pivot table. With Power Pivot you can store an enormous amount of data from multiple sources (not just a spreadsheet) into Excel back end and summarise multiple tables into a single pivot table. It is this advanced functionally which gives power users a genuine advantage.

** **I can not recommend highly enough learning Excel’s shortcuts. As an Excel user you can increase your productivity and achieve much more by using the keyboard as opposed to the mouse. If I were to select say 20,000 rows by 20 columns the task would take 10-15 seconds with the mouse – if however I selected Control + Shift + 8 (top keyboard 8 not number pad) then it takes the time to press those 3 keys – a fraction of a second. The data needs to be structured in a tabular format but that is a given for advanced Excel users.

In Excel goal seeking is the ability to calculate a problem by iteratively testing solutions until the correct answer is reached. This is often referred to as what-if analysis or back-solving a problem. The beauty of mastering the technique is you can get optimal solutions to difficult problems in moments without having to run the trial and error yourself. It is worth pointing out that Goal seek works wonderfully well with VBA as usually more than one cell needs a goal and VBA will do many cells in no time at all.

Excel has a really cool tool called solver that uses trial and error to find optimal solutions for a range of operational problems. It is a sort of WHAT IF analysis and is genuinely useful when determining the optimal outcome, given a set assumptions.

Solver is an Excel Addin which will need to be incorporated into Excel.

The language that sits behind the Excel spreadsheet is known as Visual Basic for Applications. With knowledge of VBA and advanced user can make Excel do some amazing things. Tasks can be automated, new formulas can be created and time consuming reporting processes can be done by the computer not the individual. Once the knowledge is acquired the time savings can be enormous.

]]>The following are a selection of my premium Excel dashboards which are available on my Eloquens channel. These are the most advanced Excel Dashboards I have created to date. Enjoy!!!

There are plenty more Excel Dashboards on my Dashboard page for free download. Smallman Excel Dashboards

]]>I came across a post today which was asking for assistance to convert all the for converting all of a selected range to either relative or absolute referencing.

The poster at first tried to choose the Specialcells method (a snipers rifle if you will) but could not get it working so checked all the cells in a selected range (a shot gun by comparison). The reason why the first method would be better is Excel will surgically concentrate on only the cells that have formula in and ignore the rest of the cells. The latter method will check all of the cells in the selected range.

The following are the values that need to be entered into the input box.

xlAbsolute 1 Convert to absolute row and column style.

xlAbsRowRelColumn 2 Convert to absolute row and relative column style.

xlRelRowAbsColumn 3 Convert to relative row and absolute column style.

xlRelative 4 Convert to relative row and column style (no references)

The following is the code I wrote to solve the problem. It works nicely.

Option Explicit

Sub ConvertFormulasToAbsolute()

Dim rng As Range

Dim i As Integer

i = InputBox("Add a number between 1 & 4", "Goski")

For Each rng In Selection.SpecialCells(xlCellTypeFormulas)

rng.Formula = Application.ConvertFormula(rng.Formula, 1, 1, i)

Next

End Sub

Place the VBA code in a regular module and select the range you wish to affect.

All the very best.

Smallman

]]>So I have a very important Excel workbook and I need the original formula protected at all costs. So I want a process that runs through all of the worksheets in the Excel workbook and converts everything from formulas to values. This way none of the mechanics can be messed with in any way. This is a bit like sending out a read only version of your work. Or converting the whole thing to PDF. There are many ways to skin the preverbal cat as it were.

Option Explicit

Sub ConverttoVals()

Dim sh As Worksheet

For Each sh In Sheets

sh.UsedRange.Copy

sh.UsedRange.PasteSpecial xlPasteValues

Next sh

Application.CutCopyMode = 0

End Sub

The For Each loop covers off all the worksheets in the workbook and the used range covers off only the used data in the workbook. After this the paste special xl values takes over to finish the job.

The process runs reasonably quickly. It will clear the file. You want to make sure that you save the file as a different version and please have a back up just in case you accidently save the file.

Enjoy and I hope it has been useful.

]]>Of late I have been working with pulling data from cells and extracting the numbers between a series. So if the data is presented like the following.

(2010-2015)

I want to see the following:

2010, 2011,2012, 2013, 2014, 2015

This post is very similar to the blog post from January which does the same sort of thing differently:

To do this we could create a custom function to pull the data from a cell in a neat and clean way.

Option Explicit

Function GetYr(txt As String) As String

Dim Var As Variant

If txt Like "*-*" Then

Var = Split(txt, "-")

GetYr= Join(Evaluate("transpose(row(" & Var(0) & ":" & Var(1) & "))"), ", ")

Else

GetYr= Val(txt)

End If

End Function

I prefer the extract method as it is cleaner and uses less coding to achieve a customised result. Use the dash ***-*** as your delimiter, if you want to change it use your symbol in place of the dash.

Neat and clean result with a custom function drawing on the Evaluate function.

Also if you wish to have a different data separator then change the following;

Years = Join(Evaluate("transpose(row(" & Var(0) & ":" & Var(1) & "))"), ", ")

Where ", "

The end needs to change if you wished to use for example the dash then the code would look as follows.

Years = Join(Evaluate("transpose(row(" & Var(0) & ":" & Var(1) & "))"), "- ")

Where "- "

Is the part you change. Hope this is clear.

The below file shows the coding in action. Hope this provides more clarity.

We can attack this problem with an index and match and the help of the IFERROR function in case there is no result.

=IFERROR(INDEX(C6:H6,0,MATCH("*",C6:H6,0)),"Not Trained")

The INDEX’S first argument is always where the result lives. So C6:H6 is the range we are evaluating for a result.

The MATCH is what are we matching and in this case ANYTHING will do and this is represented by the wildcard character “*”. We put this in quotation marks so Excel knows it is text. The rand this can be found in is the second argument which is C6:H6 and the zero (0) is for an exact match.

Finally the IFERROR function kicks in if no result is found. This is where no data is entered for an individual indicating they have had no training.

]]>