This blog post is sharing of YouTube content that I participated in creating, but does not live on my YouTube channel. This is to create an easy way for LifeHackers to find my content and support the events I participate in, at the same time! Click here for full listing!
| Event Name | Date |
| Excel Life Hacks – 2023 | March 2023 |
Transcript of Content (Disclaimer – Auto Generated):
Introduction
0:07 welcome to another essay Global webinar today talking about all cool things
0:16 Excel Excel life hacks if you know Excel don’t worry I feel pretty sure you’re
0:21 going to learn a thing or two today over the next 45 minutes or so we’ve got a lot to touch on and a lot to show so
0:29 maybe just take a minute and sit back and relax and enjoy some of these tips hopefully they will save you some time
0:35 if you’re not familiar with me my name is Sean dorward I’m a Microsoft MVP for
0:41 business applications I am the a vice president here at SC Global for service
0:47 and delivery where we at sa global Service and support all things Dynamics uh this session here hopefully if you’re
0:55 not using Dynamics you’re at least using Excel but ideally pretty much all of the clients that we work with pretty much
1:02 anyone in an office home or remote are using Excel so this session is designed
1:08 to help save time maybe to give you some tips that you can impress your boss with
1:14 or maybe just to stop some of the craziness that you feel when you drag and drop things in Excel don’t worry
1:21 we’re going to cover a lot here today but if you stick around to the end there’s
1:26 going to be a QR code that you can snap with your camera on your phone uh and and fill out a form to get the actual
1:33 Excel file this Excel file which has all of our tips in it that
1:38 we’re going to cover and as well you’ll get this recording and naturally the
1:44 slide deck that we’re using so hopefully while you’re consuming the information get your wheels turning when you receive
1:51 the Excel file from from myself and from our team here at sa Global the recording
1:56 Etc you’ll be well armed to implement these things and if you have trouble you
2:01 can reach me here’s my email address Sean dsaglobal.com you can find my
2:07 writings my musings my my community contributions at lifehacks365.com if you
2:13 are not familiar with some of the things that I do around the community I am pretty heavily involved in the community for Dynamics specifically you can learn
2:21 about power bi Dynamics GP Dynamics CE uh business Central you name
2:28 it uh I’ll be at the in-person Dynamics con live event coming up in May I’ll
2:35 also be at the summit event in Orlando excuse me in Charlotte this October
2:43 um I’ll be at directions which is a partner event so if anybody here is a partner uh
2:51 please stop by and say hello there as well so without further Ado that’s about three minutes in uh we’ve got so many
2:58 people on the line here we might run right up to the end but here’s my email it’ll be at the last slide as well for
3:05 you to go ahead and shoot me an email if you need any help or have any questions so I’m going to camera off throughout
3:10 the most the most of the the show and tell and Excel here and I’ll be back on toward the end now
3:17 while you’re going through this remember stay to the end so you can get the Excel file with this exact Excel file that I’m
3:24 using um and we will jump between this PowerPoint and Excel so I’m not just going to talk about things I’m actually
3:31 going to show it so give me some Grace if I bumble through some of them I’ll try to get through it all with that
3:37 being said gonna turn the camera off and let’s get
3:42 to it uh before I forget here’s a I’ll put these links in the chat for you
3:48 um for these events we have some discounts if you’d like to take advantage of that so we’re going to get started here and we’re going to talk
3:54 about our very first one which is quick links now if you’re familiar with Excel
3:59 don’t be discouraged if you see a tip in here that you know and feel like it’s not maybe not for you stick around we’re
4:05 going to kind of ramp up and build up this session as we go along it’s really important to use Quick links in Excel
4:12 like all the Microsoft products there’s so many different functions and features that are nested into all of these
4:19 ribbons in the product but things that you commonly use or most frequently used
4:25 although Microsoft puts some at the home here you can add to your quick links or
4:30 I guess it’s called quick access right here easily we’ve got some added here these are some of the things we’re going
4:36 to use but to add you’re just going to do the drop down and do more commands find the commands
4:42 in here I always just like to switch this to all commands let the entire list of all commands populate here in this
4:50 list and then find exactly what you want to put on quick links so for example we’re going to put on quick links
4:57 something called camera and I’m just going to use the keyboard drop down to the C’s there’s camera I could click on
5:03 it and hit add I already have it in here but if I didn’t it would just add it in
5:08 there and make that available and I no longer have to go try to find it so real easy that’s quick links I’ve got a few
5:15 things up here in addition to Quick links by the way if you enjoy the tip
5:20 just go ahead and post that in the chat uh let me know that you like it if you in addition to Quick links maybe there
5:26 are some things that that you really want to have organized in your own way now with Microsoft 365
5:34 your profile follows you and you can create these ribbon groups and in in
5:40 Excel so I’m just going to right click on the ribbon and go to customize this ribbon now I already have one created
5:47 here called Sean and in there all I had to do was create a new group excuse me
5:52 let me remove that one there’s that Grace I asked for I’m going
5:57 to create a new tab and we’ll rename it and we’ll call it new
6:02 and maybe you want to call it your name I have mine my name
6:08 here I’m just going to remove this create your own Tab and even can even
6:13 create your own group and in that group you can add these functions as well so
6:18 I’ve done that here I’ve got a group tab called Sean my group is called life
6:24 hacks and here’s a couple things that I like to have at my fingertips instead of trying to Bumble through and find them
6:30 so two nice little tips there to get the end user personalized personalization to
6:35 make yourself more efficient now one of our first tips we’re going to talk about today is you did you know that if you
6:42 have a PDF and it’s got a table inside of it you can bring that into Excel and
6:48 I think that’s really cool we’re going to do that by going to insert excuse me we’re going to go to data by
6:54 the way on the slide deck usually or on the actual tab in the file that you can
7:01 get if you stay to the end and use a QR code usually there’s kind of little
7:06 shortcut instructions here so I use them for myself as well so we’re going to go to Quick links
7:12 excuse me we’re going to go to data get data from file and from PDF we’re going
7:17 to point to our PDF and once you do this this is actually using the power query driver engine in the background to get
7:24 the data it recognizes there’s a page and there’s a table so I can link to
7:30 this table I’m just going to load that table right into here and just like that I’ve brought that
7:37 beautifully into Excel all nice and formatted for me to use as I need there we go that’s getting
7:46 data from a PDF for sure there’s many other places you can get data from but I
7:51 thought that was quite interesting so if you have data in a PDF that you need to get over and I know as an accountant
7:56 that happens a lot we have a PDF of maybe an inventory register or a bank statement those are very very helpful to
8:06 be able to just import that in now I don’t know how well this will come across on our webinar here today but
8:14 text to speech I’ve added this up into my quick access speak cells as its
8:19 actual name and if you are maybe running a tape on an adding machine or you’re
8:25 maybe uh just want to have it read it back to you or you have low vision
8:31 Etc highlighting the cells whether it’s a number or a text and choosing word
8:37 cells will in fact read it out loud 206. I find this helpful when I have to run a
8:44 tape using my audio machine if you still use an adding machine like I do or even a calculator so it can read the numbers
8:50 to you that’s speak cells and I added that to my quick
8:56 access ribbon you can just simply do the same by searching for speak cells in
9:03 your uh tell me what to do search there’s speak cells right there all right if you like that give a plus one
9:09 in the chat next up is camera I like this one a lot before we get into this I want to talk about this data set let me
9:16 close this up maybe tidy up my screen a little bit this data set just a simple data set
9:22 we’re going to use this through a couple of the tips throughout our time here um not really super flashy data set but
9:30 we’ve got a couple columns couple values some items Etc and in this particular case you
9:35 notice here you may notice here that in C5 I’m referencing to another tab on the
9:41 camera tab now what’s cool about this is I might have this question here and I
9:46 might say well how do I I here it’s referencing over here here are the values okay but I want to as I change
9:55 this information I don’t have to jump back over here to see the impact as you
10:01 can see I’ve got quantity sold and my total revenue is Drive is driven by that
10:06 price each so what I could do is I could use the camera function and all I’m
10:12 going to do is I’m going to highlight this data over here and I’m going to use the camera now again building on our our
10:19 first tip I use the camera I added camera to my quick access you can search for it
10:26 and tell me or simply add I’m going to hit the camera button I’m going to come over here and I’m going to put
10:34 my cursor where I want that to be and boom just like that it puts now it’s not
10:39 a screenshot I know what you’re thinking I can just grab a screenshot Sean but watch what happens if I change this
10:45 number remember that with C1 here or C3 just like that it’s going to update and
10:51 show me the changes now it’s not going to I can’t hurt anything but it’s just
10:57 going to give me that view into the other tab this is so cool I love it do you use that do you like using that let
11:04 me know in the comments in the chat uh let’s jump over to conditional formatting here
11:10 conditional formatting is a great and easy way to bring the data to life we’re talking business intelligence at its
11:16 kind of ground level but super important especially in Excel we can use this conditional formatting in a really
11:22 simple way let me zoom out a little bit here and I can use conditional formatting and I can say let’s take a
11:29 look here let’s just highlight the data and we’re going to use some rules we’re going to say let’s do highlights
11:38 uh greater than and we’ll pick our mouth here found the average here we’ll just do greater than 500. real easy to do
11:45 another thing we can do using these simple uh pre uh predetermined
11:51 conditional formats for us is maybe get some other visual cues for us so is it
11:56 trending up or trending down or is it kind of neutral on average very helpful to tell what’s happening with the data
12:03 there’s also ways we can do use formulas to do this if it’s greater than this cell those kind of things but a lot of
12:10 built-in conditional formulas for formatting for so let’s do color scales
12:15 I like this so you can see the the higher the number The Greener it is in this case that’s conditional formatting
12:21 make sure you use it another way you can use conditional formatting here this is conditional formatting kind of on
12:27 steroids this these are incrementally different colors on the on the scale
12:33 then based on the number that is in here the format is condition the conditional
12:39 formatting says if the number is this make it a certain color and what you can do is you can use this to tell a story
12:45 or if you’re really crazy you can use it to make a picture of somebody if you
12:52 want to look at that that’s pretty cool that’s really done using conditional formatting maybe not a really good use
12:58 of my time but super super cool what might be a better use of my time if I can show this is kind of a really
13:04 Advanced way of doing conditional formatting so I’ve got a simple kind of Boolean field here an on or off
13:12 field and I want to do a little bit something else with this I’m going to cheat and copy my formula here but what
13:18 I’m going to do is I’m going to come in here and I’m going to highlight this and go to format cells and I’m going to go
13:24 into custom and I’m going to give it this custom um
13:30 formatting and what I’m saying is if make the color green if it’s one if it’s a one and if it’s a
13:38 negative number make it red use these um emoticons or whatever they’re called
13:44 emojis icons and in order to get into that in this window we’re going to use
13:49 the window Windows button and the decimal or the dot or period that pulls
13:55 up the Emoji menu so yes you can use emojis to
14:02 tell a story in your conditional formatting as well let me hit that and just like that now it’s green if it’s
14:08 complete or it’s pending with a red X if it’s not pretty cool
14:14 uh kind of fancy Advanced conditional formatting that’s just one example let
14:19 me know what you think you can also use Excel to do some sort of what-if analysis this
14:27 is called goal seek and in this data set I’ve got price each um and I’ve got quantity sold and my
14:34 total revenue so I’ve got my cost we’ll get the markup on this Joker um but I’ve got my total profit is 93.10
14:43 and so if I want to know how many do I have to sell for my total profit to be
14:49 twenty thousand what I can do is I can use goal seek so I’m going to go to here
14:54 it is tell me what to do I’m going to go to data what if analysis goal seek and
15:02 this little pop-up menu is going to pop up I want to set this value G5 remember I said my my question is how do I get my
15:10 profit to be twenty thousand and then by changing what cell so I’m asking how
15:15 many do I need to sell and let’s hit okay and it’s going to go through the process and you can see just like that I
15:21 need to sell 41 units to break the 20 thousand dollar profit Mark for this
15:26 that’s pretty cool now I can accept that by hitting OK or I can hit cancel and it’s going to take me right back to my
15:32 original data that’s one example of using goal seek check it out you’ll love it 100 powerful filling blank so this
15:41 happens a lot we get a data set from somewhere and here we go let me zoom in a little bit I’m using the control and
15:48 my mouse wheel to zoom in can also use control on the plus sign but I want this East to fill here this
15:55 West to fill here this West to fill here this South to fill these two this name to be here this thing to be here Etc so
16:03 what I’m going to do I could do something like this I could do that I could copy and paste I
16:11 could double click and drag it down on each one of these couple Mouse strokes but watch this I’m going to highlight it
16:17 I’m going to hit F5 and I’m going to go to special and then blanks and hit okay
16:23 it’s going to highlight my blanks now I’m going to go into the bar here my formula bar and I’m just going to say
16:29 show me the field above this one and hit uh enter
16:36 just gave me one this is where I got tripped up let’s try that again because the trick is we have to hit shift enter
16:43 so I’m going to do F5 again special blanks okay tell it to point to the cell
16:49 above us let’s do one more time because I
16:55 went too fast for myself I’m going to hit
17:01 this cell do plus here and then shift enter is it shift
17:08 enter control enter I’m glad I looked control enter and now it’s going to fill those
17:14 in just like magic pretty cool check that out if you have fill uh blanks in
17:20 your data set and next one up here this is one of my favorites I recently
17:25 learned about this and it was like I wish I wish you know 10 years ago Sean
17:30 would have learned this by the way I’m sliding over for my tabs here each one of these tips are a tab but if you have
17:36 a lot of tabs you can just right click here and see sort of like a menu of them and jump around to them but I’m going to
17:42 slide over here and I’m going to go to Alt Enter and what Alt Enter does is it adds sort of that auto sum for you so if
17:49 you here I’ve got some data set the small data set by quarter and sales and I want a total for each quarter and then
17:56 I won a yearly total so I need a total here total here total here total here total here total here total here you get
18:01 the ID and I’m running out of breath and then I want it also to happen down here and down here so let’s take a look
18:08 at how this works I’m going to highlight all the cells and I’m just gonna hit alt equals and it’s going to automatically
18:15 what put in these total some fields for me that’s pretty cool and it actually is
18:22 the formulas not just giving me the totals that’s pretty slick that’s alt
18:28 equals sorry if I said Alt Enter alt equals is awesome let’s move over to
18:35 the next one here if you have if you have the needs email Excel I think if
18:41 you’re like me what you were doing maybe you’re not let me know if this is helpful but what what I was doing is I
18:46 was taking a screenshot of it and pasting it into an email but if you add to your quick access bar or your Ribbon
18:53 or search for the function called
18:59 send to mail recipients all you have to do is click this button
19:06 tell it I want to send the current sheet and it’s going to put that into the body
19:13 of an email for you now I don’t have Outlook open so this might drag for a
19:18 second but it was that easy in your world you’re gonna have Outlook open like a like a normal working day
19:25 um and it’s going to go ahead and and create that email there we go so I’m going to put this in here I’m going to
19:30 send it to myself and hit send this sheet
19:36 that was pre now to be faster for you because you’re going to have Outlook open running mine had to kind of open up the Outlook engine in the background but
19:43 wasn’t that cool wasn’t that easy that’s awesome I absolutely love that next up
19:49 here Tech split couple things about this one good example here is
19:54 I’ve got some concatenated data here but but maybe this is representative of your chart of accounts if you think about
20:01 think about your chart of accounts and are you doing this are you doing mid
20:06 A3 uh I don’t know five comma six are you
20:12 doing something like that oh I missed it that’s usually how it works for me I don’t get it right but let me show you text split so you could do text split
20:20 and text split is going to say okay I’m going to break this data up I’m going to say text split what text do I want to do
20:27 this text and what delimiter and in this case it’s my Dash
20:32 and that’s it the others are optional boom wow just like that and I can double
20:37 click and drag that down that will save you a ton of time that is Tech split
20:44 anything it could be a comma you could use any delimiter it doesn’t have to be something like a dash or a decimal it
20:50 could be a space an ampersand the letter T whatever you want text split I also have a note here to show control e if
20:58 you’ve ever done uh if you’ve ever seen Flash Fill pop up and I’m kind of jumping ahead here
21:03 because Flash Fill is one of the tips but if I just type in the word middle here
21:08 nothing really happens in Flash Fill but if I hit control e now it says oh I see
21:15 what you did let me do the rest for you in this column now I don’t need this up
21:21 top here but it did immediately pull in those middle um the middle values watch this
21:28 if I do it caps it says oh I saw what you did there Sean good idea let me make
21:33 the others caps too that’s using control e on my keyboard pretty cool check those two out two for one uh we’re on slide 25
21:41 here here is one of my favorite Excel tips this is called automatically
21:47 highlight roll rows you can use your camera on your cell phone to scan that QR code it will take you to a Blog on my
21:54 personal website you’ll also get it in the PDF here I’m going to show you this this is sort of a
21:59 bonus tip I’m not going to walk through how to do it here on the call on the webinar but you there are instructions
22:05 in the blog so what this does is it says for somebody with with weakening
22:10 eyesight like myself can you please highlight the row that my mouse is on and so every time I move my mouse around
22:18 now I can easily see the Run of data left and right because even though this
22:23 is blue and white kind of gradient between the two rows it’s still hard for me anyway and I like this now when you
22:29 get this file it’s going to give you a warning if you stay to the end and you get the sexual file it’s going to give
22:35 you a warning that there’s a macro in here because that’s what this does it uses a macro so don’t worry the file
22:41 isn’t malicious at least at least by Design that’s what the warning is for
22:46 because this is a macro that’s automatically highlighting rows check it out Alt Enter and typing cells
22:54 if you’ve ever had this is an oldie but a goodie if you’ve ever had multiple rows of data and you wanted them to be
23:01 in one cell or vice versa if you’ve got data in one cell and you want to try to
23:06 split it out Alt Enter is how you’re going to get to that kind of additional
23:11 Row in the cell so as we type these four things out these are on different cells
23:17 and if I type if I type on this row and I hit enter it’s going to go down and
23:24 and next Etc so but if I want to put these all in the same cell what I do is I hit I’m
23:31 going to type stuff and I’m going to hit Alt Enter and it’s going to go down a row and I’m going to type some more and
23:37 I’m going to hit Alt Enter and type even more and then when I hit enter it puts it all in there
23:44 um so that’s all to enter love it a little tip in here on how to use how to find that using the Ampersand care
23:51 formula um that’s Alt Enter for typing in a Cell searching using wild cards
23:58 so I’m I’m pretty sure you’re familiar with the find control F or what people
24:04 call search but find to locate things in your data set here we can use wild cards
24:10 the asterisk means any number of characters any value in question mark
24:16 means one single character so you can incorporate these into your
24:24 search or you’re fined so let’s jump over here and here’s a long list of of
24:32 email addresses and I want to search for all of the email addresses that
24:40 start with Luc must have a digit a value after that and
24:46 then anything remain I think that’s probably silly to have that exact formula but I wanted to show you both at
24:53 once so I’m just going to control F and I’m going to type that in Luc question mark and we’ll start with just that one
24:59 now if I do find next here’s kind of two two tips in one find next will take me to the next one
25:06 find next find next but I want to show you find all because this is pretty cool too if you do find all and you open this
25:13 up it’s going to show you all of the results that match your search and oh even cooler if you click on them it
25:20 takes you to that spot in the spreadsheet so think about this for your numbers I absolutely love this so
25:27 another thing we could do is we can say um and it’s going to start with anything and then it’s going to start with a k
25:35 this the last name is going to start with the case so let’s see find all now every last name starts with a K using my
25:42 search and I can quickly jump to them that’s wild cards in search
25:47 pretty cool let me know in the chat if you use that I love that also don’t want
25:52 to forget the powerful find all I use it all the time let’s talk about slicers
25:57 let’s slide over here slide to the right let’s talk about slicers
26:04 slicers are fantastic for many reasons and the the biggest reason to me is
26:13 well because sorting data and filtering data using
26:19 the filter is helpful but it is a couple extra keyboard clicks or Mouse steps so if I
26:27 want to see all of the sales person for Kim I’m just going to change my filter
26:33 choose Kim and there it is now if I need to switch and see Natalya I’ll switch
26:40 and see Natalia this way couple clicks but let me show you a
26:45 slicer so I’m going to remove all these and I’m in this data set and I’m just going to go to
26:52 insert slicer and slicer gives us the ability
26:57 to have this Dynamic sort of dashboard or menu like
27:05 filtering slicing and dicing so here’s my sales person here’s my product so if
27:11 I wanted to replicate what I just did in those seven to ten clicks I want to see
27:16 all of Kim there’s Kim I want to see Natalia there’s Natalia hold the control button down if I want to see Kim and
27:23 Natalya just click them both I can clear my filter by clicking the clear or if I
27:29 want to see all of the Jonathan sales for products 13 and 4.
27:36 or not 13 and 4. now we go 13 and four just like that so that’s kind of taking
27:41 your filter if you will to the next level and that
27:47 to me is really cool also it just makes your data set feel much more like a
27:54 dashboard to me and if you incorporate if you go one step further and you incorporate charts this is going to work
28:03 dynamically and represent the data in the charts so it’s super powerful and
28:08 it’s really easy to do these are these are customizable you can change their color you can change the layout you can
28:14 you can add multiple selectors you could do all sorts of things that’s slicers
28:21 I love it love it love it love it you ever have a spreadsheet that’s got formula errors in it and you don’t know
28:29 where they are and maybe you’ve tried to search for like the pound n a or the
28:34 pound divide and you realize that it’s not really searchable that way I’ve got a data set here with a few errors in it
28:42 but imagine this being much bigger it wasn’t too hard for me to scroll down and see that but imagine a much wider or
28:47 much taller data set and I want to see the errors I want to see if there are errors and this is a really great way to
28:54 handle it so we’re just going to go to control G uh see oops
29:02 kind of get back to the beginning you’re going to control G and then we’re going to go to special in this formula section
29:09 we’re just going to uncheck everything except errors and now we’re gonna hit okay
29:15 and now you see everything that is
29:21 an error is highlighted and as I hit tab on my keyboard it’s
29:27 taking me to the next error what that is so so simple it’s highlighting each one
29:35 and all I’m using is my Tab Key to bounce around for those so that is go to
29:43 errors all right so we’re at about a halfway point in our allotted time for
29:49 for our webinar today I hope you’re learning some things as a brief reminder stick around to the end you’re going to
29:55 have a chance to get this exact Excel spreadsheet we’ll be sure to get you the slides as well let’s get right back to
30:02 it um actually yep so watch window is
30:08 another one I’m not sure why the animation is slowing things down here there we go watch window is another way
30:14 remember the camera tip we showed earlier watch Windows another way that you can kind of see the values from
30:22 other cells so this is in um this is in formulas watch window and
30:29 here I’ve got some things that it’s pointing to and you could see that it’s got this life hacks file with these
30:35 sheets and this is the cell and it’s showing me the values and I can dock this actually up here so as I’m working
30:41 through the spreadsheet wherever I am it’s still showing the values from those fields this is helpful when you’re
30:48 linked to other spreadsheets if you’ve ever used a spreadsheet that is linking
30:54 to another spreadsheet not just another tab but another spreadsheet watch window is quite helpful there and all you have
31:01 to do is add you can find your data source you point to a different file and it will drop it in there and as that
31:08 data changes so will the visual here in watch window I’m going to close that out
31:13 that’s a pretty cool one as well let’s get into an easy one here but helpful is
31:19 that you can color code tabs so if you’ve got a lot of tabs and you want somebody to look at a certain one or
31:26 have their eyes gravitate toward it you just right click on it go to tab color and choose the color for that tab I like
31:34 that I use that often especially when uh you know as an accountant I’m often copying my journal entries from one tab
31:42 to the next and I like to highlight uh the final version or the current month
31:47 that I’m working on so that’s tab color color coding tabs here I’ve got this in
31:54 we’re not going to go through these each one that’s for sure but you’ll get it on the slide deck and I think this is
32:00 really important to remember there’s a lot of ways that you can incorporate the keyboard into
32:08 um navigation and functionality in Excel we know most folks know some of the you
32:15 know tried and true across Windows operating system like control C for copy and and
32:21 control P for Pace control V excuse me control V for pace and control P for
32:27 print um but control s for Save open a new table uh create a new table is Ctrl T
32:36 um go through a couple others here shift tab is going to take you left to sell just like tab takes you right of cell
32:43 shift tab will take your left one uh shift enter will go up a cell while enter will take you down a cell those
32:49 are helpful for moving around control end and control home going to acell A1
32:54 or the last cell uh we used F5 to go to the dialog box earlier another good one
33:02 um some of the the other ones you know control F for find control h for replace control a for select all
33:10 and yeah so I think these are helpful I just wanted to share a list of the the
33:15 known ones that I had um could shift space and control space to select the row and select a column I
33:21 use that often control a for some reason it’s so cool to have it on your twice uh what else and control let’s see control
33:29 9 for hide row and control zero for hide column okay you’ll get that in the slide deck let’s talk about our next one here
33:36 which is changing case changing case
33:41 um we’ve got some data set here just simple formulas here we’re gonna do app equals lower and point to this one it’s
33:47 going to force all of our data into lowercase we can also use the upper
33:52 function equals upper points or data force it all into uppercase double click
33:59 and drag it down uh slide down and then we can also use proper and I like proper
34:06 um it makes our data look nice and well I guess proper but this is going to give
34:11 us our our proper proper text formatting you can see here in multiples it’s all
34:17 caps for each other impactful words in it so those are some easy ways to change
34:23 the text values in your Excel uh text um
34:29 uh case in your Excel file upper lower proper
34:38 let’s go ahead and talk about sorting data this is one of my other favorites I I really really like this one
34:45 so what I was experiencing with you know when we look at this data
34:51 set um that we talked about earlier where is it at here something like this
34:58 where if we wanted to sort it we wanted to sort it out where did it go
35:07 there we go if we wanted to sort it we could sort it we could say okay give me
35:12 sort this but then if we use the filter to sort by this it ignores the previous
35:18 sort right that so using the filters difficult to sort unless it’s just one
35:24 but you could in fact you know highlight your data and go to data sort
35:30 and at a level and sort by another level and change it so it’s several clicks to
35:38 get to that and and although that works when the data changes it doesn’t Resort
35:46 so you have to sort it again and I don’t know that drove me crazy let me put that
35:52 back to where it was and hop over to the sort tab so what I want to show you is a function a dynamic array called
36:00 sort and here is my little data set and I’ve got my shortcut here but I’m going
36:08 to try to build this out so we’re going to go to sort and you see my function dialog box says what’s the data and I’m
36:15 going to say if my data is A3 to c14 and now the next one is sort index now let
36:21 me stop there if you don’t know you can do this you can highlight over these get more information you can open this up in
36:27 the dialog box that gives you also some more information so you notice my sort index says a number eight indicating the
36:34 row or column to sort by so I want to sort by
36:40 I want my name no I want it by color
36:47 then by name then buy amount so I can dynamically do these all three so I’m
36:53 going to do color is column two name is column one
36:58 oops I need to put these in squiggly brackets squiggly brackets being the
37:05 technical term there squiggly bracket close then
37:10 I’m going to say sort order now how do what’s the order I want to sort these in so I want to sort column two
37:16 descending which is negative one I want to sort column one ascending which is
37:21 positive one and then lastly amount descending which is negative one and I’m
37:27 going to squiggly bracket close that one as well and I don’t have anything else
37:32 to add here and you can see it will hit okay and my formula worked and look at how cool that is now I have that sorted by
37:41 color reverse alphabetically then by name alphabetically
37:47 and then by amount highest to lowest so if I look at this Jennifer red 30
37:53 Jennifer read 30 and I make this a one I
37:58 don’t have to resort anymore it’s going to dynamically do it for me and even
38:04 still if I just change this to Yellow because I used you guessed it
38:10 conditional formatting in this sort dialogue in the sort array that we learned earlier it’s going to highlight
38:17 those yellow and because y comes after R it’s going to put at the top of our list that is pretty cool if you like that I
38:25 love that I use sort all the time no longer do we have to Bumble through
38:30 remembering to Resort Oh I thought I was working the the oldest entries or the
38:36 oldest transactions or the smallest or highest dollar amount that sort I
38:41 absolutely love it um and I’m highlighting here that you can you can point that array that
38:48 Dynamic array and use it to build charts and graphs as well so that’s pretty cool
38:55 that’s sorting let’s get into dates I don’t know what this random sheet one
39:02 here is I’m going to delete that let’s get into dates dates dates dates all right we have a
39:08 couple here to talk about and if you use Excel dates can give you
39:15 a little bit of heartburn at least I do me uh but I’m going to cover a few things here so we’re just going to go through if we want to get
39:22 and I’m going to use this cell here we want to get the day the day out of a date formula we can use
39:31 the day function equals day and then point to the day likewise if we want to
39:36 get the month out of a date we can just point to the month
39:42 and pull it out now you may have done like I used to do using the mid in
39:48 theory you could use you guessed it Tech split um if you pasted it as a general value but these functions are quite helpful
39:55 now we can also use we can also use year
40:02 so pretty cool nice and easy nice and easy now next
40:09 step I want to show you is e-date and this is this is the last day of the
40:16 month for the month identified in the formula so I’m going to say e dates B3
40:25 oops sorry e day B3 if I want to use the actual month that that’s in
40:31 um zero excuse me e-date is the actual date we’re going to do minus one we’re going to do minus one so we’re going to
40:37 go a month back we want to go month back that’s what this is doing or we want to go a month ahead ahead that’s what this is doing if
40:45 we drag that down you notice that and Let’s do let’s do two let’s
40:51 jump into February because February gets tricky notice that for the accountants
40:57 in the room if you’re always wanting to use the last day of the month
41:02 um or take this into account when you’re dragging and dropping for February it even takes into account
41:08 that it’s a shorter month so it’s not going to give you some kind of fictional date or roll into March unexpectedly
41:16 pretty cool let’s take a look at the text function for days of the day of the
41:22 week we’re going to use text we’re going to come back over here to B3 and we’re going to say
41:28 give me the tech give me the day okay so no worries I’m gonna drag that down look what happens here I used 1D per day
41:37 I used 1D now if I drag that down when I get to the first and the second
41:43 you see it’s only one digit wide it doesn’t keep it consistent so to keep it
41:50 consistent we can add another D in here to our formatting oops and I’m going to
41:56 double click and drag that down and now it says oh I hear what you’re saying you want these to be two give me give me the
42:01 date but make sure they’re all two digits that’s that’s pretty cool for consistency what happens if we do 3D is
42:10 pretty cool it’s going to give us the day of the week the three character version of the day
42:17 of the week I love it I love it I love it I love it now let’s do 4D we get the
42:23 fully qualified name at the end of the week so if you’ve ever tried to have needed to do this text and D in quotes
42:31 is going to give you that that um custom format that’s going to return the
42:37 day of the week now d stands for day what if we used m per month and that’s going to do exactly
42:45 the same it’s going to return the month and if we do two M’s it’s going to give us
42:50 two digit months and if we do 3ms you guessed it same thing and then naturally
42:57 four digit ma m is going to give us the full name of the month and yes these can
43:03 be nested you can join these together so you could actually do Wednesday
43:09 uh whatever what is today Thursday March 23rd 2023
43:14 um you can incorporate them together so that’s using text that builds out the custom format next we’re going to use
43:21 days days here let’s take a let’s hop into our dialog box here
43:28 this is going to return the number of days between two dates now often folks
43:34 just use the minus sign to kind of take this day minus that day and that works
43:41 sometimes it doesn’t work other times um so what I like to do to be sure is to
43:46 say I’m going to take days and I’m going to use that and here’s my start day
43:52 and my other day and now it tells me how many days are in between those two dates next up
44:01 here is Network days now Network days is not
44:09 the days you hang out and chat with people this is the net working days
44:14 between two dates this can get a little bit tricky but if I want working days
44:20 now uh if you’re us-based it uses the the local by the way it uses your local
44:28 Windows computers work day calendar so us is Monday through Friday if you’re in
44:36 another country and your work schedule is different locally uh you and your
44:43 Windows system is set up as such it would take that into account so we’re going to say between the 19th
44:49 and the 1st of January 2023 and say okay there’s 11 working days 11 working days
44:57 between those two dates now if you may have noticed there’s also this way of saying wait what are your holidays so
45:03 all I did was I have a list of holidays here and it’s going to exclude those if it’s a working day and so now it says
45:09 nine even though there’s four holidays one of these was not a working day so that’s Network days that’s really
45:14 helpful if you’re doing proportionate distributions Etc I love it I love it I hope you like it too eom EO month
45:22 let’s jump into our format here function here Returns the last day of the month
45:27 yep that’s what I thought I was saying earlier we’re going to do equals EO month last day of the month start date
45:34 now if we do zero it’s gonna say the last day of this month whatever month you were pointing to but what about the
45:40 last day of last month that’s a negative one or the last day of
45:46 next month and all this is relative to the date that is called out in the formula so that’s EO month the last day
45:54 of the month and sure if it’s uh if it’s February it’s going to recognize it so if we did
46:00 14 uh next year in 2024 it’s a leap year so it also recognizes that pretty cool
46:06 that’s EO month uh next up is weekday
46:11 I’m gonna do weekday and I’m going to point to F3
46:17 and it’s going to tell you the day of the month or excuse me the day of the week and I’m going to switch this today
46:24 here uh better do let me get the date it’s a
46:29 Monday so our our weekday goes Monday through Sunday so if this if I drag this
46:34 down you can see because these are sequential by date and if I just cheat and copy this down
46:41 here
46:46 it’s going to stick with the third I get I really just used I really did just seven days away yep anyway that’s
46:52 weekday I like that one I hope you do too uh two more uh last one here week number yeah so this is if you use Erp
46:59 systems week number can be quite helpful we’ll just point to a date and it’ll give you the week number in the year
47:05 week num is going to get you there all right let’s move on those are dates a
47:11 lot of cool stuff there if you are interested in seeing the formulas in a
47:17 spreadsheet you can go to formulas show formulas just like that so if you’re
47:23 unsure what value in a big number set is is a sum or multiplying or doing whatever you can quickly see that using
47:30 show formulas awesome tip see a list of tabs I showed you that earlier that’s
47:36 just right clicking on the navigation there next up is Flash Fill let’s get into that with a little teaser on that
47:42 earlier but here I’ve got this fictional name a email this if I just start typing
47:47 brassarte and then kinds it recognizes or it did for a second it recognizes
47:55 what I’m doing and I just hit enter and it will do that uh sure I could do it with
48:01 um just the first name as well but that’s not nearly as impressive uh and yes I can use Ctrl e to get me there or
48:09 let me clear this up um let me get to Dante Lucy and hit
48:16 enter well I could also use text split but Flash Fill is pretty cool all also
48:21 can use Ctrl e got some really cool ones coming up please stick around the Lex
48:27 ones are gonna blow your mind uh what this one will be in the spreadsheet I don’t I forgot to put this one in there
48:32 sorry but that’s a cool one let me get over here really quick lead to
48:40 remove blank rows so if you have a data set and you’re removing blank rows like
48:48 this and you’re highlighting them and you’re right clicking and getting going to
48:55 delete and then shift cells up yep that works
49:01 it’s an Excel just crash on me in the middle of a webinar I think so
49:08 I think so let’s see hang tight there folks
49:15 hang tight I have a plan B
49:23 let’s see let’s clean this up I can’t believe that just happened that’s okay
49:30 we’ll get it fixed we will get it fixed
49:40 hang tight got some really cool ones to show you I promise hang in there for me just some technical difficulties and
49:48 that Grace I asked for earlier
49:56 all right looks like it’s giving me a real hard time here unfortunately so
50:02 I’m gonna continue to try for just a second
50:09 close everything I possibly can there we go all right so we got it closed that’s
50:14 a good step that’s a good sign thanks for your patience looks like we
50:21 lost a few people but hope but not too many please hang in there we got a few good ones to show here I saved some
50:28 goodies for the end and I want to give you a chance to get that file as well
50:35 all right so let’s get into remove blank
50:40 lines and now what we’re gonna do remember I showed you that kind of other
50:46 way and we’re going to put these um back down just give me one more back down
50:51 there what we’re going to do is we’re going to highlight this time thank you by the way for everyone who hung in there and give me a bit of patience and
50:59 Grace to all that reloaded I’m going to highlight this and then I’m going to go to F5 and I’m going to go to special and
51:07 then I’m going to choose blanks and I’m going to hit OK and now it highlights all the blank rows for me automatically
51:15 instead of me doing it one at a time and now all I have to do is right click delete and shift cells up so that has
51:23 just saved me a ton of time what about that one did you like that one I hope you did let’s talk about this next one
51:29 here real quick now I’ve got two more to go two more really cool ones to go move rows or
51:38 columns ladies and gentlemen this is absolutely my top tip I love this if I
51:46 need to move these three together now sure let’s pretend we couldn’t use sort but you’ve done this before you need to
51:52 move data in this so you’re going to come in here you’re going to go to insert shift cells down then you’re going to
51:59 grab whoops I didn’t even insert the right spot that’s what happens usually when you do things the long way you mess
52:05 up now I’m going to come over here I’m going to copy this or maybe I’ll cut it and paste it and then I got to delete
52:11 this right you know where I’m going let’s undo all that and get back to where we were I want this row directly
52:19 underneath this row all I have to do is highlight it let me get rid of that uh
52:26 flashing box I’m going to highlight it hold down the shift key on my keyboard and click
52:32 the outer edge and as I slide it up just put it right in place and drops it right
52:37 in in the I do the wrong one I did and moved the wrong one drops it right in place let’s move this one here hold the
52:44 shift key down slide it up put it right where it needs to do cut out those three four steps and if I wanted to move the
52:50 column I do the same thing hold the shift key down whoops let’s move the header as well that’d be kind of silly
52:56 Slide the shift hold the shift key down and slide it over notice the green is
53:01 bump in between row and column you want to make sure that if you’re moving a column that you
53:07 put the column in the right spot not like I just did and put it up one um but that’s all there is to it hold
53:14 the shift key down and get it where it needs to go that is pretty cool that’s
53:21 how you easily move a row or column without having to Bumble through insert
53:26 cut paste delete leads next and last on our list is X lookup I’m showing this I
53:34 literally had a phone call with a colleague yesterday something very similar and I thought my goodness I should show
53:41 this on the Excel life hacks tomorrow so here it is if you’ve used vlookup before you know that one of the restrictions to
53:48 vlookup is the data must be in the left column and you can only look One
53:53 Direction and One Direction consistently what x lookup does it’s another one of
53:58 those fancy Dynamic arrays is it gives you the chance to look multiple
54:04 directions so let’s talk about what we have here we’ve got data here’s the how many we’ve sold of these items here’s
54:12 how many we’ve purchased of these items we need to get it in another way we need to get it like this or we can use
54:19 um you know this is a super simple example but we need to get it over here so what I’m going to do is I’m going to
54:24 say equals x lookup and I want to look up
54:33 this value comma in this excuse me let me start over I
54:40 want to look up
54:46 this value and I want to look here for that value
54:52 next I want to return because I’m in the sold column the values here from sold
54:57 that’s it and I want an exact match so I’m just going to close that up so
55:04 item one sold is quantity one we see that now I’m going to
55:10 absolute these and drag this down perfect we can cut this over using the X
55:19 lookup and we’re just going to drop this down a row because we’re going to do this on the 5 for the purchase
55:26 boom so now for purchased for item one it’s 11 and we’ll drag that down this x
55:32 lookup is dynamic so guess what we can do if we move this column like we just
55:38 did in the other tip look what it does it literally
55:43 does not break a single thing my point is they don’t have to be in a sort order like your vlookup does right or if I
55:51 just take this this particular one and I’d move it over whoops and I shift move
55:57 it over nothing breaks nothing at all breaks and you can see that as I change
56:02 it’s going to still find the proper value for item two and sold that is X
56:08 lookup check it out it is one of my favorites ladies and gentlemen we are at
56:13 the end of our Excel life hacks session this QR code will take you to a form use
56:18 your camera go to the form and we will be sure to the system will automate if you put your name and email address in
56:24 there you’ll get this exact Excel file please if you need anything relating to your Dynamics deployment or Microsoft
56:31 Office or teams Etc reach out to us we’d love to have a chance to earn your business my name is Sean dorward I want
56:37 to thank you so very much for uh my name is Sean dorward I want to
56:43 thank you so very much for joining us here today for Excel life hacks that was a lot get the file we’ll get you the
56:50 recording we’ll get to the PDF slide deck and I hope you have a great rest of your day thanks so much for joining us
56:55 I’ll see you soon foreign [Music]