Steps to Enable the Developer Tab in Excel:

For Excel 2016, 2019, 2021, and Excel for Microsoft 365:

1. Open Excel.

2. Click on the "File" Tab in the top left corner.

3. In the menu that appears, click on "Options" at the bottom (this opens the Excel Options dialog box).

4. In the Excel Options window, select "Customize Ribbon" from the list on the left.

5. On the right side, under the section Main Tabs, you’ll see a list of tabs available in the ribbon. Find and check the box next to "Developer".

6. Click OK to close the dialog box.

For Excel 2013:

The steps are very similar:

1. Click on the "File" Tab in the top-left corner.

2. Click "Options" at the bottom of the menu.

3. In the Excel Options dialog box, select Customize Ribbon on the left.

4. On the right, check the box next to Developer under the Main Tabs section.

5. Click OK to save your changes.

For Excel 2010:

1. Click on the "File" Tab, and then click Options.

2. In the Excel Options window, select Customize Ribbon from the list on the left.

3. On the right side, check the Developer box under Main Tabs.

4. Click OK.

Once the Developer Tab is Enabled:

• You will see the Developer tab added to the Excel ribbon.
• The Developer tab will give you access to tools like:
  • Visual Basic (VBA) editor (for creating and editing macros).
  • Macros (record, run, and manage macros).
  • Form Controls (buttons, checkboxes, etc.).
  • Add-ins (for managing Excel add-ins like Power Pivot).
  • XML Source (for working with XML data).
  • Controls for creating user forms and ActiveX controls.

Summary of Steps:

1. Go to File > Options.
2. Select Customize Ribbon.
3. Check the box next to Developer.
4. Click OK.

Excel macros are written in VBA (Visual Basic for Applications), which is a programming language that allows for greater customization and flexibility in automating tasks.

How to Record a Macro in Excel

Recording a macro is an easy and accessible way to automate tasks without needing to write any VBA code. Here’s how you can record a macro in Excel:

Step 1: Enable the Developer Tab

Before you can record a macro, you need to make sure the Developer tab is visible in Excel. It’s not displayed by default, so you’ll need to enable it:

1. Open Excel.
2. Click on the "File" tab, then select Options at the bottom.
3. In the Excel Options window, select Customize Ribbon on the left.
4. On the right, under Main Tabs, check the box next to Developer.
5. Click OK.

The Developer tab should now be available in the Excel ribbon.

Step 2: Start Recording a Macro

1. Go to the Developer tab.

2. In the Code group, click Record Macro. The Record Macro dialog box will open.

3. In the dialog box:

   • Macro Name: Give your macro a name (e.g., "FormatData" or "SortData"). Macro names must start with a letter, and they cannot contain spaces or special characters.
   • Shortcut Key (optional): Assign a keyboard shortcut to the macro (e.g., Ctrl + Shift + F). Be cautious with the shortcut key—if you choose one that is already used in Excel, it will override that function.
   • Store Macro In: Choose where to store the macro:
     • This Workbook: Saves the macro in the current workbook (only available for this workbook).
     • New Workbook: Saves the macro in a new workbook.
     • Personal Macro Workbook: Saves the macro in a hidden workbook that opens automatically with Excel. This option allows you to use the macro across all workbooks you open.
   • Description (optional): You can add a description of what the macro does, which is helpful for documentation.

4. Click OK to begin recording.

Step 3: Perform Actions You Want to Automate

After clicking OK, Excel will start recording everything you do. This means every action you take (typing, clicking, formatting, etc.) will be included in the macro.

• For example, you might:
  • Select a range of cells.
  • Change the font size or color.
  • Apply a filter or sort data.
  • Insert a chart.
  • Delete rows or columns.

Important: Be careful not to make any unintended actions during the recording process, as they will be included in the macro.

Step 4: Stop Recording the Macro

Once you’ve finished performing the actions you want to automate:

1. Go back to the Developer tab.
2. In the Code group, click Stop Recording.

Your macro is now saved and ready to be executed whenever you need it.

Step 5: Running the Macro

You can run a macro either using the shortcut key (if you assigned one) or through the ribbon:

1. Using the Ribbon:

   • Go to the Developer tab.
   • In the Code group, click Macros.
   • In the Macro dialog box, select your macro name.
   • Click Run.

2. Using a Keyboard Shortcut:

   • If you assigned a shortcut key during the recording, simply press the shortcut (e.g., Ctrl + Shift + F) to run the macro.

Step 6: View and Edit the Macro (VBA)

If you want to view or edit the macro (for example, to modify the code or add more functionality), follow these steps:

1. Go to the Developer Tab.
2. Click Macros in the Code group.
3. Select the macro you want to view/edit and click Edit.

This opens the Visual Basic for Applications (VBA) editor, where you can see the VBA code generated by your actions. You can modify the code directly in the editor.

Example of VBA code generated by recording a simple macro:

Sub FormatData()
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Hello"
    Selection.Font.Size = 14
    Selection.Font.Color = RGB(255, 0, 0)
    Selection.Cells.HorizontalAlignment = xlCenter
End Sub

This code represents a macro that:

• Selects cell A1.
• Sets the value to "Hello".
• Changes the font size to 14.
• Changes the font color to red.
• Centers the text in the cell.

Tips for Working with Macros:

• Security: Macros can contain harmful code (especially those downloaded from untrusted sources), so always enable macros from trusted sources only. Excel has built-in macro security settings to help manage this.

• Debugging: If a macro doesn't work as expected, you can go to the VBA editor to debug it. You can step through the code line-by-line to find out where things went wrong.

