In this Excel training video, we’ll guide you through the essential process of identifying external links, such as links or references to other workbooks. Managing external links is crucial for maintaining data integrity and ensuring accurate calculations in complex Excel projects. We’ll explore three distinct methods to streamline this task, catering to various user preferences and scenarios.

Edit Links tool

Firstly, we’ll demonstrate the utilization of the Edit Links tool, a powerful feature within Excel that provides a comprehensive overview of all external links associated with your workbook. This tool not only reveals the connections but also allows for efficient management and updating, ensuring that your data remains current and error-free.

Find tool

Additionally, we’ll delve into the application of the Find tool, showcasing how you can quickly locate external links within your spreadsheet. This method is particularly useful for users seeking a straightforward and precise way to identify and navigate through links embedded in their Excel workbooks.

Conditional Formatting

Finally, we’ll show you how to use Conditional Formatting to visually highlight cells containing external links, offering a visual cue that aids in quick identification and enhances the overall clarity of your Excel project.

Resources

Example of a correctly formatted external link:

='https://optionbox-my.sharepoint.com/personal/john_optionbox_co_uk/Documents/[Names.xlsx]Sheet1'!$A$1

• Ensure that the filename and extension are in square brackets [filename.xlsx]
• Change Sheet1 to the name of the sheet you’re linking to.
• !$A$1 refers to the cell you’re linking to. If you were linking to cell B2 you would use !$B$2

Formula to highlight external links in conditional formatting:

=ISNUMBER(SEARCH(".xlsx", FORMULATEXT(A1)))

In this Excel training video, we’ll guide you through the process of retrieving the day of the week in Excel by leveraging the platform’s pre-existing functions. Excel provides a range of built-in functions designed to simplify data manipulation, and in this tutorial, we’ll demonstrate step-by-step instructions to help you seamlessly incorporate the day of the week into your spreadsheets.

Furthermore, we’ll look into the powerful realm of conditional formatting to showcase how you can visually emphasize specific days within your Excel sheets. By the end of the video, you’ll not only be adept at extracting the day of the week but also have a newfound skill in using conditional formatting to elevate the visual impact and functionality of your Excel spreadsheets.

Resources

Day of week using TEXT (Mon, Tue, Wed…):

 =TEXT(A1, "ddd")

Day of week using TEXT (Monday, Tuesday, Wednesday…):

=TEXT(A1, "dddd")

Day of week using WEEKDAY (1 [Mon], 2 [Tue], 3 [Wed]…):

=WEEKDAY(A1, 2)

Custom day of week using WEEKDAY:

=CHOOSE(WEEKDAY(A1, 2), "Mon", "Tues", "Weds", "Thur", "Fri", "Sat", "Sun")

In this Excel training video, we guide you through the process of enabling the developer tab in both Windows and Mac desktop versions of Excel. We’ll also show you how to use some of the common developer tools that can help you take your Excel game to the next level.

Once enabled, we’ll show you how to utilize add-ins, form controls, and Visual Basic for Applications (VBA). By the end of this tutorial, you’ll not only have the developer tab readily available but also the skills to harness its tools effectively.

Resources

VBA code to add current date & time to selected cells:

Sub AddDateTime()
    Dim selectedRange As Range
    Set selectedRange = Selection

    If Not selectedRange Is Nothing Then

        For Each cell In selectedRange
            cell.Value = Now
        Next cell

    Else
        MsgBox "Please select a range of cells.", vbExclamation, "Selection Required"
    End If

End Sub

In this Excel training video, we’ll look at some of the logical functions in Excel. We’ll look at IF, IF OR (where OR is nested within an IF function), and AND functions, providing you with a comprehensive understanding of how to craft powerful logical statements in your spreadsheets.

Furthermore, we’ve curated a bonus tip featuring the SWITCH function, adding an extra layer of versatility to your logical toolkit. Uncover how SWITCH can simplify complex decision-making processes, providing a dynamic and efficient alternative to traditional IF statements.

Resources

Icons used in the video:
☑ ☒

IF OR Formula:

=IF(OR(A1 = 0, B1 = 0), "yes", "no")

The above formula checks cells A1 and B1 to see if either of the values are equal to 0. If either A1 or B1 is equal to 0, the formula will output “yes“, otherwise it will output “no“.

In this Excel training video, we’ll be showing you how to use the versatile COUNTIF function. Our focus centers on an essential data analysis task: counting non-empty cells in your spreadsheet. Through step-by-step instructions, we guide you on harnessing the power of COUNTIF to efficiently tally up cells that are not blank, providing you with a foundational skill for data management.

But the learning doesn’t stop there –- we go beyond the basics to showcase additional capabilities of the COUNTIF function by counting cells based on specific keywords or patterns. This comprehensive guide ensures that you leave with a well-rounded understanding of the COUNTIF function, empowering you to navigate Excel with precision and efficiency in your data analysis projects.

Resources

Formula to count non-empty cells:

=COUNTIF(RANGE, "<>")

Change “RANGE” to any range of cells, for example: “A1:B5” or “Table1“

An asterisk * can be used to signify any character. For example, the following formula will look for any cell from A1 to B5 that contains the letter A followed by any other character.

=COUNTIF(A1:B5, "A*")

Question marks ? can be used to signify any sequence of characters. For example, if you had codes in your spreadsheet, such as A10B20C30, you can use question marks to find these patterns by replacing the numbers with question marks: “A??B??C??“

=COUNTIF(A1:B5, "A??B??C??")

Note: If you tried to find a sequence of characters using asterisks instead of question marks, the formula would not work as intended.

In this Excel training video, we’ll be using some Excel functions to demonstrate how to extract substrings from your data. Our focus revolves around four fundamental functions: LEFT, RIGHT, MID, and SEARCH. Through clear and concise explanations, we guide you on how to leverage these functions to precisely retrieve the desired portions of text or characters within your Excel spreadsheets.

But that’s not all — we’ve also prepared a bonus tip featuring the SUBSTITUTE function, adding an extra layer of versatility to your substring extraction toolkit. This valuable insight promises to broaden your understanding of Excel functions and equip you with the expertise needed to navigate and manipulate text data efficiently.

Resources

LEFT Function:

=LEFT(CELL or "text", number)

LEFT Examples:

=LEFT(A1, 2)

=LEFT("mystring", 2) Returns "my"

RIGHT Function:

=RIGHT(CELL or "text", number)

RIGHT Examples:

=RIGHT(A1, 6)

=RIGHT("mystring", 6) Returns "string"

MID Function:

=MID(CELL or "text", start position, end position)

MID Examples:

=MID(A1, 3, 3)

=MID("mystring", 3, 3) Returns "str"