There are some quirks with Google Sheets. Usually sorting from A to Z or Z to A is a simple process , and when you have a data range to analyze in Sheets there are various additional options you have. BUT watch out for sorting by sheet or sorting by range in google sheets.
In addition, one other sort capability you have is you can also sort by color! But it's not intuitive, since it doesn’t readily show up in the sort menu, but I'll covered how to “enable” it.
Monday, June 26, 2023
Monday, June 19, 2023
Convert One Column Address List Same & Different Row Amounts
When you get a list of addresses, sometimes it’s in a good format to do a mail merge. That’s when you’ve got the name is one column, address in another column, etcetera. If you get an address list with all that information in ONE column, it’s not that hard to copy and paste transpose to another set of cell ranges IF there aren’t too many records (less than five).
When you get a list of records that are higher, say more than 10 records, then it becomes more time consuming. Get to 20+ records, and you might as well forget about taking the afternoon off. BUT if the records follow a consistent and static structure, then you can use some formula tricks to take care of it quite easily. AND if the records don’t follow a structure, but have some sort of pattern, there’s also a way to take care of that and unstack this data.
Monday, June 12, 2023
Dynamic Chart Label Million to Billion with Conditional Formatting
You want dynamic labels for your charts or graphs? For example, when showing sales, if it’s over a million bucks you don’t want the chart label to show 7+ digits. You just want it to show something like 1 M. But if it’s less than 1 million, like 500,000 you want it to show 500K. AND you want it to be dynamic cause your data changes so often.
Think you must do this manually? NO!! There’s a way to do it with a custom format hack. I’ve even through in a bonus tip near the end.
Monday, June 5, 2023
Key Word Search to Bring Back Multiple Results
Do you create dashboards or templates and want to have a mini search bar or search cell...Something like a mini google in your Excel sheet?
Imagine having a cell where you can type some keyword or part of a word and expect it to bring back records that match the keyword. It’s not a full fledge search engine, but it is a neat thing you can do with just a few combinations of the newish dynamic array functions in the latest versions of Excel.
Having a keyword search capability and using it for lookups is just another cool way you can use Excel to help you make spreadsheet data useful. Whether you’re doing it for yourself or have a template to send your users, think of this as a neat little tool that hopefully make your job easier.
Monday, May 29, 2023
Lookup to Find Multiple Matches
If you’ve done lookups with VLOOKUP, INDEX/MATCH or even the new XLOOKUP, it usually just brings back one record or one match per cell. But what if you wanted a lookup to find multiple matches? Think you’d have to write crazy long formulas or VBA code?
Well with a neat dynamic array function in M365, it’s just a simple combination with other functions that will take you less than a couple minutes to put together...maybe even less that one minute, once you understand how it’s done.
Monday, May 22, 2023
Searchable Dropdown List - 2 Examples
Do you create dashboards or template files for others. Want to add some pizzazz and interactivity? Why not create a searchable dropdown list?
You can then use this feature to perform lookups or change the appearance of data in your charts or graphs. You can make it look “cool” for your user and it’s not that hard to do.
If you’re putting together a file that has gives users the ability to interact, why not let them also search for data that brings back results?
Monday, May 15, 2023
Data Dropdown Validation List without Duplicates
This video is all about how to create a dropdown list that removes the value you selected previously so it won’t show up on the next set of dropdowns!
This is useful if your planning seating table arrangements or assigning roles to people for an event. This is an essential video for anyone has to project manage these types of activities!
Something similar if you don't have the latest M365 Excel with Dynamic Array Functions https://www.youtube.com/watch?v=UIsKXur3CeE
Subscribe to:
Posts (Atom)