Google Sheets Template for Contact Information Tracking

I created this Dashboard for personal use to quickly locate contact information without having to sift through numerous pages or websites. This simple and easy-to-use Google Sheets tool allows me to find information for any Branch, Sub-Branch, or Division with just a click of a button. It can be accessed from any device—mobile, laptop, or desktop—and can be shared with anyone. Data scraping tools have been utilized to gather information from the respective organization's website. Advanced Google functions like IMPORTHTML, IMPORTXML, VSTACK, XLOOKUP, and FILTER have been used to create the Information Tracker. This eliminates the need to repeatedly visit websites and navigate through different pages to find information. 

For Free Template or Service, Please contact rubeliba@gmail.com 

Excel Template for Loan Amortization Schedule - Loan Calculator

 I have developed an easy-to-use loan amortization schedule in Excel, utilizing advanced functions to simplify my daily tasks. Relying on internet templates for loan amortization can be unreliable and inefficient. By customizing this schedule, I have tailored it to meet my specific needs, making my work more efficient and accurate.

For Free Template or Service, Please contact rubeliba@gmail.com 

Excel Template for Commercial Invoice

This straightforward MS Excel workbook simplifies the process of creating invoices, allowing users to do so quickly and effortlessly. Users can fill out a Data Entry form, selecting repetitive items from drop-down lists. Manual calculations are unnecessary, thanks to the use of FILTER and XLOOKUP functions. Data is automatically linked to the Invoice page, eliminating the need for further edits or page setup. After completing the Data Entry Form, simply click the Print button to generate a polished A4-sized printout.

For Free Template or Service, Please contact rubeliba@gmail.com 

Excel Template for Bill of Lading

This user-friendly MS Excel workbook streamlines the creation of a Bill of Lading, making the process both quick and simple. Users need to complete a Data Entry form, where repetitive items can be easily chosen from drop-down lists. With the incorporation of FILTER and XLOOKUP functions, manual calculations are eliminated. The data seamlessly integrates with the Bill of Lading page, so no additional edits or page setup are required. Once the Data Entry Form is completed, just click the Print button to obtain a professional A4-sized printout.

For Free Template or Service, Please contact rubeliba@gmail.com 

Excel Template for Bill of Exchange

This intuitive MS Excel workbook simplifies the process of creating a Bill of Exchange, making it efficient and hassle-free. Users can fill out a Data Entry form, with repetitive items easily selectable from drop-down lists. The use of FILTER and XLOOKUP functions ensures that no manual calculations are needed. Data is automatically linked to the Bill of Exchange page, removing the need for additional edits or page setup. After completing the Data Entry Form, just click the Print button to produce a polished A4-sized printout. 

For Free Template or Service, Please contact rubeliba@gmail.com 

Excel Template for Purchase Order

This user-friendly MS Excel workbook streamlines the creation of a Purchase Order, making the process efficient and straightforward. Users can complete a Data Entry form, selecting repetitive items from convenient drop-down lists. With the integration of FILTER and XLOOKUP functions, manual calculations are unnecessary. The data seamlessly integrates with the Purchase Order page, eliminating the need for further edits or page setup. After completing the Data Entry Form, simply click the Print button to generate a professional A4-sized printout. 

For Free Template or Service, Please contact rubeliba@gmail.com 

Excel Template for Proforma Invoice

This MS Excel file has been designed to enable users to effortlessly and swiftly create a Proforma Invoice. By completing a user-friendly data entry form, where a few items can be selected from drop-down lists, and then clicking a button, users can immediately print a perfectly formatted invoice, thanks to pre-set page setup and formatting. Additionally, this file can be linked with pre-saved data for even greater convenience. For those who prefer alternative platforms, this format is easily adaptable to MS Word, Google Sheets, or Google Docs, ensuring flexibility and ease of use.

For Free Template or Service, Please contact rubeliba@gmail.com 

Excel Template for Packing List

This straightforward and user-friendly MS Excel template allows users to create a Packing List from scratch by entering data into the data entry form. If desired, the template can also be used with pre-saved data. Printing is hassle-free—simply complete the data entry form and click the print button to get an A4-sized printout. Additionally, the template includes a supplementary sheet with valuable information about the contents of the Packing List.

For Free Template or Service, Please contact rubeliba@gmail.com 

Excel Template for Invoice

