Show Hide Scroll Bars to Make Professional Looking Excel Tools

Show hide scroll bars excel is allow you to build more professional looking excel tools without scrolling. Therefore, if you know how to show hide scroll bars in excel it is a bonus for you. Further it is a simple setting in excel and most  of users don't know about it. Now let's see how we can use this cool feature.

Show Hide Scroll Bars in Excel

• Click on the File
• Then Click on the Options
• After that Select the Advance on the left side selections
• Finally scroll down to Display options for this workbook

Now you can see there are two tick boxes named Show horizontal scroll bar & Show vertical scroll bar as above picture. They are ticked by default and remove the tick to hide scroll bars. If you change you your mind you can tick them again to show the scroll bars.

There is a small video to demonstrate this and also how to enable disable sheet navigation. You can check it here.

Clean / Filter Set of Data Using a Keyword(s) - Part 02

If you are interested about manual method instead of VBA/Macro check my previous post.

Clean / Filter Set of Data Using a Keyword(s) - Part 01

This Code has 5 steps which will describe here.

First Step

Declaration of  variables and assigning variables

Dim a As Long, b As Long, c As Long, d As Long 

Dim i As Integer, j As Integer, k As Integer, l As Integer

Dim dl As Worksheet, da As Worksheet, wo As Worksheet, wk As Worksheet 

Dim st As Variant

Dim rn As Range

Application.EnableEvents = False

Application.ScreenUpdating = False

Set dl = Sheets("Data List")

Set da = Sheets("Dashboard")

Set wo = Sheets("List Without Keywords")

Set wk = Sheets("List With Keywords")

There are 5 sheets in this workbook. I have assigned 4 of them to variables for easy use. Then turned off events and screen updating to speed up the macro,

Second Step

