(1) Wildcards
(2) Duplicate
(3) Remove Duplicates
(4)Transpose
(5) Filter
(6) Conditional Formatting
(7) Sparklines
(8) Pivot Tables
(9) Auto-fill
(10) TRIM
11-20:
I've used Microsoft Excel for 20 years, and these 20 tips & functions will make you an expert and increase your productivity 100X📊
Don't ever use Excel again without knowing these 20 tips & functions:
(1) Wildcards
(2) Duplicate
(3) Remove Duplicates
(4)Transpose
(5) Filter
(6) Conditional Formatting
(7) Sparklines
(8) Pivot Tables
(9) Auto-fill
(10) TRIM
11-20:
(12) IF
(13) SUMIF
(14) SUMIFS
(15) COUNTIF
(16) COUNTIFS
(17) UPPER, LOWER, PROPER
(18) CONVERT
(19) Stock Market data
(20) Geography / Maps
Let's discuss each in detail (with pictures & examples):
A wildcard is a special character that allow you to perform partial matches on text in your Excel formulas.
Excel has three wildcards: an asterisk "*", question mark "?", and "~"
Duplicate the data from the cell above.
Ctrl + D fills and overwrites a cell with the contents of the cell above it
This will transform items in rows, to instead be shown in columns, or vice versa.
To transpose a column to a row:
1. Select the data in the column,
2. Select the cell you want the row to start,
3. Right click, choose paste special, select transpose
The FILTER function allows you to filter data based on a query.
For example, you can filter a column to show a specific product or date.
You can also sort in ascending or descending order.
The shortcut for this function is CTRL + SHFT + L
Conditional formatting helps to visualize data, and can show patterns and trends in your data
Go to: Home –> Conditional Formatting –> Highlighting Cell Rules
Sparklines allow you to insert mini graphs inside a cell provides a visual representation of data.
Use sparklines to show trends or patterns in data.
On the 'Insert tab', click 'Sparklines'
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"
With large data sets, instead of typing a formula multiple times, use auto-fill.
There are 3 ways to do this:
(1) Double click mouse on the lower right corner of a 1st cell, or
(2) Highlight a Section and type Ctrl + D, or
(3) Drag the cell down the rows
TRIM helps to remove the extra spaces in data.
TRIM can be useful in removing irregular spacing from imported data
=TRIM()
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)
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")
Use this to sum the values in a range, when they meet a certain criteria.
For example, use this if you want to figure out the amount of sales in a given region or by person.
SUMIFS sum the values in a range that meet multiple criteria.
For example, use it if you want the sum of two criteria, for example, Apples from Pete.
The formula is SUMIFS (sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
Use COUNTIF to count the number of cells that satisfy a query.
For example, you can count the number of times a particular word has been listed in a row or column.
CountIf 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.
=UPPER, Converts text to all uppercase,
=LOWER, Converts text string to lowercase,
=PROPER, Converts text to proper case
This converts one measurement to another. There are multiple conversions that you can do.
An example is meters to feet, or Celsius to Fahrenheit.
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
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
- Follow me: @FluentInFinance
-🔁RT the FIRST tweet
-❤️LIKE the tweets
This account was created to help you learn.
More from Screeners
You May Also Like
Oh my Goodness!!!
I might have a panic attack due to excitement!!
Read this thread to the end...I just had an epiphany and my mind is blown. Actually, more than blown. More like OBLITERATED! This is the thing! This is the thing that will blow the entire thing out of the water!
Has this man been concealing his true identity?
Is this man a supposed 'dead' Seal Team Six soldier?
Witness protection to be kept safe until the right moment when all will be revealed?!
Who ELSE is alive that may have faked their death/gone into witness protection?
Were "golden tickets" inside the envelopes??
Are these "golden tickets" going to lead to their ultimate undoing?
Review crumbs on the board re: 'gold'.
#SEALTeam6 Trump re-tweeted this.
I might have a panic attack due to excitement!!
Read this thread to the end...I just had an epiphany and my mind is blown. Actually, more than blown. More like OBLITERATED! This is the thing! This is the thing that will blow the entire thing out of the water!
Tik Tok pic.twitter.com/8X3oMxvncP
— Scotty Mar10 (@Allenma15086871) December 29, 2020
Has this man been concealing his true identity?
Is this man a supposed 'dead' Seal Team Six soldier?
Witness protection to be kept safe until the right moment when all will be revealed?!
Who ELSE is alive that may have faked their death/gone into witness protection?
Were "golden tickets" inside the envelopes??
Are these "golden tickets" going to lead to their ultimate undoing?
Review crumbs on the board re: 'gold'.
#SEALTeam6 Trump re-tweeted this.
I'm going to do two history threads on Ethiopia, one on its ancient history, one on its modern story (1800 to today). 🇪🇹
I'll begin with the ancient history ... and it goes way back. Because modern humans - and before that, the ancestors of humans - almost certainly originated in Ethiopia. 🇪🇹 (sub-thread):
The first likely historical reference to Ethiopia is ancient Egyptian records of trade expeditions to the "Land of Punt" in search of gold, ebony, ivory, incense, and wild animals, starting in c 2500 BC 🇪🇹
Ethiopians themselves believe that the Queen of Sheba, who visited Israel's King Solomon in the Bible (c 950 BC), came from Ethiopia (not Yemen, as others believe). Here she is meeting Solomon in a stain-glassed window in Addis Ababa's Holy Trinity Church. 🇪🇹
References to the Queen of Sheba are everywhere in Ethiopia. The national airline's frequent flier miles are even called "ShebaMiles". 🇪🇹
I'll begin with the ancient history ... and it goes way back. Because modern humans - and before that, the ancestors of humans - almost certainly originated in Ethiopia. 🇪🇹 (sub-thread):
The famous \u201cLucy\u201d, an early ancestor of modern humans (Australopithecus) that lived 3.2 million years ago, and was discovered in 1974 in Ethiopia, displayed in the national museum in Addis Ababa \U0001f1ea\U0001f1f9 pic.twitter.com/N3oWqk1SW2
— Patrick Chovanec (@prchovanec) November 9, 2018
The first likely historical reference to Ethiopia is ancient Egyptian records of trade expeditions to the "Land of Punt" in search of gold, ebony, ivory, incense, and wild animals, starting in c 2500 BC 🇪🇹
Ethiopians themselves believe that the Queen of Sheba, who visited Israel's King Solomon in the Bible (c 950 BC), came from Ethiopia (not Yemen, as others believe). Here she is meeting Solomon in a stain-glassed window in Addis Ababa's Holy Trinity Church. 🇪🇹
References to the Queen of Sheba are everywhere in Ethiopia. The national airline's frequent flier miles are even called "ShebaMiles". 🇪🇹