view · edit · history · print

Bad habits...

Be aware: all versions of Excel contain far more rows than the Application can ever handle in a decent way. Newer versions, far exceed [with millions of rows] the internal Applications capabilities of 65536. Excel can only track 65536 dependencies to unique references for automatic calculation and filtering. Need more? Use access or a real database!

See also google "excel bad practices" -- some are a bit silly, but not the ones above :-). Off-course, there are situations where the best practices may not apply; but only if you know why and did think about it without getting tricked by convenience.

Excel formula

weeknumber:

  • you need a function add-in i think : Analysis Toolpak
  =WEEKNUM(G4;2) 
  • withouth add-in
  =INT((TODAY()-(DATE(YEAR(TODAY());1;1)-(WEEKDAY(DATE(YEAR(TODAY());1;1))-2)))/7)+1  

in-cell graph (does not work wit negative numbers):

  • A1=multiplier(0,1)
  • A2=first value
  =REPT("|";A2*A1) 

het verschil:

  • A=old value
  • B=new value
 delta   =B-A
 %delta  =-(1-B/A)*100 <= het verschil in procent
 pct(%)  =B/A*100

alternating rowcolors:

  • use conditional formatting formula
 =MOD(ROW();2)
 =IF(OR(((MOD(ROW();4))=1);((MOD(ROW();4))=2));1;0) = bad math but it works
 =MOD(MOD(ROW();4);3) = bad math but it works

convert EURO format:

  • example: "66,55- EURO"
  • result: "-66,55"
 =VALUE(IF(ISERR(FIND("-";A1));LEFT(A1;FIND(" ";A1)-1);CONCATENATE("-";LEFT(A1;FIND("-";A1)-1))))

convert example:

 
  A                       B             C        D                E             F          G
1 text string:            position(-)   length   extract          number?       value      number?
2 formula:                =FIND("-",A3) =LEN(A3) =MID(A3,B3+1,C3) =ISNUMBER(D3) =VALUE(D3) =ISNUMBER(F3)
3 apple - 2,100,000       7             17       2,100,000        FALSE         2100000    TRUE
4 red orange - 915,000    12            20       915,000          FALSE         915000     TRUE

in this example =MID(A3,B3+1,C3) could also be written as =RIGHT(A3,C3-B3-1).

  • left value of "-" =MID(E5;1;FIND("-";E5)-1)
  • right value of "-" =MID(E5;FIND("-";E5)+1;LEN(E5))

encapsulate left and right values xxxxx = MID(...)

  • =IF(ISBLANK(E5);0;VALUE(xxxxx))

Excel VBA

Paste in appropriate excel sheet object

  
Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim Row             As Long
    Dim myrange         As Range

    Cells.Interior.ColorIndex = xlNone
    Cells.Borders.ColorIndex = xlNone

    Row = Target.Row

    Set myrange = Range("A" & Row, "II" & Row)

    myrange.Interior.ColorIndex = 15
    myrange.Borders.ColorIndex = 1

End Sub

Paste in appropriate excel sheet object

  
Option Explicit 

Private Sub Worksheet_SelectionChange(ByVal Target As Range) 

    Dim RngRow          As Range 
    Dim RngCol          As Range 
    Dim RngFinal        As Range 
    Dim Row             As Long 
    Dim Col             As Long 

    Cells.Interior.ColorIndex = xlNone 

    Row = Target.Row 
    Col = Target.Column 

    Set RngRow = Range("A" & Row, Target) 
    Set RngCol = Range(Cells(1, Col), Target) 
    Set RngFinal = Union(RngRow, RngCol) 

    RngFinal.Interior.ColorIndex = 6 

End Sub 

see:

  
Option Explicit

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If ActiveCell.Interior.ColorIndex = xlNone Then
ActiveCell.Interior.ColorIndex = 40 ' tan...sort of
Else
ActiveCell.Interior.ColorIndex = xlNone
End If
End Sub

Macro to hide columns and the form Scroll Bar objects

  
Sub hide1()

    Dim counter As Long

    'Range("hideme").Columns(1).Select
    ' When columns is applied to a Range object that's a multiple-area selection, 
    ' this property returns columns from only the first area of the range.
    For counter = 1 To 4
        Range("hideme").Columns(counter).Select
        Selection.EntireColumn.Hidden = True
    Next counter
    Range("A1").Select

    Dim sOptBut As Shape

    For Each sOptBut In ActiveSheet.Shapes
        If sOptBut.FormControlType = xlScrollBar Then
             sOptBut.Visible = msoFalse
        End If
    Next sOptBut

End Sub

sort columns individually of each other for box and wiskers diagram sample

  
Sub SORTCOL()

