Andrew Lokenauth

Andrew Lokenauth

18-10-2022

21:31

Top 25 Microsoft Excel tips & functions to learn ASAP that will change your life forever & put you in the top 1% 📊25 powerful ways to make you an expert & increase your productivity. Don't ever use Excel again without knowing these 25 tips & functions:

(1) Importing data from websites (2) Sparklines (3) Goal Seek (4) Conditional Formatting (5) Wildcards (6) Transpose (7) Duplicate (8) Remove Duplicates (9) Filter (10) Slicer (11) Pivot Tables (12) Auto-fill (13) DatedIf (14) TRIM (15) Index Match 📊16-25:

(16) XLOOKUP (17) IF (18) SUMIF (19) SUMIFS (20) COUNTIF (21) COUNTIFS (22) UPPER, LOWER, PROPER (24) CONVERT (24) Stock Market data (25) Geography / Maps 📊Let's discuss each in detail (with pictures & examples):

(1) Importing data from websites: With Excel, you can connect to multiple data sources, text files, other Excel files, databases & websites. • Select 'Data' > Get & Transform > From Web • Press CTRL+V to paste the URL into the text box, then select OK This will save hours!

(2) Sparklines: Sparklines allow you to insert mini charts inside any cell, and provides a visual representation of data! Use sparklines to show trends or patterns in data. On the 'Insert tab', click 'Sparklines'

(3) Goal Seek: Get fast answers with Goal Seek. It is also known as What-if-Analysis. Goal Seek basically uses trial & error to back-solve a problem, by plugging in guesses until it arrives at the correct answer.

(4) Conditional Formatting: Conditional formatting helps to visualize data and shows patterns & trends in your data Select 'Home' > Conditional Formatting > Highlighting Cell Rules

(5) Wildcards: Wildcards are special characters that allow you to perform partial matches in your Excel formulas. Excel has three wildcards: • tilde ( ~ ) • asterisk ( * ) • question mark ( ? )

(6) Transpose: Transpose will transform items in rows, to instead be shown in columns (or vice versa) To transpose a column to a row: • Select the data in the column • Select the cell you want the row to start • Right click, choose paste special, select transpose

(7) Duplicate data from the cell above • Ctrl + D fills and overwrites a cell with the contents of the cell above it (8) Remove Duplicates: Remove duplicates in a set of data in Excel • Use the shortcut: Alt + A + M

(9) Filter allows you to filter data. You can filter a column to show a specific product or date. You can also sort in ascending or descending order. (10) Slicer: Slicers provide buttons that you can click to filter tables, or PivotTables Select 'Home', go to Insert > Slicer

(11) Pivot Tables: A powerful tool to calculate, summarize & analyze data, which allows you to compare or find patterns & trends in data. To access this function, go to "Insert" in the Menu bar, and then select "Pivot Table"

(12) Auto-fill: With large data sets, instead of typing a formula multiple times, use auto-fill. There are 3 ways to do this: • Double click mouse on the lower right corner of a 1st cell, or • Highlight a Section and type Ctrl + D, or • Drag the cell down the rows

(13) DatedIf: Calculates the number of (1) days, (2) months, or (3) years between two different dates =DATEDIF(X,Y,"D") X = Start date cell Y = End date cell "D"= Time interval • D = Days • M = Months • Y = Years

(14) TRIM: TRIM helps to remove the extra spaces in data. TRIM can be useful in removing irregular spacing from imported data. =TRIM( )

(15) Index Match: The main difference between VLOOKUP and INDEX MATCH is the column reference VLOOKUP uses a static column reference but INDEX MATCH uses a dynamic column reference Index Match is much more flexible as you can search by row, or by column, or by both

(16) XLOOKUP: XLookup is an upgrade compared to VLOOKUP or Index & Match. Use the XLOOKUP function to find things in a table or range by row. Formula: =XLOOKUP (lookup value, lookup array, return array)

(17) IF: The IF function makes logical comparisons & tells you when certain conditions are met. For example, a logical comparison would be to return the word "Pass" if a score is >70, and if not, it will say "Fail" An example of this formula would be =IF(C5>70,"Pass","Fail")

(18) SUMIF: Sum the values in a range, if they meet a certain criteria (19) SUMIFS: Sum the values in a range that meet multiple criteria Use it if you want the sum of two criteria: Apples & Pete Formula =SUMIFS (sum_range, criteria_range1, criteria1, ...)

(20) COUNTIF: Counts the number of cells that satisfy a query. (Count the number of times a word has been mentioned) (21) COUNTIFS: Counts the number of times a criteria is met For example, it counts the number of times that both (1) apples and (2) price > $10, are mentioned

(22) UPPER, LOWER, PROPER: • =UPPER, Converts text to all uppercase, • =LOWER, Converts text string to lowercase, • =PROPER, Converts text to proper case

(24) CONVERT: This converts one measurement to another. There are multiple conversions that you can do. An example is meters to feet, or Celsius to Fahrenheit.

(24) Stock Market data: You can get stock data in Excel Enter a list of stock ticker symbols. then select the cells and go to the Data tab, then click the Stocks button within the Data Types group Excel will attempt to match each cell value to a company stock, and fill in data

(25) Geography / Maps: Instead of researching geographical data or maps, use Excel With the Geography data type, you can retrieve data like population, time zone, area leaders, gasoline prices, language, and more Type the data you need, then go to Data Tab -> Geography

Microsoft Excel should be taught in all schools! Excel is an underrated skill, which will help your career! If you found this thread🧵helpful: • Follow me: @FluentInFinance •🔁RT the FIRST tweet •❤️LIKE the tweets This account was created to help you learn!



Follow us on Twitter

to be informed of the latest developments and updates!


You can easily use to @tivitikothread bot for create more readable thread!
Donate 💲

You can keep this app free of charge by supporting 😊

for server charges...