TLDR;
This video presents 20 tips and tricks for Microsoft Excel, covering a range of functionalities from data input and analysis to collaboration and automation. It includes converting images to tables, using Excel for free online, leveraging AI-driven insights, and mastering functions like XLOOKUP, SUMIF, and IF. The tutorial also covers data types, macros, pivot tables, data import from the web, and collaboration tools.
- Convert images to tables
- Use Excel for free on the web
- Use Ideas for data insights
- Master XLOOKUP, SUMIF, and IF functions
- Collaborate with others
Introduction [0:00]
Kevin introduces a tutorial on 20 tips and tricks for Microsoft Excel, inviting viewers to use the provided timestamps to navigate to specific sections of interest. The video promises to cover a range of Excel functionalities to improve user efficiency and data handling.
Convert image to table in Excel using phone [0:17]
Users can convert image data into Excel tables using the Microsoft Office app on iOS or Android. By selecting "Actions" then "Image to Table" within the app, users can take a photo of a table, confirm the selection, and open the digitized data directly in Excel. This allows for easy saving and desktop access.
Use Excel for free on the web [1:29]
Excel is accessible for free on the web by visiting office.com and logging in with a Microsoft account, which can also be created for free. The web version includes commonly used controls and allows users to create new workbooks from scratch or use templates.
Use Ideas to generate insights on your data [2:08]
The "Ideas" feature in Excel helps generate insights on data. Located on the Home tab, the Ideas pane analyzes data and suggests views. Users can also type questions about their data, using column headers as references, to receive specific answers and insights.
Define names for cells [3:19]
Defining names for cells instead of using letter-number combinations makes formulas easier to understand. By highlighting cells and using the "Define Name" option under the Formulas tab, users can assign names to columns. Formulas using these names (e.g., "revenue - cost") are more readable and maintain clarity.
Absolute & relative cell references [4:42]
Relative cell references adjust formulas as they are moved, while absolute references remain fixed. The F4 key toggles between relative (e.g., L1) and absolute ($L$1) references. Pressing F4 multiple times cycles through options where only the row ($L1) or column (L$1) is locked, providing flexibility in formula application.
Macros [6:35]
Macros automate repetitive tasks. To record a macro, go to View, then Macros, and select "Record Macro," ensuring "Use Relative Reference" is checked. After naming the macro and assigning a shortcut, perform the desired steps, then stop recording. The macro can then be run to repeat those steps automatically.
Data types [8:35]
Excel's data types, such as stocks and geography, allow users to pull in related data. By selecting a list of countries and converting them to the "Geography" data type, users can click the plus icon to add information like population, largest city, and unemployment rate directly into their sheet.
Xlookup function [9:38]
XLOOKUP is a function that looks up data, replacing VLOOKUP and HLOOKUP. The function requires a lookup value, a lookup array (where to find the value), and a return array (what to return). It can also specify what to return if no match is found and the match mode (e.g., exact match).
Sum function [11:47]
The SUM function totals values. Users can enter "=SUM(" and select the range, or use the shortcut Alt + = to automatically sum values above. Highlighting values also displays the sum in the status bar. Inserting a table and adding a total row provides another way to sum values using a dropdown list of functions.
Concatenate and flash fill [12:43]
Concatenating combines values from different cells. The CONCAT function can be used, or, more simply, users can type the desired combination in one cell and use Flash Fill. Flash Fill detects the pattern and applies it to other cells automatically, merging values without needing a formula.
Sumif function [13:48]
SUMIF sums values based on a criterion. The function requires a range to check against the criteria, the criteria itself, and the range to sum. For example, it can sum profit only for the United States by checking the country column and summing the corresponding profit values.
Countif function [14:45]
COUNTIF counts cells that meet a criterion. The function needs a range to evaluate and the criterion to match. For instance, it can count how many countries sell fortune cookies by looking across the list of cookies sold in each market.
IF function [15:35]
The IF function runs a logical test on data. It requires a logical test, a value if true, and a value if false. For example, if a favorability rating is below 25%, the function can return "investigate," otherwise "all good."
Calculate difference between dates & convert function [16:48]
Excel can calculate the difference between dates. Using "=TODAY()" provides the current date. Subtracting an earlier date from today's date calculates the age in days. The CONVERT function can then convert this value from days to years or other units.
Adjust column width & row height automatically [18:11]
To automatically adjust column widths and row heights, click the corner to select the entire sheet, then click between two columns or rows. This resizes all columns or rows to fit their content.
Freeze panes [18:56]
Freezing panes keeps headers visible while scrolling. Under the View tab, the "Freeze Panes" option can freeze the top row or first column. The split function adds a bar to freeze panes in a specific location.
Pivot Tables [19:33]
Pivot tables summarize and analyze data. Select data and insert a pivot table. Drag fields into rows, columns, and values to explore data. Pivot charts visualize data from the table, updating dynamically with changes.
Drop down lists [21:14]
Dropdown lists reduce data entry errors. Under the Data tab, use "Data Validation" to allow values from a list. Define the list source, and users can select from the dropdown in the cells.
Import data from web [22:16]
Data can be imported from the web using the "From Web" option under the Data tab. Paste the URL, and Excel fetches data. The Power Query editor cleans up data before importing. Set a refresh frequency to keep data updated automatically.
Collaborate with others & comment [23:54]
Excel files saved in OneDrive or SharePoint can be shared for collaboration. The share control defines editing or viewing permissions, sets expiration dates, and allows sharing via email or link. Comments facilitate real-time collaboration.
Wrap up [24:45]
Kevin concludes the tutorial, inviting viewers to share their favorite tips and suggest future topics. He encourages viewers to like the video, subscribe for more content, and leave comments for future video ideas.
