Data is everywhere but the information must be produced.

Veri her yerde ama bilgiyi üretmek gerekir.
Data is everywhere but the information must be produced.

FARUK ÇUBUKÇU, IT Specialist.

Split Function

One of the powerful functins of Excel VBA is Split function. Split function splits (parse) the string data with a given separator. For example, “Turkey/İzmir/Urla” string data can be splitted as follows:

Turkey

İzmir

Urla

Code required:

Dim StringParts() As String

StringParts  = Split( “Türkiye/İzmir/Urla” , "/" )

StringParts (0) = "Türkiye”

StringParts (1) = "İzmir"

StringParts (2) = "Urla"

What will be the date after 3 months form now?

What will be the date after 3 months form now?

=EDATE(date, number of months)

Excel's new ew artificial intelligence (AI) features explained by Microsoft.

New tools (Ideas), new data types and new fuctions added Excel to provide artificial intelligence (AI) features. 

These functions:  FILTER, UNIQUE, SORT, SORTBY, SEQUENCE, SINGLE, and RANDARRAY.

More information:  https://www.microsoft.com/en-us/microsoft-365/blog/2018/09/24/bringing-ai-to-excel-4-new-features-announced-today-at-ignite/

Excel in business life

https://www.facebook.com/EngineeringML/photos/pb.1701212786658447.-2207520000.1564390695./2251719211607799/?type=3&theater

Create a chart by pressing F11 key

To change a formula

There are different ways to change a formula; 

1) On formula bar. 

2) Doubl-click on the cell

3) Press F2 key

Usage of Percent Sign %

Percent sign are used for different purposes.

Excel VBA - Find the last row

In Excel VBA, we need to find out last row number (how many rows / data tables's rows count) to scroll down or other operations.

For example start from B2 to the last row in B column.

Sheets("FC").Range("b2").End(xlDown).Row

or

Sheets(1).Range("b2").End(xlDown).Row

Example: Read all row in the column and add to combo box.

For line= 2 To wb1.Sheets(1).Range("b2").End(xlDown).Row

ThisWorkbook.Sheets("Sheet1").ComboBox1.AddItem wb1.Sheets(1).Cells(satir, 2).Value

Next

Using Excel Functions Together

Which function can be used together? And which of these combinations offers strong solutions?

VLOOKUP and MATCH

IFERROR and VLOOKUP

INDEX and MATCH

SUMIF and INDEX and MATCH

LEFT and FIND

RIGHT and FIND

MID and FIND

IF and AND / OR

We can see MATCH function too many. Because it produces the number of item in a list or column. So this number used in VLOOKUP as a third parametet (column number). 

for more explanation: 

www.excelci.com

Excel - quick access tollbars best usages - mostly used icons

Count Number of Errors

Making too much calculation cause some Excel errors. So you can count these errors on your table.

=SUM(--ISERR(D2:D10))

Also number of lines that includes no error.

=SUM(--NOT(ISERR(D2:D10)))

Typical screenshot:

Excel 2016 - Office 2016 - Excel 2016 versiyon is coming

´Excel Power Query

´New forecast functions

´Pivot table, something new in slicers

´Pivot charts, time gruping

´PowerView for OLAP connections 

´Data model, PivotTable, use relationships automatically

´New BI functions and capabilities

´Excel data cards 

´……

VLOOKUP function - 15 different usage scenarios

VLOOKUP function is one of the most powerfull function in Excel.

We can explain 15 different usage for you. 

1. Exact match (typical scenario). 

2. Close match (for numbers)

3. Wildcard characters  (* and ?)

4. With error control (iferror). 

5. With error control (if  iserror)

6. Compare two lists

7. With MATHC function. Two dimensional.

8. With ROW function. 

9. Winth COLUNN function. 

10. With IF function. 

11. With LOOKUP function. 

12. With CHOOSE function. 

13. Winth SUM function. And Array usage.

14. With INDIRECT function. 

15. Other usages.

userform validation

' textbox must be filled

If txtAdiSoyadi.Text = "" Then
   MsgBox " name textbox must be filled!!!!!!!!!"
   txtAdiSoyadi.SetFocus
   Exit Sub
 
End If

If txtTcKimlikNo.Text = "" Then
   MsgBox "ID textbox must be filled!!!!!!!!"
   txtTcKimlikNo.SetFocus
   Exit Sub
End If

EXCEL VBA - how many rows data we are going to handle in loop?

Range("a" & Rows.Count).End(xlUp).Row


MsgBox (Range("a" & Rows.Count).End(xlUp).Row)
          ' from bottom to the last filled cell

     
With Cells :
 

MsgBox (Cells(Sheets("Sheet1").Rows.Count, "A").End(xlUp).Row)

INDEX function simplified

Excel shortcut keys: Ctrl and 8

Excel Shortcut Keys – Ctrl and 8

To hide and display the outline symbols, press Ctrl+8

To restore the outline symbols again, press Ctrl+8 

Note: First use Data / Groups tool on typical data and then use these keys to hide and display grouping symbols. 

Using Smiley icon in Excel

Using Smiley icon in Excel is one of the most asked questions. 

First of all, is is a character like J,K,L. But is Windings font. 

You should build your IF sentences accordingly. Then create charater with CHAR function. 

Such as; IF (a1>b2;CHAR(74);.......).  Then format the cells where smileys are seen with Windings. 

Excelskill.com, is a very unique Excel training video site, provides hundreds of video that shows advanced Excel topics. Videos presented by very experienced and talent Microsoft Certified Trainer - Faruk Cubukcu.
Most videos are unique solutions that will be easily adapted to your job. You can watch these videos to see how Excel makes it easy to use Excel functions (such as LARGE, MID, VLOOKUP, LOOKUP and SUMIF functions). Also data analysis skills, sort and filter features and advanced use of Pivot tables and charts.
There are many videos to learn advanced use of IF function. Including nested IF functions, IF with AND and OR, also COUNTIFS and SUMIFS, and AVERAGEIFS, IFERROR and IS…
You can learn conditional formatting and data validation techniques in a custom way. Also how to use the functions in various scenarios, in large spreadsheet, and on other worksheets and workbooks.
Excelskill.com videos categorized by, general, data analysis, finance, statistics, macros and VBA. All videos recorded in Excel 2013, but most of them relevant to the 2010 and 2007 versions as well. After you have watched the videos, you can download the data file and make hands-on exercises on them.