• Personal Macro Workbook: If you want to use macros across multiple workbooks, storing them in the Personal Macro Workbook is a great option. This is a hidden workbook that opens automatically whenever you start Excel.

• Assign Macros to Buttons: You can make macros easier to execute by assigning them to buttons on your worksheet. Just go to the Insert button in the Developer tab, choose Button, and link your macro to it.

1. Formatting Font (Size, Color, Style)

• Select the cells you want to format.
• In the Home tab of the ribbon, you'll find the Font group, which includes various font formatting options.

Font Size

• Click the Font Size drop-down box to select a size, or type a number directly to set a custom size.

Font Color

• Click the Font Color button (it looks like a letter "A" with a color bar beneath it).
• Choose a color from the palette or select More Colors for custom colors.

Font Style (Bold, Italic, Underline)

• Use the Bold (B), Italic (I), or Underline (U) buttons in the Font group to apply these styles.
• You can also use keyboard shortcuts: Ctrl + B for bold, Ctrl + I for italic, and Ctrl + U for underline.

Font Type (Typeface)

• Click the Font Name drop-down list to select a different font type (e.g., Arial, Calibri, Times New Roman).

2. Formatting Cell Background (Fill Color)

• Select the cells you want to format.
• In the Home tab, in the Font group, click the Fill Color button (paint bucket icon).
• Choose a color from the color palette, or click More Colors for custom options.

3. Adding Borders to Cells

• Select the cells where you want to add borders.
• In the Home tab, in the Font group, click the Borders drop-down button (it looks like a square with four border lines).
• You can choose various types of borders, such as:
  • Bottom Border
  • Top Border
  • Left Border
  • Right Border
  • All Borders (adds borders around every cell in the selection)
  • Thick Box Border
  • Outside Borders
  • More Borders (opens the Format Cells dialog box for more options, like customizing line styles and colors).

4. Formatting Cell Alignment

Excel provides options for aligning text within a cell:

• Vertical Alignment: In the Alignment group (on the Home tab), you can adjust:

  • Top Align: Aligns the text at the top of the cell.
  • Middle Align: Centers the text vertically in the cell.
  • Bottom Align: Aligns the text at the bottom of the cell.

• Horizontal Alignment: Similarly, you can align text:

  • Left Align: Aligns the text to the left side of the cell.
  • Center Align: Centers the text horizontally in the cell.
  • Right Align: Aligns the text to the right side of the cell.

• Text Orientation: To rotate the text within a cell:

  • Click the Orientation button in the Alignment group, and select the rotation angle or click Format Cell Alignment for more options.

• Wrap Text: To make the text appear on multiple lines within the same cell (e.g., when the text is too long for the cell):

  • Click the Wrap Text button in the Alignment group.

• Merge Cells: To merge two or more cells into one large cell:

  • Select the range of cells you want to merge, then click Merge & Center in the Alignment group. You can also choose Merge Across or Merge Cells without centering the text.

5. Formatting Number, Date, and Time Styles

Excel provides specific formatting options for numbers, currency, dates, and percentages.

• Number Formatting:

  • In the Number group on the Home tab, select one of the options such as Number, Currency, Accounting, Date, Time, Percentage, or Fraction to format the data in the selected cells.

• Custom Number Formats:

  • Right-click the selected cell(s), choose Format Cells, and navigate to the Number tab to select a custom number format. You can create your own formats for numbers, dates, and times here.

6. Using the Format Cells Dialog Box

To access more advanced formatting options:

• Right-click on a cell or selection of cells and choose Format Cells, or press Ctrl + 1 (Windows) or Command + 1 (Mac).
• The Format Cells dialog box will appear, with several tabs for specific formatting options:
  • Number: Adjust number, currency, date/time, etc.
  • Alignment: Set horizontal and vertical alignment, text control (wrap, merge, shrink), and text direction.
  • Font: Change the font type, size, style, and color.
  • Border: Set the type of border (lines, colors, and positioning).
  • Fill: Set the background color or fill pattern.
  • Protection: Enable or disable cell protection (if you are planning to lock or protect the worksheet).

7. Using Themes and Styles

• Themes: You can apply a predefined set of fonts, colors, and effects across your worksheet.
  • Go to the Page Layout tab and choose a theme from the Themes group.
• Cell Styles: Predefined styles for headings, titles, and data.
  • On the Home tab, in the Styles group, click Cell Styles to apply a pre-defined style (like a title, header, or good/bad/neutral formatting).

8. Additional Formatting Tips

• Quick Styles: You can use the Quick Analysis tool (the icon that appears when you select a range of data) to apply formatting, charts, and other analysis features quickly.
• Conditional Formatting: In the Home tab, the Conditional Formatting button lets you apply formatting based on the values of the cells (e.g., highlighting cells that are greater than a certain number or using color scales).

Summary of Common Formatting Shortcuts:

• Bold: Ctrl + B
• Italic: Ctrl + I
• Underline: Ctrl + U
• Open Format Cells dialog: Ctrl + 1
• Align Left: Ctrl + L
• Align Center: Ctrl + E
• Align Right: Ctrl + R
• Increase Font Size: Ctrl + Shift + >
• Decrease Font Size: Ctrl + Shift + <

• INT
• CUMIPMT
• CINT
• CUMINT

• XLOOKUP
• VLOOKUP
• Data modelling (Power Pivot)
• Both Xlookup & Data modelling

• COSH
• HYPERCOS
• HYPERC
• COSHY

• Values area
• Filters area
• Rows area

1. Ctrl + F
2. Ctrl + H
3. Ctrl + R
4. All of the above