Featured post

Trick to get the sum row wise or column wise

    Row-wise or Column-wise Sum Using a Shortcut: Select the range of numbers in the table that you want to sum (either a column or a row, or even multiple columns or rows). Press the Alt key on your keyboard. After pressing Alt , press the +   = key . This is the AutoSum shortcut. If you're summing a column , Excel will automatically fill the cell directly below the selected range with the sum of that column. If you're summing a row , Excel will automatically fill the cell directly to the right of the selected range with the sum of that row.   The AutoSum feature ( Alt + = ) automatically sums numbers in a contiguous range of cells.    
1 comment

AUTO BORDER IN EXCEL


1. Quick Auto Border for Entire Table

To quickly add borders to an entire table or selection, use the Format as Table option:

    • Select the range of cells you want to format.
    • Go to the Home tab on the ribbon.
    • Click on Format as Table and choose a style.
    • This automatically applies borders around your data, giving your table a clean, organized look. You can adjust the colour or style of the border if needed.

2. Using Conditional Formatting for Borders

Excel’s Conditional Formatting tool lets you apply borders based on specific conditions, which is great for automatically highlighting certain data:

    • Select the cells you want to format.
    • Go to Home > Conditional Formatting > New Rule.
    • Choose a rule (e.g., cells greater than a specific value).
    • Under the Format button, click on Borders and choose your desired border style.
    • The borders will automatically adjust based on the rule you've set.

3. Auto Borders with Table Styles

When you insert a Table (using Ctrl + T), Excel automatically applies borders to all the cells in the table. You can customize the table style:

    • Select any cell in the table.
    • Go to Table Design on the ribbon and choose from various predefined styles.
    • You can further customize by clicking Borders from the Font group and selecting the appropriate border style.

4. Using the Fill Handle for Quick Borders

Quickly apply borders to adjacent cells using the Fill Handle:

    • Select a range of cells with borders.
    • Hover over the small square at the bottom-right corner of the selection (the Fill Handle).
    • Drag to extend the selection to neighbouring cells, and Excel will copy the border formatting along with the content.

5. Adding Borders to Empty Cells

If you want to add borders to empty cells automatically:

    • Select the entire range where you want to add borders (including empty cells).
    • Go to Home > Borders and select All Borders. This will apply borders to both filled and empty cells within the selected range.

6. Keyboard Shortcuts for Borders

Use these keyboard shortcuts to speed up your workflow when applying borders:

    • Ctrl + Alt + 0: Add or remove borders around the selected cells.
    • Alt + H + B + A: Apply All Borders to the selection.
    • Alt + H + B + T: Apply Thick Box Border to the selected range.

7. Automatically Add Borders for Data Validation

If you're working with data validation (e.g., drop-down lists), you can use borders to highlight valid entries:

    • Select the cell or range where you applied data validation.
    • Set up your data validation rules under Data > Data Validation.
    • Add borders to these cells so that users can easily identify which cells require input.

8. Using VBA for Advanced Auto Border Features

For more advanced users, VBA (Visual Basic for Applications) can create custom macros to apply borders automatically based on specific criteria. Here’s a simple script to add borders:

 VBA.......

Sub AutoBorders()

    Dim rng As Range

    Set rng = Selection

    With rng.Borders(xlEdgeBottom)

        .LineStyle = xlContinuous

        .ColorIndex = 0

        .TintAndShade = 0

        .Weight = xlThin

    End With

    ' Repeat for other borders as needed

End Sub

This script adds a thin continuous bottom border to your selected range. You can customize it to apply borders on other edges or with different styles.

9. Adjusting Border Styles

Once borders are applied, you can adjust the styles:

    • Click the Borders button in the Font group on the Home tab.
    • Choose from a variety of border styles: solid, dotted, dashed, thick, etc.
    • Customize the thickness and colour to suit your needs.


 

Comments

Post a Comment

POPULAR POSTS

essential BAsic excel formulas a quick guide

