Do you track time sheets for people? Want a quick way to track and add up hours worked? Also want to be able to share or collaborate on the sheet?
A good solution is to use Google sheets. The calculations are easy to do, and the sheet is all in the “cloud” and easily shareable.
I’ve cover two ways you can track time spent either in decimals so you can multiply by some rate like 10 bucks and hour. Or you can get the time spent in duration like 8 hours 10 minutes.
Monday, July 24, 2023
Monday, July 17, 2023
Highlight Table Cell based on Dropdown Selection
Sharing data table with someone and wanting to quickly highlight a cell based on some dropdowns? This could be a situation where you’ve got a dashboard or report and you need users to find the main data point after they select something.
This video will show you how simple it is to create a dropdown. Not one but two! And once the choice is made, it’ll quickly highlight that part of a table to show the important information.
Monday, July 10, 2023
Filtering and Slicers - Google Sheets
Filtering column data in sheets in another way to help you narrow done on the important stuff of your data. Also something a bit newer called Slicers, and I’ll cover that closer to the end of the video. But did you know you could filter for value, conditions or colors in sheets? There’s all sorts of ways you can tease out the important stuff in your data with filters and you can even hide your filtering when you are collaborating with others in google sheets.
Monday, July 3, 2023
Extract First, Last & Middle Name Excel Power Query
You’ve got names in a column cell and you want to separate first, middle and last names. Easy to do if ALL the values were the simple first…middle… and last name. But what if some didn’t have middle names? What if some had two or three middle names?
Easy if it was like 5 to 10 names. 20 names…not that easy...2000 names? Much harder to do. But if you’ve got power query, it’ll only take under 5 minutes to do. Plus once you set it up, you can do it over and over again.
Monday, June 26, 2023
Sort by Column - Google Sheets
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 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.
Subscribe to:
Posts (Atom)