Excel VLOOKUP function defined in an Image

Excel - Data Analysis - Regression- Analysis ToolPak

Excel Pivot Table- Tips & Techniques

Excel VBA - Last Column or Last Row

Find last column of a row, blank and very next to last updated cell

Sometimes in a sheet where we have a data in grid/table, we might want to periodically add data (pulled from somewhere dynamically) in very last column of that grid. For that we first need to identify the very last column on left which is blank and available for new entry.

We can use below VBA code. This will identify the column blank along the last populated column and will update word "Salary" as header there.

So if in Sheet1, we have some data in range A1 to F15, the below code if run, will update word "Salary" in Cell G1.

This is very simple, please try it and in case of any issue or question, please feel free to contact me.

Sub sbLastColumnOfARow()

Sheets("Sheet1").Select

With ActiveSheet

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

LastheadcolumnFinal = Lastheadcolumn + 1

End With

NewcolumnLabel = "Salary"

Cells(1, LastheadcolumnFinal).Value = NewcolumnLabel

End Sub

Similarly finding the last populated row in a column is also easy through below code syntax.

'Find the last non-blank cell in column A(1)
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row

And below code is also on same lines. This will add the active cell's value in another sheet's specified column and in the next blank cell and in another respective column's same cell the value of 1 and will take back to the active cell again.

Sub ProductUpdate()

AddProduct = ActiveCell.Value

mysheet = ActiveSheet.Name

mycell = ActiveCell.Address

Sheets("Bill").Select

LastRow = Cells(Rows.Count, 5).End(xlUp).Row + 1

Cells(LastRow, 5).Value = AddProduct

Cells(LastRow, 7).Value = 1

Sheets("Products").Select

Sheets(mysheet).Range(mycell).Select

End Sub

Excel Format Painter > formatting to an entire column/row

Format Painter is the most helpful feature to copy formatting from one cell and applying to others.

With a couple of clicks, we can copy formatting like number format, font, size, color, cell background color, cell borders and text alignment etc.

The feature is available on the home tab in the clipboard group.

Basic use is very simple. Select the cell whose formatting you want to copy and click on Format Painter button. The pointer will change to brush. Now click on the cell where you want to apply the formatting.

Simple way to copy cell formatting to an entire column/row.

• Select the cell with the desired format and press Ctrl + C to copy its contents and formats
• Select the entire column or row which we want to format by clicking on its heading
• Right click the selection and under paste options (Excel 2013), select the option ‘Formatting’. 

This way we will copy just the formatting in entire column, easily.