This MS Excel template enables users to create a Invoice by entering data directly into a Data Form. It can be customized with pre-saved data and includes drop-down lists to minimize errors. Since buyers and sellers often remain the same, users can utilize multiple drop-down lists for these entries as well. The template also supports mail merging, making it even more convenient and user-friendly. Additionally, this file contains valuable information about various aspects of invoicing, as well as a comprehensive list of countries and currencies. 

For Free Template or Service, Please contact rubeliba@gmail.com 

Excel Template for Blood Pressure Tracking

This Excel file allows users to record their blood pressure logs. Daily data is collected, accumulated, and presented through various charts and tables. If any data exceeds a certain threshold, the corresponding cell will automatically turn red to indicate that the level has been crossed. Additionally, there is a separate section to alert users when it is time to consult a doctor. Advanced Excel functions such as XLOOKUP, SUMIFS, FILTER, UNIQUE, and CONDITIONAL FORMATTING have been utilized in this file. 

For Free Template or Service, please contact rubeliba@gmail.com

Excel Template for Blood Sugar Tracking

This Excel template is designed for tracking blood sugar levels. It collects daily data and presents it through charts and tables. If any recorded value exceeds a specified threshold, the corresponding cell automatically changes color to indicate the exceedance. The cell value turns red to alert users when it's advisable to consult a doctor. Advanced Excel functions like XLOOKUP, FILTER, UNIQUE, and CONDITIONAL FORMATTING have been incorporated into the template.

For Free Template or Service, Please contact rubeliba@gmail.com 

Excel Template for Activity Tracker and Calories Burned

This Excel file enables users to track their daily activities and calories burned. The data is collected and accumulated daily, then presented through various charts and tables. Users can search for information by day, week, or month. Additionally, the types of activities performed are summarized in the presentation. Advanced Excel functions such as XLOOKUP, FILTER, UNIQUE, SUMIFS, and CONDITIONAL FORMATTING have been utilized in this file. For Free Template or Service, please contact rubeliba@gmail.com 

For Free Template or Service, Please contact rubeliba@gmail.com 

Excel Template for Exercise Log

This Excel template is tailored for monitoring daily exercise and tracking calories burned. It offers a concise summary of activities performed, highlighting common exercises users frequently undertake. The template compiles daily data into clear charts and tables for effortless monitoring. Users can easily search by day, week, or month to assess their progress. Advanced Excel functions, including XLOOKUP, FILTER, UNIQUE, SUMIFS, and CONDITIONAL FORMATTING, are seamlessly integrated to improve usability and facilitate progress tracking.

For Free Template or Service, Please contact rubeliba@gmail.com 

Excel Template for Nutrition Tracking

This Excel template empowers users to meticulously track their daily food intake. It systematically collects data daily, presenting it comprehensively through diverse charts and tables. Users can effortlessly search and analyze information by day, week, or month. Each food item's ingredients are meticulously recorded and displayed separately for clarity and detailed analysis. Leveraging advanced Excel functions like XLOOKUP, FILTER, UNIQUE, and CONDITIONAL FORMATTING, this template ensures efficient data management and insightful nutritional tracking. 

For Free Template or Service, Please contact rubeliba@gmail.com 

Excel Template for Cost of Goods Sold (COGS) Statement

This user-friendly Excel file simplifies the process of preparing your Cost of Goods Sold (COGS) Statement. Users can effortlessly input data directly into the COGS sheet. For enhanced functionality, a macro-enabled data entry form can be added to automatically gather and organize data in a separate table for in-depth analysis. The template is easily customizable, allowing you to add more items as needed. Experience the ease of managing your COGS with this intuitive tool.

For Free Template or Service, Please contact rubeliba@gmail.com

Excel Template for Simple Balance Sheet Creation

This Excel file enables users to record their personal or small business finances. It is specifically designed for individuals without accounting knowledge but with a basic understanding of assets and liabilities. Users can input asset and liability information into two separate sheets/tables, and the balance sheet will be automatically generated. The file includes graphical presentations to visualize and compare current year's financial position to the previous year. It is very easy to use. For Free Template or Service, please contact rubeliba@gmail.com 

For Free Template or Service, Please contact rubeliba@gmail.com 

Excel Template for Financial Ratio Analysis

In this project, I've developed an Excel Template to automate the generation of financial ratios from the Statement of Earnings (Profit & Loss Statement) and Balance Sheet. This template eliminates the need for manual calculations; ratios are automatically derived based on data input into the two statements. Users simply complete the Statement of Earnings and Balance Sheet, and the ratios are instantly calculated. The template ensures accuracy by linking directly to the data in the statements, using simple Excel formulas to create a user-friendly and efficient tool. This template is a part of my complete accounting solution.