Image
  Here are some tips for using Excel formulas: All formulas start with an equal sign (=) To refer to a cell, enter the column letter followed by the row number. For example, B2 refers to the cell at the intersection of column B and row 2.           The formula bar shows the formula you enter in a cell. You can see it whenever that cell is selected. Here are some basic Excel formulas you can use for common tasks: 1. SUM Adds a range of numbers. =SUM(A1:A10) 2. AVERAGE Calculates the average of a range of numbers. =AVERAGE(B1:B10) 3. COUNT Counts the number of cells that contain numbers. =COUNT(C1:C10) 4. MAX Finds the maximum value in a range. =MAX(D1:D10) 5. MIN Finds the minimum value in a range. =MIN(E1:E10) 6. IF Performs a logical test and returns one value for TRUE and another for FALSE. =IF(F1 > 10, "Yes", "No") 7. CONCATENATE (or &) Joins two or more strings together. =CONCATENATE(G1, " ", H1) =G1 & " " & H1 8...
Read more

Some important tips to learn MS Word

Image
    Some important tips to learn MS Word   ·       Steps:           Open Word   ·         Click   start   ·         click   program   ·         Click   MS word   Steps:    Find ·         click on edit menu ·         click find ·         Type the text you want to find ·         Click on Find Next button.   Steps: Replace ·           Click on the Edit menu ·         click replace ·         Type the text you want to replace on the first line ·         On the second line, type the text you want to replace on the first line ·         Click on the Replace All button.   Steps: Go to ·           Click on t...
Read more

Insert Serial Numbers in Roman Numerals, Alphabets, and Numbers in Excel

Image
  How to Insert Serial Numbers in Roman Numerals, Alphabets, and Numbers in Exce l To easily generate serial numbers in Roman numerals, alphabets, or numbers, follow these simple formulas in Excel: SL NO in  Roman Numerals: Click on the cell where you want to start the serial number (e.g., A1). Enter the following formula: =ROMAN(ROW(A1)) After entering the formula, drag the fill handle (small square at the bottom-right corner of the cell) down to apply it to additional cells. SL NO in Alphabets Click on the cell where you want the alphabet sequence to begin (e.g., A1). Enter this formula: =CHAR(64 + ROW(A1)) Drag the fill handle down to continue the alphabet sequence.    Sl no in Numbers: Click on the cell where you want to start numbering (e.g., A1). Enter the formula: =ROW(A1) Drag the fill handle down to generate a sequence of numbers. By using these simple formulas, you can quickly generate serial numbers in Roman numerals, alphabets, or numbers across your spre...
Read more

How to find sum of numbers with units in a range in Excel?

Image
  How to find sum of numbers with units in a range in Excel? In Excel, you can use a formula to handle this task. Select a blank cell, and type this formula =SUM(SUBSTITUTE(B1:B7, "kg", "") + 0) into it, and then press  Shift + Ctrl + Enter  keys simultaneously to get the correct result. OR USE THIS  FORMULA  SELECT A BLANK CELL AND TYPE THIS FORMULA=SUM(--LEFT(B1:B7,2)) USE CTL+SHIFT+ENTER . A                                   B APPLE 20KG PEAR 30KG MANGO 10KG BANANA 11KG GRAPES 50KG ORANGE 22KG POTATO 30KG   173 = SUM(--LEFT(B1:B7,2)) USE CTL+SHIFT+ENTER Tip:  in above formula, B1:B7 is the numbers with units you want to sum up, kg is the unit in the data ...
Read more

Rearrange columns in excel sheet using a quick trick

Image
    Rearrange columns in excel sheet using a quick trick     No need to copying, pasting, or dragging , just used a simple trick  Select all the table by pressing CTRL +A, This highlight all the cells in your sheet. Next press  ALT +HSU,  This opens the  sorting dialouge box,. In this dialogue box click on option and selct left to right Now choose the row you want to sort by. you will get a  revised table of data as per your need.   scan
Read more

TIPS TO INSERT A SERIAL NUMBER IN AN EXCEL SHEET

Image
  TIPS TO INSERT A SERIAL NUMBER IN AN EXCEL SHEET USING A COMAND AND TRICKS             To insert a serial number (SL NO) in an Excel sheet using a command, you can use a simple formula. Here’s how you can do it: Open Excel and navigate to the cell where you want to start your serial numbers (e.g., A1).   Enter the Formula : In cell A1, type 1 to start the numbering. In cell A2, type the formula =A1 + 1 . This will add 1 to the value above.   AutoFill the Series : Click on the small square at the bottom-right corner of cell A2 (the fill handle). Drag it down to fill the cells below with sequential numbers as you desired. 1       =A1+1                 TRICKS   TO INSERT...
Read more