' sort columns individually of each other for box and wiskers diagram sample
' make sure data samples are transposed from top to bottom
' select top row from left to right accross the dataset

' 5 6 7 8 <= select from left to right
' 4 4 8 9
' 5 8 6 7

For Counter = 1 To Selection.Count

    Columns(ActiveCell.EntireColumn.AddressLocal).Select

    Selection.Sort Key1:=Range(ActiveCell.AddressLocal), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    ' if headers ar posing problems user 'Header:=xlNo' instead of 'Header:=xlGuess'

    ActiveCell.Next.Select

Next Counter

End Sub

How to convert date/time to seconds

Times are stored internally by Excel as fractions of a 24-hour day, so that 12:00:00 would be stored as 0.5 and 18:00:00 as 0.75. Consequently, to convert a time to seconds, you must multiply by the number of seconds in a day - it is easier to remember this as *24*60*60 rather than the actual number (86400).

How to list environment Variables in Excel

Tools > Macro > VB Editor

actions: double click on sheet2 > paste below subroutine and [run] > then switch back to sheet2 in excel to display results.

  
Sub test()

Dim EnvString As String
Indx = 1
Do
    EnvString = Environ(Indx)
    Cells(Indx, 1) = EnvString
    Indx = Indx + 1
Loop Until EnvString = ""

End Sub

Get only 1 var and optionally show in msgbox

  
Dim ENVIR As String
ENVIR = Environ("SNC_LIB")
MsgBox ENVIR

Find row with text OR last row containing data and use it

  
    ' find text "END OF TODO" and jump to it
    'Cells.Find(What:="END OF TODO", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    ' => alternative to above: goto last row with data
    'Range("a65536").End(xlUp).Select
    'lastrow = ActiveCell.Row
    ' => alternative to above: no select required
    lastrow = Range("a65536").End(xlUp).Row
    ' => example usage for lastrow: Range("B" & lastrow).Select
    ' => example usage for lastrow: Range("A" & lastrow & ":B" & lastrow).Select

Find column with text and use it

  
    Cells.Find(What:="sometext", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
        xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        True, SearchFormat:=False).Activate

    curcol = Left(ActiveCell.EntireColumn.Address(columnabsolute:=False), Application.WorksheetFunction.Find(":", ActiveCell.EntireColumn.Address(columnabsolute:=False)) - 1)

Disable Addins

  
AddIns("RowLiner 4.1").Installed = True
AddIns("RowLiner 4.1").Installed = False

Shifting rows up and down.

attach shortcut ctrl key combination: tools > macro > macros > select macro > options

  
Sub row_up()

    curcol = ActiveCell.EntireColumn.Address(columnabsolute:=False)
    curcolleftsize1 = (Len(curcol) - 1) / 2
    curcolnum = Left(curcol, curcolleftsize1)

    currow = ActiveCell.EntireRow.Address(rowabsolute:=False)
    currowleftsize1 = (Len(currow) - 1) / 2                   '    currowleftsize2 = Application.WorksheetFunction.Find(":", currow) - 1 'alternative
    currownum = Left(currow, currowleftsize1)

    currownum_finalposition = currownum - 1
    currownum_insertrow = currownum - 1

    Rows(currow).Select                                       '    Range(currow).Select 'alternative to row select
    Selection.Cut
    Rows(currownum_insertrow & ":" & currownum_insertrow).Select
    Selection.Insert Shift:=xlDown

    Range(curcolnum & currownum_finalposition).Select

End Sub

Sub row_down()

    curcol = ActiveCell.EntireColumn.Address(columnabsolute:=False)
    curcolleftsize1 = (Len(curcol) - 1) / 2
    curcolnum = Left(curcol, curcolleftsize1)

    currow = ActiveCell.EntireRow.Address(rowabsolute:=False)
    currowleftsize1 = (Len(currow) - 1) / 2                  'currowleftsize2 = Application.WorksheetFunction.Find(":", currow) - 1 'alternative
    currownum = Left(currow, currowleftsize1)

    currownum_finalposition = currownum + 1
    currownum_insertrow = currownum + 2

    Rows(currow).Select                                      'Range(currow).Select 'alternative to row select
    Selection.Cut
    Rows(currownum_insertrow & ":" & currownum_insertrow).Select
    Selection.Insert Shift:=xlDown

    Range(curcolnum & currownum_finalposition).Select

End Sub


increase fs relative to the current size

  
  A		B	C
1 Filesystem	curr_GB	new_GB
2 /sapmnt	1	1
3 /usr/sap	4	5
4 /sapdb	1	1
5 /usr/sap/ACC	5	7
6 /sapmnt/ACC	3	4
7 /sapdb/ACC	20	29
8 SUM		34	50