Clear available data from output sheets and find last rows of input sheet and Keyword list. (Keyword list is on the column A of the Dashboard Sheet.

a = dl.Cells(Rows.Count, "A").End(xlUp).Row

i = dl.Cells(1, Columns.Count).End(xlToLeft).Column

j = da.Cells(Rows.Count, "A").End(xlUp).Row

wo.Cells.Clear

wk.Cells.Clear

dl.Activate

dl.Range("A1", Cells(1, i)).Copy Destination:=wo.Range("A1")

dl.Range("A1", Cells(1, i)).Copy Destination:=wk.Range("A1")

b = 2

l = Range(da.Range("B2").Value & 1).Column

variable b is going to use as row number of first row which is blank in the sheet List With Keywords sheet. Initially it is 2 

Step Three

Loop through every cell in keyword list, filter data set using the each keyword and copy data with keyword to the sheet List With Keywords. Finally visible cells in the Data List sheet colored using yellow.

For k = 2 To j

st = da.Cells(k, "A").Value

dl.Range("A1", Cells(a, i)).AutoFilter Field:=l, Criteria1:="=*" & st & "*", Operator:=xlAnd

Set rn = Nothing

On Error Resume Next

Set rn = dl.Range("A2", Cells(a, i)).SpecialCells(xlCellTypeVisible)

On Error GoTo 0

If Not rn Is Nothing Then

rn.Copy Destination:=wk.Range("A" & b)

b = b + rn.Cells.Count / i

rn.Interior.Color = RGB(255, 255, 0)

End If

Next

During every iterate of the loop b recalculating to have last empty row number. Here we can't use rn.Rows.Count because rn is not a continuous range. Some rows are hidden according to the filter and therfore rn.Rows.Count give the count of first continuous rows which is not correct. To overcome this error this code calculate all cells in the range and divide it by number of columns. (Cells Count = Columns Count * Rows Count)

Step Four

Filter the Data List sheet to show all rows which are not having any color and copy those data to the sheet List Without Keywords. These are the subset of Data List sheet data which don't having any keyword.

dl.Range("A1", Cells(a, i)).AutoFilter Field:=l, Operator:=xlFilterNoFill

Set rn = Nothing

On Error Resume Next

Set rn = dl.Range("A2", Cells(a, i)).SpecialCells(xlCellTypeVisible)

On Error GoTo 0

If Not rn Is Nothing Then

rn.Copy Destination:=wo.Range("A2")

End If

dl.AutoFilterMode = False

dl.Cells.Interior.Pattern = xlNone

After all filter in the sheet Data List is removed using AutoFilterMode=False and all colors add to the cells set to none.

Step Five

If one row in Data List sheet included more than one keyword, macro copied it more than one to the List With Keywords sheet. Step Five is to remove those duplicates. If there is more than one row to consider .RemoveDuplicates method need to have all column numbers as a array. First four lines of this part is to build that array.

ReDim st(0 To i - 1)

For a = 0 To UBound(st)

st(a) = a + 1

Next

wk.Activate

wk.Range("A2", Cells(a, i)).RemoveDuplicates Columns:=(st), Header:=xlNo

Step Six

Finally code will display the Dashboard sheet and enable Events and screen updates.

da.Activate

MsgBox "Filter Complete", vbInformation

Application.ScreenUpdating = True

Application.EnableEvents = True

During this part code notify the user that process is over. If you turned off events and screen updating during any macro, make sure to turned on within the macro, Otherwise they are turned of until you close all opened excel files and open excel.

You can download the sample excel sheet with the macro using below link

Clean Data List.xlsm

Clean / Filter, Set of Data Using a Keyword(s) - Part 01

When we have a large data set we have to filter / clean it before use. There are many blank rows / columns, unwanted spaces, etc in the data set. We can remove most of them using the tool developed by KTK Excel Tools. You can find out about it from below link

LK Tools Tab for Ribbon

Anyway this article is not to describe how to use it.

If we have to remove some of the data according to a list of keywords, How can we do that? Here is the simplest manual method I can imagine.

1. Filter data set using one keyword.
2. Copy the visible data to a new sheet.
3. Change the visible cell color to Yellow
4. Filter data set using next keyword
5. Copy the visible data to the same sheet which has data from step 2. (Paste this data to the bottom of the sheet)
6. Change the visible cell color to Yellow
7. Repeat the steps 4, 5 and 6 for all keywords
8. Remove duplicates in the new sheet which has copied data. (This is a subset of the beginning data set which including keywords)

If you need to have data subset which are not included keywords, Follow Below steps.

9. Go to the sheet which has all data
10. There are some rows having yellow colored by the previous steps.
11. Filter the data set using color filter to show cells without any color.
12. Copy visible data to new sheet.
13. This is the data subset which is not included keywords.

As a process, this is simple to understand and follow. However, if we have more keywords to look, the time taken to complete this process will be huge. We can automate this process using a macro. Check My next post for the macro.

Clean / Filter, Set of Data Using a Keyword(s) - Part

Show Hide Sheet Tabs in Excel

As I'm an excel macro developer, I'm receiving 4-5 excel files a day from my potential customers. One day I was wondering that there was no way to navigate through the sheets. I have checked if the sheets are hidden, no they are visible but can't navigate. Then I did a search on the google and found the solution.......!. Excel allows us to Hide sheet tabs. Let's see how.

• Click on Office Logo on excel 2007 or File on later versions
• Select Options from that submenu.
• Now you can see a new, popup window appears.
• Select Advanced from the left buttons.
• Scroll down to the Display Options for the Workbook section.
• Tic the option Show Sheet Tabs. (If you are going to hide the sheet tabs, remove the tic)
• Click OK.

Here is a picture about the Display options for workbook section. It shows how to show hide scroll bars on the excel.

Please make sure to have some kind of option like hyperlink or button to navigate through sheets if they are still needed to view.

You can watch the video here

Absolute & Relative References

When copying a formula to another cell there are two things to be happening to the formula.

1. Adjust the formula in the new cell according to the cell
2. Didn't change the formula or part of it according to the destination cell

There are two names for these situations

• Relative Reference
• Absolute Reference

Relative reference means that the formula changing according the new cell it copying and absolute reference is not adjusting the formula as it copies. The difference made by the $ in the formula (before/after the column letter) Without $ mark in the formula, it works as a relative formula.

There are 3 types of absolute references.

• Use Fixed Cells
• Use Fixed Columns
• Use Fixed Rows

If use =$A$1 ($ in both sides of column letter) in any cell, this formula will not change according to

the destination cell. (Fixed Cell). Whenever copy formula to a new cell new cell formula also =$A$1. No change in formula according to the destination cell

If we use =$A1 and copy it to a new cell, the column will not change. As an example, let's think we

enter =$A1 to the cell C1 and copy it to cell D1. The formula in cell D1 will be =$A1 because $ in front of the column letter locks the column. If we paste the formula to cell D2, The formula in cell D2 will be =$A2, Because the formula has locked only for column changes.

If we use =A$1 and copy it to a new cell, the row will no change. As an example, let's think we enter =A$1 to the cell C1 and copy it to cell D1. The formula in cell D1 will be =B$1 because $ after the column letter locks the row. If we paste the formula to cell D2, The formula in cell D2 will be =B$1, Because the formula has locked only for row changes.

New Function IFS

This Function Distributed with Jan 2016 updates for Excel 2016. Please make sure you have installed latest version of excel and is updated. Otherwise, you can't use this function.

Are you using nested If functions? It's time to use IFS for those nested functions. You can use up to 127 pairs of conditions in IFS function.

IFS(logical test1,value if true1,[logical test2,value if true2],[logical test3,value if true3]............)

logical test1 - This is the first condition. This is an required input for the function.
value if true1 - Result if first condition true. This is also required input.
logical test2 - The second condition. This is an optional input
value if true2 - Result if seconed condition true. This is an optional input
.
.
.
upto 127

If you need to return default value if any of the conditions not meet, enter a condition that always true ("1=1") and enter the default value as result.
Check below dataset

Re-enter column A values to yoour workbook or download the sample file here. Enter below to all cells in column B

=IFS(A2>90,"A+",A2>75,"A",A2>65,"B",A2>55,"C",A2>40,"S",A2>30,"W",1=1,"F")

c Here we used 1=1 as always true condition and "F" as default result.
You can see cell B2 showing F because 30 is not within any condition. All others shows the values according to the conditions on the function.

You can discover more about this function from Microsoft Office Help.

New Function TEXTJOIN

This Function Distributed with Jan 2016 updates for Excel 2016. Please make sure you have installed latest version of excel and is updated. Otherwise, you can't use this function.

This function joins the texts with any delimiter we define. Syntax for the function is

TEXTJOIN(delimiter,ignore empty,text1,text2.........)

Delimiter - The delimiter between two texts. This can be space, any single charactor or many charactors. Should be enter within double quotes.
Ignore Empty - If we set 'True', Function will ignore any empty cell if referred.
Text1 - First text to be join
Text2, Text3.......... - Other texts to be join.

See below examples

	A	B	C	D
1	Desktop	Computor
2	HP	60	Black	Ink
3	Laptop		Comutor
4	HP	Scaner
5	8	GB	Flash	Drive
6	HP	Inject	Printer

=TEXTJOIN(" ",TRUE,A1:D1) will return Desktop Computer.

Blank cells ignored because ignore empty is True.

Space between two texts because Delimiter is Space

=TEXTJOIN("-",TRUE,B3:E3) will return HP-60-Black-Ink

Here "-" between two texts because it is the delimiter

=TEXTJOIN("-",FALSE,B4:E4) will return Laptop--Comutor-

Here additional "-" because ignore empty is False

If we set ignore empty to true, output will be Laptop-Comutor.

You can dowload example file here.

You can discover more about this function from Microsoft Ofice Help.

Funnel Charts

Funnel Charts are distributed with excel 2016 updates during Jan 2016. Please make sure you are using Excel 2016 & already installed Jan 2016 updates before use Funnel Charts.

This chart type can use to demonstrate data sets those have descending or ascending pattern. See some examples for this type of data sets.

• No of applications during various stages of interview process
• Cost of item during production process
• Grade 12 Mathematics Z Score
• etc

Download File With Sample Data

Follow below steps to generate

1. Sort the data set ascending or descending pattern. 
2. Select the dataset
3. Go to the Insert tab on the ribbon
4. Click on the 'Recomended Charts'
5. Select 'Funnel'
6. Press OK

See on You Tube

Flash Fill - New Feature

Flash Fill is a new feature in excel, which is looking like it automated text to column feature in excel. Flash Fill identifies the patterns on dataset & give us the extracted output. You can download this demonstration file from this link.

Let's consider that we have a list of emails, which are created in following pattern.

email=first name.last name@emailprovider

Email

keashan.jayaweera@gmail.com

thilanga.jayaweera@yahoo.com

nuwan.sameera@ymail.com

nayana.lankathilaka@sltnet.com

lakshi.ranaweera@ktktools.net

We are going to extract first name, last name & email provider from this list. In traditional way we can use text to column option in data tab in the ribbon. Using Flash Fill we don't even think to change the tab in the ribbon. Assume that first email in the cell A2. Then we are going to enter first name in column B, Last name in Column C and email provider in column D.

Just start typing from Cell B2. Enter first name of the first email to cell B2 & then enter relevant first name to cell B3. While typing you can see excel shows all first names as flash popup. Just press enter to confirm it. That's it. All first names are extracted.

Try to extract last name and email provider from this list.

See on YouTube