For Free Template or Service, Please contact rubeliba@gmail.com

Excel Tips: How to Add Printing Button in a Worksheet - one for Print to PDF and another for Print to Device

Problem: How can you add a button for PDF printing and another for printing in device. 

Solution: You can add buttons for PDF printing and printing directly to a device in Excel by using VBA. Here's how to create these buttons and assign the necessary macros:

1. Enable the Developer Tab (if it's not already enabled):

   • Go to File > Options.
   • Select Customize Ribbon.
   • In the right pane, check the Developer checkbox.
   • Click OK.

2. Add Buttons to the Worksheet:

   • Go to the Developer tab.
   • Click Insert in the Controls group.
   • Under Form Controls, click the Button (Form Control) icon.
   • Click and drag on the worksheet to draw the button.
   • Repeat to create two buttons, one for PDF printing and one for direct printing.

3. Assign Macros to the Buttons:

   • After drawing each button, the Assign Macro dialog will appear.
   • Click New to create a new macro for each button, which will open the VBA editor.

4. Write the VBA Code:

   • In the VBA editor, you will see something like this:

     vba
     Sub Button1_Click()
     '
     ' Button1_Click Macro
     '
     ' 
     End Sub
     

   • Modify the code for the PDF printing button:

     vba
     Sub PrintToPDF()
         Dim ws As Worksheet
         Dim pdfPath As String
         
         ' Set the path where the PDF will be saved
         pdfPath = ThisWorkbook.Path & "\MyWorksheet.pdf"
         
         ' Export the active sheet to PDF
         ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=pdfPath, _
             Quality:=xlQualityStandard, IncludeDocProperties:=True, _
             IgnorePrintAreas:=False, OpenAfterPublish:=True
     End Sub
     

   • Modify the code for the direct printing button:

     vba
     Sub PrintToPrinter()
         ' Print the active sheet
         ActiveSheet.PrintOut
     End Sub
     

   • Close the VBA editor.

5. Assign the Macros to the Buttons (if not automatically assigned):

   • Right-click the PDF printing button.
   • Select Assign Macro.
   • Choose the macro you created for PDF printing (e.g., PrintToPDF).
   • Click OK.
   • Repeat for the direct printing button, assigning it the macro for direct printing (e.g., PrintToPrinter).

Now, you will have two buttons on your worksheet. Clicking the PDF button will save the active sheet as a PDF file, and clicking the print button will print the active sheet directly to your default printer.

Excel Tips: How to Add Refresh Button in a Excel Workbook

Problem: Hoe to Add a Refresh Button Button in an active sheet to automatically refresh Pivot Table (s) without going to Data Tab or Pressing Alt + F5 or Ctrl + Alt + F5. 

Solution: You can add a refresh button directly on your worksheet in Excel 2021. Here's how to do it:

1. Enable the Developer Tab (if it's not already enabled):

   • Go to File > Options.
   • Select Customize Ribbon.
   • In the right pane, check the Developer checkbox.
   • Click OK.

2. Add a Button to the Worksheet:

   • Go to the Developer tab.
   • Click Insert in the Controls group.
   • Under Form Controls, click the Button (Form Control) icon.
   • Click and drag on the worksheet to draw the button.

3. Assign a Macro to the Button:

   • After drawing the button, the Assign Macro dialog will appear.
   • Click New to create a new macro, which will open the VBA editor.

4. Write the VBA Code:

   • In the VBA editor, you will see something like this:

     vba
     Sub Button1_Click()
     '
     ' Button1_Click Macro
     '
     ' 
     End Sub
     

   • Modify it to refresh all data connections and pivot tables:

     vba
     Sub Button1_Click()
         Dim ws As Worksheet
         ' Refresh all pivot tables in all sheets
         For Each ws In ThisWorkbook.Worksheets
             Dim pt As PivotTable
             For Each pt In ws.PivotTables
                 pt.PivotCache.Refresh
             Next pt
         Next ws
         ' Refresh all data connections
         ThisWorkbook.RefreshAll
     End Sub
     

   • Close the VBA editor.

5. Assign the Macro to the Button (if not automatically assigned):

   • Right-click the button.
   • Select Assign Macro.
   • Choose the macro you just created (e.g., Button1_Click).
   • Click OK.

Now, whenever you click the refresh button on your worksheet, it will refresh all pivot tables and data connections in your workbook.

Excel Function: Generate Auto Serial Number in Excel

Problem: If you  want to generate auto Serial Number in Column A starting from A2 depending on having data from Column B from B2. If nothing is inserted in Column B, Column A will remain blank. How can you do it?

Solution: To generate an auto serial number in Column A starting from A2, depending on the presence of data in Column B from B2 onwards, you can use the following formula in Excel:

1. Using a Formula in Column A:
   1. Click on cell A2.
   2. Enter the following formula:

      excel
      =IF(B2<>"", ROW(B2)-1, "")
      

   3. Drag the fill handle down to cover the range you need in Column A.

This formula checks if there is any data in Column B. If there is, it will generate a serial number based on the row number minus 1. If there is no data, it will leave the cell blank.

2. Using VBA: If you prefer to use VBA to automate this process, follow these steps:
   1. Press Alt + F11 to open the VBA editor.
   2. Insert a new module by clicking Insert > Module.
   3. Copy and paste the following code into the module:

      vba
      Sub GenerateSerialNumbers()
          Dim i As Integer
          Dim lastRow As Long
          lastRow = Cells(Rows.Count, 2).End(xlUp).Row ' Find the last row with data in Column B
          
          For i = 2 To lastRow
              If Cells(i, 2).Value <> "" Then
                  Cells(i, 1).Value = i - 1
              Else
                  Cells(i, 1).Value = ""
              End If
          Next i
      End Sub
      

   4. Close the VBA editor.
   5. Press Alt + F8, select GenerateSerialNumbers, and click Run.

These methods will ensure that Column A generates serial numbers only when there is data in the corresponding cells in Column B. If Column B is empty, Column A will remain blank.

Excel Function: How to Create Number Sequence in Excel - Populate Column With Numbers

Problem: If you want to generate number 1 to 2000 in column number A. Number will start from A2. How can you do it?

To generate numbers from 1 to 2000 in column A starting from cell A2 in Excel 2021, you can follow these steps:

1. Using Fill Handle:

   1. Type 1 in cell A2.
   2. Type 2 in cell A3.
   3. Select cells A2 and A3.
   4. Drag the fill handle (a small square at the bottom-right corner of the selection) down until you reach cell A2001.

2. Using the SEQUENCE Function:

   1. Click on cell A2.
   2. Enter the following formula:

      excel
      =SEQUENCE(2000, 1, 1, 1)
      

   3. Press Enter. This will generate numbers from 1 to 2000 in column A starting from A2.

3. Using VBA: If you prefer to use VBA, you can do so by following these steps:

   1. Press Alt + F11 to open the VBA editor.
   2. Insert a new module by clicking Insert > Module.
   3. Copy and paste the following code into the module:

      vba
      Sub GenerateNumbers()
          Dim i As Integer
          For i = 1 To 2000
              Cells(i + 1, 1).Value = i
          Next i
      End Sub
      

   4. Close the VBA editor.
   5. Press Alt + F8, select GenerateNumbers, and click Run.

These methods will help you populate column A with numbers from 1 to 2000 starting from cell A2.

Comprehensive Excel Template for Efficient Risk Grading

This straightforward Excel template utilizes various parameters to assess the risk grading of a business unit. As a banker, I have selected parameters primarily related to financial and security risks. However, these parameters can be customized to fit specific needs and industry practices. The template is user-friendly and easy to use. While it does not employ advanced Excel formulas, it includes numerous nested IF functions, which can be time-consuming to create manually. This ready-to-use template allows for hassle-free risk assessment of any business entity. 

For Free Template or Service, Please contact rubeliba@gmail.com 

Excel Template for Efficient Branch Mail Tracking

This project allows users to efficiently track incoming mail from various units. By simply checking a box, the sending branch is automatically marked with a strikethrough and its color is changed. Additionally, the top of the tracker provides a summary of the number of sending and non-sending branches. This tool is user-friendly and easy to use, utilizing the XLOOKUP function and conditional formatting. 

For Free Template or Service, Please contact rubeliba@gmail.com 

Barcode Generation in Excel

In this straightforward project, I have developed an Excel workbook capable of automatically generating barcodes without the need for complex formulas or numerous steps. Users simply need to enter a number in one field, and the corresponding barcode will be generated in the next field. This form is versatile and can handle barcode generation for any volume. The creation of this barcode generator relies on a simple Excel formula and a specific font, which users must download from the web before using the workbook. This tool is suitable for both small and large businesses. 

For Free Template or Service, Please contact rubeliba@gmail.com