![Sheet Skunk](/img/default-banner.jpg)
- Видео 92
- Просмотров 86 075
Sheet Skunk
США
Добавлен 2 май 2023
Getting ahead in business can be tough. But it doesn't have to be!
🎓📚Professionally, I'm a data analyst with a bachelors in computer and network security and a masters in analytics.
I’m used to being handed complex tasks or strange requests that initially seem as simple as moving mountains. 🏔️😓
Personally, I love that kind of challenge.
This is why I want to share what I've learned and show you how to make your office life a productive one without the stress. In a world where we rely more and more on fresh data and our capability to manage it, no one should have to feel overwhelmed or ill-equipped to get the job done.
And while I mainly focus on mastering Excel here, I'll make sure to cover all the tools you use throughout the day in a way that is easy to understand for any skill level.
So let's maximize your potential by learning all the keyboard shortcuts, spreadsheet formulas, and customizations you need to level up your game!
🎓📚Professionally, I'm a data analyst with a bachelors in computer and network security and a masters in analytics.
I’m used to being handed complex tasks or strange requests that initially seem as simple as moving mountains. 🏔️😓
Personally, I love that kind of challenge.
This is why I want to share what I've learned and show you how to make your office life a productive one without the stress. In a world where we rely more and more on fresh data and our capability to manage it, no one should have to feel overwhelmed or ill-equipped to get the job done.
And while I mainly focus on mastering Excel here, I'll make sure to cover all the tools you use throughout the day in a way that is easy to understand for any skill level.
So let's maximize your potential by learning all the keyboard shortcuts, spreadsheet formulas, and customizations you need to level up your game!
Excel Interview Prep: Topics to Know (with Examples)
Interviews can be scary, right? You never know what is going to be asked or how knowledgeable the interviewer is when it comes to Excel. And let's face it! Knowing how to use Excel is more or less a given in the business world.
So, here's a suggestion...instead of memorizing a hundred different questions and answers, spend that time making yourself more comfortable with the easy, medium, and hard skills most jobs are looking for. If you're familiar with the key concepts, there's very little you'll be stumped by in a job interview.
While every company and position is different, based on my experience, the skills I cover in this video should help you prepare for that big bad interview!
00:00 I...
So, here's a suggestion...instead of memorizing a hundred different questions and answers, spend that time making yourself more comfortable with the easy, medium, and hard skills most jobs are looking for. If you're familiar with the key concepts, there's very little you'll be stumped by in a job interview.
While every company and position is different, based on my experience, the skills I cover in this video should help you prepare for that big bad interview!
00:00 I...
Просмотров: 56
Видео
New REGEX Functions in Excel
Просмотров 2172 месяца назад
Using regular expression is an excellent way to find a needle in a haystack, especially when working in Excel. I've been playing around with Microsoft Excel's 3 new regex functions and I've put together a few short examples to show you just how easy they are to use. For those looking for some extra detail behind the exact regular expression syntax, feel free to check out this link: www.rexegg.c...
Show Dates in Excel Pivot Table
Просмотров 1902 месяца назад
Displaying your Pivot Table dates in the format you want can be a bit tricky for many Excel users. But no more! In less than 3 minutes, I'll show you three different ways to modify your Pivot Table output to show your date data just the way you want it 😊👍 Looking for more Excel help? ➡️ sheetskunk.com Let's connect on social! 👨💻📲 TikTok ➡️ www.tiktok.com/@sheetskunk Instagram ➡️ ...
How to Import an Excel (CSV) File into MySQL
Просмотров 1074 месяца назад
As a data analyst, SQL is my go-to exploration tool. And if you're familiar with how the corporate world works, many departments are resistant to move away from Excel. But that doesn't mean you can't broaden your skillset. The first step before navigating the syntax, formats, and advanced features, is to import a file into a database. So, take the next 10 minutes and I'll show you just how it's...
Replace your IF Statement with SWITCH
Просмотров 4,7 тыс.5 месяцев назад
🚀 Unlock the power of Excel's SWITCH function and streamline your workflow! Sure, you could use a crazy looking nested IF formula or the IFS statement. But why bother when you have SWITCH? 💡Why SWITCH? The SWITCH function simplifies your formulas by replacing a crowded formula bar when alternating between outputs with conditional logic. 00:00 Why SWITCH? 00:40 Example 1 03:10 Example 2 04:57 Ti...
Link Google Drive files to Google Sheets
Просмотров 2715 месяцев назад
Inserting a link within your Google Sheet to another document is pretty straightforward. But with the additional option of Smart Chips, you a get a dynamic preview and access to everything found in your Google Drive. And the best part, it only takes a click of a button to set it up! Looking for more Excel help? ➡️ sheetskunk.com Let's connect on social! 👨💻📲 TikTok ➡️ www.tiktok.com/@sheetskunk...
Automate an Attendance Sheet in Excel - With Formulas!
Просмотров 1345 месяцев назад
It takes less than 10 minutes to create a dynamic attendance sheet in Excel. You can use the steps in this video to track work attendance, school, or even create a habit tracker! The formulas used to automate the process are a bit lengthy, but I'll explain each part of the process so you can create your own attendance sheet. ✅ 00:00 Intro 00:39 The setup 01:30 Formula to automate days of the mo...
Python in Excel Tutorial
Просмотров 3256 месяцев назад
Python in Excel has been available for several months now. If you've used Python in the past, this should come as exciting news! Most companies rely on Excel for most of their day to day data analysis. With the integration of Python in Microsoft 365, your data analysis can take on a whole new form. Let me show you how to set up your Python script, create a DataFrame, run some aggregations, and ...
SUBTOTAL in Excel - Upgrade Your Calculations
Просмотров 2 тыс.6 месяцев назад
I'm not saying there's anything wrong with the SUM function in Excel. But you can do so much more with SUBTOTAL. Not just accounting for filtered and hidden rows, but you can also calculate averages, counts, and more! Let's spend the next 7 minutes exploring the power of SUBTOTAL with various examples and incorporating the conditional functionality applied by using the IF function with it. 00:0...
Make an Excel Chart Go from Lower to Highest Values
Просмотров 5787 месяцев назад
Tired of trying to figure out how to order your Excel chart values in lowest to highest values? Stop wasting your time! In this short video, I've got all the steps you need to make it an easy and hassle-free process. Here we go! 00:00 Here's the problem 00:28 Create our chart and use column sort 01:22 Using the Chart Format Axis options 02:22 Setting the axis bounds 02:59 You got this! Looking ...
Goal Seek in Excel - Easy Examples to Learn
Просмотров 1137 месяцев назад
Goal Seek is a wonderful tool in Excel's What If Analysis options. If you're struggling to with finding the exact values to come to a desired calculation, Goal Seek is the answer. It's perfect for professionals, students, and the suddenly curious answer seekers. So, dive in and I'll explain the power of Goal Seek in the simplest way possible! 00:00 How Goal Seek in Excel can help you 00:38 Goal...
Are Pivot Tables Obsolete? - NEW GROUPBY and PIVOTBY Functions in Excel
Просмотров 1,1 тыс.7 месяцев назад
If you're a beta member Insider for Microsoft 365 and haven't tried out the new GROUPBY and PIVOTBY functions, what are you waiting for? These new features add a new alternative to the traditional Pivot Table experience where your data summary will update automatically with any changes to your values. You can customize the output to match a regular Pivot Table, but now it's all created within a...
SUMIF and SUMIFS Formula in Excel: Conditional Sum
Просмотров 2347 месяцев назад
Sometimes autosum in Excel just isn't enough to get the result you need. Filtering down on specific values creates a problem for adding your values and may confuse those viewing your spreadsheet. With SUMIF and SUMIFS, you can easily total your values based on the criteria that's important to you. Especially when working with drop-down lists, SUMIFS can make the math automatic and a confident a...
Excel Filter Function Across Multiple Sheets
Просмотров 7 тыс.7 месяцев назад
FILTER in Excel has sooo many uses, sometimes it feels endless. And one such use, is to filter on data spread across multiple sheets. With the help of the VSTACK function, you can combine the columns from your entire worksheet and make a multi-functional search tool for your data. This is great for rows of historical statistics related to purchasing, member services, and more. Just remember, wh...
The REPT Function in Excel Has More to Offer!
Просмотров 2,6 тыс.8 месяцев назад
It's a simple formula that many Excel users don't bother with anymore. But the REPT function has a lot more use than you may think. There are tons of ways to get the result you need, and REPT might just be the perfect fit! In this video, I have four different use cases that will show you how you can use REPT that you probably haven't thought of. 🤔📝 00:00 REPT has more to offer 00:27 Example 1 -...
Excel's Magic Search Box You Need Today!
Просмотров 1,7 тыс.8 месяцев назад
Excel's Magic Search Box You Need Today!
Boost Your Productivity with 75 Excel Keyboard Shortcuts
Просмотров 4849 месяцев назад
Boost Your Productivity with 75 Excel Keyboard Shortcuts
Excel's NEW Checkbox Feature is Perfect! See Why Here
Просмотров 7319 месяцев назад
Excel's NEW Checkbox Feature is Perfect! See Why Here
Make an Awesome Excel Dashboard in Under 15 Minutes
Просмотров 5149 месяцев назад
Make an Awesome Excel Dashboard in Under 15 Minutes
Extract Text Faster with TEXTBEFORE and TEXTAFTER in Excel
Просмотров 73610 месяцев назад
Extract Text Faster with TEXTBEFORE and TEXTAFTER in Excel
The XLOOKUP Function in Excel Easily Explained!
Просмотров 90610 месяцев назад
The XLOOKUP Function in Excel Easily Explained!
Solve Excel Formula Errors with Trace Precedents and Trace Dependents
Просмотров 42110 месяцев назад
Solve Excel Formula Errors with Trace Precedents and Trace Dependents
Excel Waiting for another OLE Action: Try These First!
Просмотров 3,4 тыс.10 месяцев назад
Excel Waiting for another OLE Action: Try These First!
How to Use Ruler in Microsoft Word (with Examples!)
Просмотров 1 тыс.11 месяцев назад
How to Use Ruler in Microsoft Word (with Examples!)
Highlight the Active Row and Column in a Data Range in Excel (with Visual Appeal!)
Просмотров 47711 месяцев назад
Highlight the Active Row and Column in a Data Range in Excel (with Visual Appeal!)
How to Convert PDF to Word FREE - Edit Native and Scanned PDF Documents
Просмотров 6911 месяцев назад
How to Convert PDF to Word FREE - Edit Native and Scanned PDF Documents
Google Finance Function Explained! Track Investments for Free
Просмотров 1911 месяцев назад
Google Finance Function Explained! Track Investments for Free
How to Screen Record on Mac for FREE
Просмотров 2011 месяцев назад
How to Screen Record on Mac for FREE
Thank you for this nice video
👍
I found that with a larger data set, the MySQL CSV import wizard was brutally slow, especially when trying to change the data type from text to datetime. The solution I found was to use notepad++ with a plugin called CSV Lint. It converted the csv file to an SQL insert query, and dropping that into MySQL was so much faster. Great tip on the date formatting in Excel - thank you!
I feel your pain! I've imported some pretty large datasets into MySQL and it's unbearable sometimes 😒 Nice trick though, I'll have to try that plugin
@@sheetskunk I can't take credit for it. I found it on a forum, maybe stack overflow, while searching for a solution. But it was easy to do even for a relative SQL beginner like me.
@jasongins either way, it sounds pretty useful 👍
On my Excel 2013 app, everything freezes with OLE message, even the "file" button freezes too. I practically can't do anything on it, not to talk using the features on it to address the issue. Anyway out?
Yikes, that’s frustrating. Have you tried any of the options I listed? Safe mode, DDE option? Or does it freeze before you can even attempt?
Hi dad! You did amazing! Keep up the good work
🎉
Hi! I have an extensive timesheet which at the end of the month I have to save as for the next month (I can’t make a template as each month I add or remove new employees with several employment data that needs transferring to the next month). I colour the cells of the data I transferred to the payroll software. When I save it as for the next month , is there a simple way to “uncolour” the cells? I can’t select all and do a no fill as there are columns that need the fill. I hope I’m making sense! Awesome, clear content!
Hmm, I think I understand what you're asking. I'd probably have to look at it to really give you better advice, but you could apply some conditional formatting. There's a lot of ways you could do it. For instance, you could include a column which references the status of a transfer (with checkboxes or True/False). If you transferred an employee from the old sheet to the new sheet, you can check off the box in the cell for that row. Then set up conditional formatting rules for the appropriate fields to automatically remove colors when the box is unchecked, representing new employee data. Does that make sense?
❤
Isnt Choose easier for this eg: CHOOSE(LEN(B2),"Pass","Eh…","Not bad","Get your ticket!","Must See") or CHOOSE(d2,0.2,0.15,0.10,0.07) - this is a bit shorter as you dont have to repeat the value. The only thing is you dont have an "anything else" result, but to be honest it might be better to error that anyway, or you could wrap the choose in IFERROR
Easier? Maybe. It depends on the situation. In my example, CHOOSE would work well and be less lengthy. SWITCH is just another option for when CHOOSE can't do the same alone. You're right, that you could wrap CHOOSE in an IFERROR as a catch-all. But CHOOSE also relies on an index number to determine the return. So if I didn't end up using a five-star rating, or the employee tier, and instead used some kind of text alternative, then you'd have to include something like a MATCH function for the returns. At that point, I'd probably prefer to go with SWITCH. But great point!
Thank you for this bcje video 📹 👍
Will the values accept an array? IE, if you have an expression to scrutinize, can you then put the test values into an array and call the array?
In a way, yes. But it is very limited! The values/result in your array would have to match exactly to the expression to return the results in a single column. However, you can get around it with other functions, but Switch isn't necessarily built for it. In most cases where I've used it, Switch only correctly evaluates the first element of the array. I could be wrong, but I can't think of a way to make it work without using some combination of alternative functions.
Thanks! That was really simple!
No problem!
1:20
நன்றி..
Nice video and good explanation !
Thank you!
Groupby and pivotby aren't great because they would replace pivot tables, but because they can be used inside complex nested formulas
Great point! It's a nice added feature, but shouldn't be seen as a replacement to pivot tables. An example of its true benefit is as you suggested.
I don't want to use all sheets but using the control button is not working
Hmm, that's strange. Well, instead of shift or control, you can select the range from a single tab, then enter a comma. After the comma, select the range from another tab. For more tabs, just repeat the process.
"Promo SM" 😇
Is it possible to subtotal index numbers make it dynamic
Absolutely! If you're referring to the function selection, one way to do it would be to set up a drop-down list. So, if your drop-down selection is "Average", the corresponding cell would produce the number 1. When you create your SUBTOTAL formula, instead of inputting a static number, simply reference the cell that is dependent on the drop-down. If you meant making the actual range dynamic, you can do that as well. However, it depends on the situation. You could set up a table if you're just talking about including new rows to the data. Or if it's separate column indexes, you could do something similar to the example of using a drop-down. But instead of selecting a different function, you could select a different table column to subtotal. It all depends on what you're trying to do. But it is possible.
Nice. All these years and I wasn’t aware of this. Granted I use pivot tables 99% of the time.
Nothing wrong with that! It’s nice having options 😀
Hello and thanks for this trick. A question though, is it possible to have the search function over 3 columns not just one.? If i expand the search to another column I get a value error. At the moment my work around is to a sheet for each type of search. Not very ideal. i also want to incorporate that results start populating as I type but I use excel rarely so its slow going haha.
Hi! Yes, you can do this. I have another video that somewhat breaks this down with one sheet, but you can adjust the formula to include multiple. The video also tells you how to make it filter down and change the results as you type. Here's the video 👉 ruclips.net/video/89QAQG9kR3M/видео.htmlsi=mJa7u6U8LeGJAl7R But as a quick example, to filter on multiple sets of data, your formula would look something like this: =FILTER(VSTACK(Table1,Table2),ISNUMBER(SEARCH(Sheet2!$B$1,CONCATENATE(INDEX(VSTACK(Table1,Table2),0,1),INDEX(VSTACK(Table1,Table2),0,2),INDEX(VSTACK(Table1,Table2),0,3))))) Although in this formula, I'm only combining 2 sheets (or tables), and specifying the first 3 columns to search on. And cell B1 is where I would enter the text to search on. Hope that helps!
@@sheetskunk That formula did the trick. Now searching across 3 pages. Thank you so much.
That’s great! Glad it worked out 💪
Wow... great stuff 👌
A well rounded and rather enjoyable video tutorial. I've learned a lot today. Keep up the good work and continue to produce top quality videos like this one!
I appreciate it! Thank you 😊
Definitely place data in tables first, and reference the tables instead of static ranges. Even better - use PQ.
Preach. I tell people that if you're not using tables to manage your data, you're doing it wrong. Structured references are so much better to use in formulas.
Please help How to multiply in excel 5*7 within one cell
Hi, I'm not sure if I'm understanding your question. If it's a simple problem of multiplying 5 and 7 in a single cell, you would just type =5*7 and your output would show the number 35. Does this answer your question?
Nice
🤤 P r o m o s m
Thank u Chris for this nice video 📹 👍
Hi dad
Thank you for this nice video 📹 👍
Thank you for this insightful video 📹
THANKS
I'm an 'insider' and do not see this feature yet. Checked Customized Ribbon Bar and nothing there. A quick search revealed no details on this feature - just the Forms Controls and legacy ActiveX Controls.
OK, inexplicably, my insider subscription was not active on my primary computer - enablement resolved this...thanks!
Seems like an over complicated method of writing in cell F12 the formula =F11+D12 ?
Yeah the scan function is better served in other situations. I just chose a simple addition example to show how the scan/lambda functions are formatted and operate.
Thank u for this nice video 📹 👍
Thanks!
Nice tricks thank you for this nice video 📹 👍
Very nice 📹
Great! Thanks!
Glad I could help!
You are an amazing instructor. I can't wait to get this feature.
Thank you so much! 😊. It really is a feature I’ve been begging for and so glad it’s finally here.
Awesome function ... I just used it in all of my sheets that have running totals. Thank you so much for sharing.
I’m so glad you like it! 👍
Hi, would you pls zoom your worksheet because it's unable to read anything clearly.
I'm sorry about that! I've been trying to be more conscious of how well everyone can see what I'm doing through each video. Unfortunately, RUclips Editor doesn't give me the option to zoom in after it's been published. But if you need any clarification on any of the steps, just ask! ** I've considered posting a video update with the worksheet steps zoomed in. So if I do that, I'll be sure to update the description to include the link.
A better way to use CONCAT: =MAP(A2:A6,B2:B6,LAMBDA(a,b,CONCAT(a,"@",b,".com"))) 🤗
Matrix formulas do not require dragging, as they spill: Movie: =TEXTBEFORE(A2:A9,{":";" -"}) Release Year: =TEXTAFTER(TEXTBEFORE(A2:A9," -",-1),":",-1,,,"") Producer: =TEXTAFTER(A2:A9," -",-1) 🤗
Good catch! That would certainly make it faster 👍
great trick!
Letters are too small to see
Sorry about that. I’ll keep that in mind for future videos. Thanks for letting me know!
How do you get that detailed explanation window when you enter a formula?
I’m pretty sure it’s a specific feature to Excel on the web. I’ve tried activating it on desktop but it doesn’t look the same
I'm using Microsoft Office 2021. I don't have LAMBDA in my office?
Hi! Unfortunately, stand alone licenses of Office that didn’t include LAMBDA at the time of purchase, don’t get updated with new functions/tools. That’s why MS pushes the 365 subscription so any new updates are added. But you can use Excel on the web for free. Otherwise, you can use a different set of functions depending on what you’re trying to do.
Very well explained, thank you, I’ll see if I can find a use case.
It looks like List.Accumulate function in PQ
Yes it does! Pretty much the same purpose.
Thank you, SS. Great to see talented MCSFT/Excel magicians new to this platform. Looking forward to seeing your channel grow. Q - what is the pop up box with the formula description/example/etc called? The one that shows up when you type in the formula. How do we turn on that feature? It looks like a quick summary of the formula descriptions on the web. Having that summary pop up automatically like this would be such a convenient feature.
Thank you so much! I'm so happy you're enjoying the channel! As for your question, I may be wrong but I think it's specific to the Excel Online version. You can activate it in regular desktop versions of Excel but it won't float and automatically drop-down. You'd have to click the name of the function when it appears below and a separate window will open with the same information. If your version doesn't show it already, go under the Advanced Excel options and under the display section, make sure the box for "Show Function ScreenTips" is checked. Hope that helps!!
Thanks so much!
Happy to help!