B8=SUM(B2:B7)
C8=<new total>
C7=ROUNDDOWN(B7*C$8/B$8;0)


Calculate quarter based on date field (<year>Q<#> ; 2011Q2)

  
=YEAR(F2)&"Q"&LOOKUP(MONTH(F2);{1;4;7;10};{1;2;3;4})


Formulla fill down

  
1.Type a number, such as 395.54 into cell D1 in Excel.
2.Press and hold down the Shift key on the keyboard
3.Press and hold down the Down Arrow key on the keyboard to extend the cell highlight from cell D1 to D7.
4.Release both keys.
5.Press and hold down the Ctrl key on the keyboard.
6.Press and release the " D " key on the keyboard.
7.Cells D2 to D7 should now be filled with the same data as cell D1. 


Subtract date and time

  
20/03/2012 13:48 - 10/02/2012 11:49 = 937:58
where formula for 937:58 is =B1-B2 with formatting [u]:mm or [hh]:mm
you can also use the function =days360(B1;B2) and format as number
=DAYS360(B2;IF(B1="";NOW();B1))


always clean and trim

  
=TRIM(CLEAN(A2))


Get URL from excel links

  • create workbook module and paste below function
  • use function as =hlink(A1)
  
Function HLink(rng As Range) As String
'extract URL from hyperlink
'posted by Rick Rothstein
  If rng(1).Hyperlinks.Count Then HLink = rng.Hyperlinks(1).Address
End Function


Create a Search Box in Excel without VBA

Set new conditional format rule and use a formula =and(ISNUMBER(SEARCH($C$1;A4));$C$1<>"")

REF: http://easy-excel.com/create-a-search-field-in-excel-in-5-minutes/


find a sub-string in a range

  
=MATCH("*"&E2&"*";'tabsheet'!F:F;0)


Show excel formullas

  • create workbook module and paste below function
  • use function as =showf(A1)
  
Function ShowF(Rng As Range)
ShowF = Rng.Formula
End Function


Color conversion from hex codes in excel

  
Sub SetHexColors()
Dim i, LastRow
LastRow = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LastRow
Cells(i, "B").Interior.Color = HEXCOL2RGB(Cells(i, "A"))
Next
End Sub

Public Function HEXCOL2RGB(ByVal HexColor As String) As String
Dim Red As String, Green As String, Blue As String
HexColor = Replace(HexColor, "#", "")
Red = Val("&H" & Mid(HexColor, 1, 2))
Green = Val("&H" & Mid(HexColor, 3, 2))
Blue = Val("&H" & Mid(HexColor, 5, 2))
HEXCOL2RGB = RGB(Red, Green, Blue)
End Function


convert (date) time to decimal hours

  
# simply multiply it by 24. Set formatting to number and two places of decimal.
=(B1-A1)*24

PS: and back... /24 for to convert decimal hours back to days and format as time field.


fill down if blank

http://www.extendoffice.com/documents/excel/771-excel-fill-blank-cells-with-value-above.html


Calculate average of values in a relative position

calculate AVERAGE
of 1 column to the left of the current column "COLUMN()-1"
starting 2 rows down "ROW()+2"
for the next 10 rows, ending 12 rows down "ROW()+2+10"

  
=AVERAGE(INDIRECT(ADDRESS(ROW()+2;COLUMN()-1;4)):INDIRECT(ADDRESS(ROW()+2+10;COLUMN()-1;4)))

notes
ADDRESS(;;4) = returns relative adresses like A1 instead of absolute $A$1
INDIRECT evaluates the ADDRESS as a reference, otherwise the result of ADDRESS would be seen as a text string.


find reference of cell containing string

find row "MATCH"
containing next string "*Ref#"
down in current column "COLUMN()"
starting 2 rows down "ROW()+2"
ending at row "1000"

  
=ROW()+1+MATCH("*Ref#";INDIRECT(ADDRESS(ROW()+2;COLUMN();4)):INDIRECT(ADDRESS(1000;COLUMN();4));0)

notes
ADDRESS(;;4) = returns relative adresses like A1 instead of absolute $A$1.
INDIRECT evaluates the ADDRESS as a reference, otherwise the result of ADDRESS would be seen as a text string.
MATCH(;;0) = finds the first value that is exactly equal to the lookup value.


do something (filter) if a cell changes

  
Private Sub Worksheet_Change(ByVal Target As Range)
Dim keycells As Range
Set keycells = Range("b4")
If Not Application.Intersect(keycells, Range(Target.Address)) _
    Is Nothing Then

    If WorksheetFunction.CountA(Range("b4")) = 0 Then
        On Error Resume Next
        ActiveSheet.ShowAllData
    Else
        On Error Resume Next
        ActiveSheet.ShowAllData
        ActiveSheet.Range("$A$4:$DO$5000").AutoFilter Field:=3, Criteria1:="TRUE"
    End If
End If   
End Sub


excel field formatting

https://support.office.com/en-us/article/Number-format-codes-5026bbd6-04bc-48cd-bf33-80f18b4eae68

if the date is not set and therefore 0 you can use the custom format: yyyy-mm-dd;;;@

Month in English (409), no matter what language setting you have:

  
[$-409]mmm;@


Sum in a column if a certain condition in another column is met

  
=SUMIFS(I:I;C:C;"="&C11)

...sums all fields in i:i if the field in column c:c is the same as on your current line in row 11 (field c11).


Example timesheet with different example formulas including /day /week calculations

  
AM/PM	date            week	start	end	hh:mm   TOT/day h       h/day   h/week
AM	5/09/2016	36	9:14	15:59	6:45		6,75	/	/
PM	5/09/2016	36	17:14	20:55	3:41	10:26	3,68	10,43	/
AM	6/09/2016	36	11:04	12:00	0:56		0,93	/	/
PM	6/09/2016	36	12:30	17:31	5:01	5:57	5,02	5,95	/
AM	7/09/2016	36	9:04	12:00	2:56		2,93	/	/
PM	7/09/2016	36	12:30	18:35	6:05	9:01	6,08	9,02	/
AM	8/09/2016	36	9:17	12:00	2:43		2,72	/	/
PM	8/09/2016	36	12:30	18:41	6:11	8:54	6,18	8,90	/
AM	9/09/2016	36	9:14	12:00	2:46		2,77	/	/
PM	9/09/2016	36	12:30	18:45	6:15	9:01	6,25	9,02	43,32
AM	12/09/2016	37	9:14	15:59	6:45		6,75	/	/
PM	12/09/2016	37	17:14	20:55	3:41	10:26	3,68	10,43	/
AM	13/09/2016	37	11:04	12:00	0:56		0,93	/	/
PM	13/09/2016	37	12:30	17:31	5:01	5:57	5,02	5,95	16,38
                        =INT((B2-(DATE(YEAR(B2),1,1)-(WEEKDAY(DATE(YEAR(B2),1,1))-2)))/7)
					        =E2-D2				
						        =IF(B2=B1,SUM(F1:F2),"")   <=== this causes problems if you insert or have +- ť lines, use indirect!
							        =(E2-D2)*24		
								        =IF(B2<>INDIRECT(ADDRESS(ROW(B2)+1,COLUMN(B2),4)),SUMIFS(H:H,B:B,"="&B2),"/")	
									        =IF(C2<>INDIRECT(ADDRESS(ROW(C2)+1,COLUMN(C2),4)),SUMIFS(I:I,C:C,"="&C2),"/")


Chart including dates with no data

steps for data

  • make pivot (count of date)
  • Group by month and year
  • pivot > design > report layout > repeat all item labels
  • field settings > layout > "Show items with no data" checkbox
  • pivottable options > layout > "For empty cells show" 0 (or -1)
  • filter out the report endpoints (example: <1/1/2017 and >12/16/2017)

optional graph settings

  • show data labels outside (zero values will also show)
  • if empty cells set to -1 then you can change the vertical axis option minimum to 0, this will hide the -1 data labels
  • if data labels are shown, the vertical axis can be hidden
  • other markup: set width of bar and set border color

ref:


Extracting Integers and Fractions in Microsoft Excel

  
To extract the integer value, use the formula:
=INT(A1)
To extract the fractional value, use the formula:
=MOD(A1,1) or =(A1-INT(A1))

http://excelsemipro.com/2013/02/extracting-integers-and-fractions-in-microsoft-excel/


usage of F4 when selecting a cell

the selection will cycle through the possible assignments...

  
=E7
=$E$7
=E$7
=$E7


Excel Highlight rows and records

Conditional range with following formula where A1 is the list drop down for values in C:C

  
=$C2=$A$1


Highlight selected row or column in Excel (minimal, 1 line, VBA in workbook)

notes:

  • clrl+z = undo
  • you can even avoid the VBE when you press F9 (recalculate) after each change

Conditional values (applies to =$A:$AZ)

  
=ROW()=CELL("row")
=COLUMN()=CELL("col")
Application.ScreenUpdating = True

Alternatives


  



  



  



  



  



  



  



  


admin · attr · attach · edit · history · print
Page last modified on November 11, 2017, at 04:16 PM