Microsoft Excel Tips

19/09/2023

https://whatsapp.com/channel/0029Va9W9Rw0AgW8DGbUpM2L

11/01/2023

Excel Visualization Reporting & Analysation.

In an Organization, especially in Finance department strongly is recommended Excel Reporting.
For Excel Reporting if we use Charts, it will be a better reporting.
There are many types of Charts on excel. Here I am listing major users’ charts.
- Column Charts: - Clustered Column, Stacked Column, etc
- Line Charts: - Line, Stacked Line, etc
- Pie Charts: Pie, 3D Pie, Doughnut, etc
- Area Chart
- Histogram
- Etc…
Before generating a chart, need to summarize the data better through Pivot Table.
After Data summarized, you can select your desired Chart and other Options (Below listed)

Other Options are:-
- Slicer
- Timeline, etc.
So this will be a visualized reporting and more visible reporting.

Next post will update you some examples for generating chart report.

09/11/2020

Excel Backup while saving the file - MACRO VBA CODE BELOW:

Step 1 : Open Your excel file.
Step 2 : Do the Save as the file - Choose Save type - Macro enables work Book. ( See the first Picture )

Step 3 : Press Alt+F11 (Press the Key button together )
Step 4 : Double Click on “This Work book”
Step 5 : Select “Work Book” (Left side ) and Select “Before Save” (Right Side)
Step 6 : Delete the Data ( Press Ctrl + A and Press Delete Button)
Step 7 : Paste the VBA Code Below;

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Call Save_Backup("")
End Sub
Sub Save_Backup(ByVal Backup_Folder_Path As String)
Dim fso As Object
Dim ExtensionName As String, FileName As String
Dim wbSource As Workbook
Set fso = CreateObject("Scripting.FileSystemObject")
Set wbSource = ThisWorkbook
ExtensionName = fso.GetExtensionName(wbSource.Name)
FileName = Replace(wbSource.Name, "." & ExtensionName, "")
fso.CopyFile ThisWorkbook.FullName, _
fso.BuildPath(Backup_Folder_Path, FileName & " (" & Format(Now(), "mm-dd-yy hhmmssAM/PM") & ")." & ExtensionName)
Set fso = Nothing
Set wbSource = Nothing
End Sub

Step 8 : Copy the Backup folder address
Step 9 : Select the text inside VBA Code and past the address.

Step 10 : Save the file.

Now whenever you save , back files will be generated.
You can check the files on the backup folder.

29/08/2020

Hi, Just now started a You Tube Channel - "CH-TECH"

Please look below link for a introduction video 41 seconds.

subject Video thoughts - Accounting, Excel, Tally , Video & Audio Editing.

ACCOUNTING MS EXCEL TALLY VIDEO & AUDIO EDITING https://www.facebook.com/msexcelskill

28/08/2020

Sum By Color and Count By Color using VBA in Microsoft Excel

Press Alt+F11 from Excel for VBA
Once opened VBA , instert a Module
Enter the following code into VBA.

Function SumByColor(CellColor As Range, SumRange As Range)
Application.Volatile
Dim ICol As Integer
Dim TCell As Range
ICol = CellColor.Interior.ColorIndex
For Each TCell In SumRange
If ICol = TCell.Interior.ColorIndex Then
SumByColor = SumByColor + TCell.Value
End If
Next TCell
End Function

To use the function type =sumbycolor(B1,A1:A10) - where B1 is the cell reference that contains the background color that you want to count and A1:A10 is the sum range.

Tip - The cell reference containing the color can be the same as the cell reference containing the formula.

Caution - The formula does not automatically recalculate when you apply a new interior color to a cell - however, I have made this custom function Volatile which means it will recalculate every time a cell value changes anywhere within the workbook.

With only a slight modification to the VBA code you can alter this custom function to count by color.
The VBA code for a custom function that will count by color is:

Function CountByColor(CellColor As Range, CountRange As Range)
Application.Volatile
Dim ICol As Integer
Dim TCell As Range
ICol = CellColor.Interior.ColorIndex
For Each TCell In CountRange
If ICol = TCell.Interior.ColorIndex Then
CountByColor = CountByColor + 1
End If
Next TCell
End Function

30/10/2017

Headers and footers in a worksheet.

You can add headers or footers at the top or bottom of a printed worksheet in Excel. For example, you might create a footer that has page numbers, the date, and the name of your file. You can create your own, or use many built-in headers and footers.

Headers and footers are displayed only in Page Layout view, Print Preview, and on printed pages. You can also use the Page Setup dialog box if you want to insert headers or footers for more than one worksheet at a time. For other sheet types, such as chart sheets, or charts, you can insert headers and footers only by using the Page Setup dialog box.

24/10/2017

How to Set or Remove a Password in Excel

12/07/2017

Excel Daily Useful & Important Shortcuts.