Millions use Excel to handle all kinds of office and productivity tasks, so here are some of the top 10 shortcuts, tips, and hacks that will help you on your way to becoming an Excel pro.
https://www.techspot.com/article/2539-microsoft-excel-hacks/
Top 10 Hacks for Microsoft Excel
- Thread starter neeyik
- Start date
Neatfeatguy
Posts: 1,615 +3,045
I enjoy using Text to Columns in Excel. I used to make use of it a lot at my last job. I haven't had to use it for anything recently, but it works great if you have a ton of data that you copy into a single column.
My last job I was tasked with (I actually took it upon myself to do this since no one else knew how nor wanted to learn how) rebuilding Access databases and sometimes when a database was so broken, I had to be able to parse the info out in Excel to separate the data in necessary columns so I could find and remove bad data.
Also, VLOOKUP is a good tool in Excel if you need to pull data from tables.
My last job I was tasked with (I actually took it upon myself to do this since no one else knew how nor wanted to learn how) rebuilding Access databases and sometimes when a database was so broken, I had to be able to parse the info out in Excel to separate the data in necessary columns so I could find and remove bad data.
Also, VLOOKUP is a good tool in Excel if you need to pull data from tables.
Good ol' text-to-columns (Alt->A->E)
As far as VLookup goes - it's definitely a formula to be familiar with as it is so common. Although I personally used Index-Match over Vlookup (a little more versatile, but also a little more complicated) until Xlookup came along. Knowing all 3 is definitely the way to go if possible.
The only complaint I have against XLookup is that M$ locked it behind newer versions of Excel.
WhoCaresAnymore
Posts: 772 +1,221
My most helpful tools are Sumifs and Unique
If you have a column with a lot of duplicate products, but want to create a list that only has 1 of each. Unique(Column:Column). If you get a Spill error, the list is running into something below it. Make sure the cells are clear.
Sumifs allows to sum based on filters. It's a godsend with my OBDC tables I pull down with over 30k lines.
Nice article, I bookmarked it for a few things I liked.
If you have a column with a lot of duplicate products, but want to create a list that only has 1 of each. Unique(Column:Column). If you get a Spill error, the list is running into something below it. Make sure the cells are clear.
Sumifs allows to sum based on filters. It's a godsend with my OBDC tables I pull down with over 30k lines.
Nice article, I bookmarked it for a few things I liked.
Soaptrail
Posts: 46 +55
VLOOKUP's are so 2019. You my friend need to switch to XLOOKUP which is way better. First of all no more counting columns, second you can return multiple rows of data and finally it includes a built in error message when a value is not found. Enjoy because vlookpus should no longer be needed.
Ok. COUNTIF to Find Duplicates
The duplicate-checking formula uses =COUNTIF to “count” which cells contain data that appears more than once throughout the spreadsheet. Resulting values can either be “TRUE” (indicating duplicate data) or “FALSE” (showing non-duplicate data).
You may wish to begin by adding a heading like “Count” (or something similar) to a blank column, though this is optional. Then, do the following:
Copy and paste this formula into the first cell of a blank column: =COUNTIF(A:A,A1)>1
Change the A in the formula to coincide with the letter of the cell column you wish to find duplicates
Drag the cell’s contents down to the cells below
The duplicate-checking formula uses =COUNTIF to “count” which cells contain data that appears more than once throughout the spreadsheet. Resulting values can either be “TRUE” (indicating duplicate data) or “FALSE” (showing non-duplicate data).
You may wish to begin by adding a heading like “Count” (or something similar) to a blank column, though this is optional. Then, do the following:
Copy and paste this formula into the first cell of a blank column: =COUNTIF(A:A,A1)>1
Change the A in the formula to coincide with the letter of the cell column you wish to find duplicates
Drag the cell’s contents down to the cells below
Similar threads
