5 Things to Avoid in Microsoft Excel
Aug 14, 2023
5 Things to Avoid in Microsoft Excel
In this tutorial, we will show you five tips for things to avoid when using Excel. We will include tips on data structure for using pivot tables, when to use a matrix, date and number formatting, formatting a table rather than a worksheet, and how to merge cells. This a great video for both beginners users in Excel and anyone that has been using Excel for a while. https://www.myexcelonline.com/wp-cont …https://www.myexcelonline.com/107-186 …https://www.myexcelonline.com/101-exc …https://www.myexcelonline.com/107-3.html https://www.myexcelonline.com/107-59 …https://www.amazon.com/dp/1700300911 https://www.amazon.com/dp/1700729675 https://www.amazon.com/101-Best-Excel …https://www.myexcelonline.com/microso …https://www.youtube.com/@MyExcelOnlin …https://www.myexcelonline.com/ https://linkedin.com/in/johnmichaloudis https://www.instagram.com/myexcelonline/ https://www.pinterest.com/myexcelonli …https://www.facebook.com/groups/myexc …https://twitter.com/myexcelonline
Content
0.24 -> - [Narrator] Hi, and welcome
back to myexcelonline.com.
2.85 -> Today, we are going to go
over five things to avoid
6.66 -> when using Microsoft Excel.
9.09 -> If you want to learn more about
Microsoft Excel and Office,
11.97 -> join our academy online course,
14.869 -> and access more than a thousand
video training tutorials,
17.85 -> so that you can advance your level
19.47 -> and get the promotions, pay raises,
21.63 -> or new jobs.
22.65 -> The link to join our academy online course
24.93 -> is in the description.
26.34 -> Our first mistake to avoid
is setting up your data
29.7 -> if you are going to use a pivot table.
32.7 -> In this case,
33.533 -> I have some data for how
much I've spent by month
37.29 -> per category.
39.27 -> So, right here I have the categories
40.92 -> of closed food and utility,
42.87 -> and then I have data by month over here.
47.04 -> But, the problem is this
becomes very difficult
49.2 -> to create a pivot table with,
51.27 -> because the pivot table
usually wants to do some sums
55.41 -> when the table is created.
57.69 -> So, a better way to set this table up
61.246 -> and to get similar results
62.761 -> would be to not separate out
each one of these months here.
67.944 -> So, over here, I'm going
to copy these categories
70.77 -> and paste them over here for
the six months that I have.
74.58 -> And, instead of putting
the month across the top,
77.49 -> I'm just going to put a
date of 1/1 for these,
80.97 -> 2/1 for these,
82.5 -> and so on to the end of my sheet.
84.9 -> And, then, next,
86.33 -> I'm just going to copy
these values over here.
88.02 -> These are my January values, February,
91.32 -> and so on.
92.88 -> So, this is the way I should
have been doing data entry
94.95 -> all along.
96.48 -> Now, if I need to get this result out
98.82 -> just like it is over here,
100.59 -> I'm going to highlight my data here,
102.93 -> go up to insert, go over to pivot table,
106.05 -> and then down here I'm going
to select existing worksheet,
109.86 -> click this up arrow,
111.3 -> and just put my data right here in N-2.
115.454 -> Click this button, and say, okay.
117.42 -> And, now when I go to
build my pivot table,
120.51 -> I'm going to want the category
down here for my rows,
123.75 -> I'm going to select the date,
126.09 -> and drag the months up
here into my columns,
129.84 -> select the down arrow on
date and say remove field,
132.99 -> and take the amounts,
134.738 -> and just put a sum right here.
137.435 -> And, then I'm going to close this.
138.99 -> And, you can see
140.561 -> that the data that I'm
getting here in my pivot table
142.89 -> is the same data that
I'm getting over here
145.26 -> and I can also have my grand totals.
148.276 -> Our second tip for today is,
149.46 -> if you are not using a pivot table,
151.68 -> it is not a good idea
to really put your data
154.71 -> with a comma separating two of the fields.
157.86 -> So, in this case,
158.73 -> we have an amount that
was spent in what months?
161.52 -> January and March.
164.1 -> And, then in this case,
165.76 -> we have another amount that was
spent in February and April.
169.585 -> This is not a very user-friendly
way to read your data.
172.32 -> In this case,
174.027 -> it might be better to put
your data in a matrix.
175.59 -> So, let's start here with
January, February, March.
180.12 -> If we highlight those,
181.23 -> we can drag those over
and use the auto fill
184.539 -> for the rest of the months.
185.46 -> So, in January here,
187.11 -> we spent $500, same in March.
190.38 -> And, in this case we spent 750
192.63 -> in both February and April,
195.6 -> 600 in January and May,
198.3 -> and so on.
199.68 -> And, if we put the numbers
in a matrix like this,
201.54 -> we can just highlight the numbers,
203.82 -> and go to home,
205.56 -> and go to conditional formatting,
208.14 -> and we can highlight ourselves
209.91 -> that are greater than let's say zero,
213.09 -> and say, okay.
214.68 -> This is a more user friendly way to read
217.8 -> the data that you're trying to get across.
219.96 -> If you are liking this video,
221.34 -> please give us a thumbs up
and subscribe to our channel,
224.28 -> and hit the bell button to get notified
226.11 -> when we release our weekly videos.
228.81 -> The next thing to avoid
229.98 -> is bringing in anything
you wanna calculate
232.5 -> in the form of text.
234.673 -> Now, this sometimes happens
when you import data
236.37 -> from a foreign source.
237.84 -> Sometimes, dates and
numbers come in as text
241.41 -> instead of the date or
number you want it to be.
244.38 -> In this case, that's
exactly what's happened.
247.004 -> If I highlight my three dates right here
248.34 -> and I go up to home,
250.05 -> I can see that they've come in as text.
252.66 -> So, if I change the
format there to be a date,
256.53 -> and I go back to Excel,
257.7 -> and I go into this cell,
259.95 -> and I just do a formula
on this for a date,
263.005 -> so I would say equals this
date plus one and hit enter,
266.97 -> I can see that I'm having an issue,
269.1 -> because Excel is still not
calculating my formula.
272.94 -> So, let's click back there and hit delete.
275.64 -> When I click in A-2,
278.19 -> I get a warning right here.
279.57 -> If I click the down arrow,
281.55 -> I can convert this to either
the 1900s or the 2000s.
285.69 -> Let's convert it to the 2000s.
288.564 -> Now, if I click in B-2,
290.22 -> I can do equals this
plus one and hit enter.
294.75 -> And, I can see now Excel is
recognizing this as a date.
298.92 -> So, if I highlight these
three numbers right here,
301.02 -> and I go up to home,
302.07 -> and I go down to number,
305.1 -> I can see that I'm still getting
an error on some of these.
308.13 -> They are left aligned, they're
not being right aligned,
311.128 -> so it doesn't really look like
312.09 -> that this is being formatted as a number.
314.46 -> If I want to fix that,
316.14 -> and I want these all to be right aligned
317.88 -> and formatted as a number,
319.59 -> here's one little quick tip you can do.
321.57 -> You can do equals this cell
times one and hit enter.
326.31 -> And, then I can just copy my formula down
328.65 -> and I can now see that I have
a number that I can work with.
332.04 -> Some versions of Excel
333.87 -> can automatically read
text fields as numbers,
336.48 -> but some cannot.
338.104 -> So, this is a good workaround
338.937 -> when you're in the
situation where it cannot.
340.92 -> Our fourth tip for today
is formatting a table.
344.31 -> So, if you've ever been
in this scenario before
347.432 -> where you highlight a column,
348.75 -> and you want the formatting
that you put in this column
351.999 -> to keep continuing,
353.327 -> so I highlight this
column, and I go to home,
355.59 -> and let's say I make this currency,
358.35 -> and I also make the background yellow.
361.29 -> That looks okay,
362.28 -> but if I keep scrolling down
363.6 -> I can see that I have millions of rows
365.79 -> that I've formatted as yellow,
368.13 -> and that's taking up
extra memory in Excel.
371.122 -> So, I'm going to hit
control Z on both of those,
373.59 -> and let's take up a little less memory
376.138 -> by highlighting this range right here,
377.91 -> and going to insert,
379.65 -> and go to table.
381.12 -> And, it says, where's
the data for your table?
382.95 -> I'm just going to take the
default selection and say, okay.
386.4 -> Now, if I select this column,
388.71 -> I can go up to home
390.66 -> and I can highlight the back of that,
392.28 -> and I can set this to currency.
395.55 -> And, now when I add new rows,
398.449 -> so let's say I have a food expense
399.36 -> for 7/1 for 300.
402.72 -> That's going into a row that
I already had in my table.
407.241 -> But, what if I click
outside my table right here,
409.865 -> and I go to utilities, and I also do 7/1,
413.49 -> and I do 400?
415.95 -> I can see that this has
added a new row to my table.
419.55 -> Let's do one more,
420.66 -> so you can see the
alternating blue background.
422.91 -> If I do close, and I do 7/1,
427.948 -> and let's just say that was 200,
429.36 -> you can see now this
is the blue background,
431.25 -> and this keeps adding rows to my table,
434.728 -> copying the format that I've already done.
435.87 -> And, you can see that these cells here
437.4 -> don't have that format in it
439.552 -> and is not taking up extra space
440.385 -> inside of your Excel workbook.
443.4 -> And, for our final tip, let's
talk about merging cells.
446.79 -> Have you ever done this before
where you highlight a header,
450.03 -> and you go up to home,
451.29 -> and you hit this nice button here
452.58 -> that merges and centers your data?
455.61 -> Let's do that for each one of these years.
458.25 -> It looks a little nice,
because it's centered,
460.02 -> but have you ever gone back,
461.19 -> and just highlighted these columns,
462.9 -> 'cause you wanted a total,
464.76 -> and then you realize it
highlights everything,
467.31 -> because you've hit a merged cell,
470.07 -> and you can't really total up everything?
472.71 -> So, here is a little
workaround to bypass that
475.68 -> I'm going to hit control Z three times,
478.59 -> so that my merging centered
formatting is taken off.
481.214 -> You can see this is back on this cell,
483.18 -> and now I'm going to
highlight these two cells,
486.27 -> and right click, and go to format cells,
489.09 -> and under alignment, under horizontal,
491.88 -> I'm going to go to
center across selection,
495.03 -> and say, okay,
496.68 -> and then I'm just going to right click
499.093 -> and go to format cells again,
500.608 -> go to alignment, under horizontal,
502.62 -> and go to center across selection again.
504.66 -> And, then I'm going to do
that one more time right here.
508.085 -> And, now when I highlight this column,
509.28 -> you can see the numbers down here
511.633 -> where I can sum up and get a min or a max
513.693 -> and use this quick toolbar
down here to gimme some totals.
516.06 -> So, instead of using this button here
519.69 -> that merges your actual cells,
522.06 -> you really only need to
highlight right click,
526.08 -> go to format cells,
527.76 -> and use the center across
selection alignment.
531.42 -> So, hopefully that gives you a few tips
533.31 -> on how to be a little more productive
535.8 -> inside of Microsoft Excel.
537.18 -> If you have any questions or comments,
538.47 -> please leave them below
and we'll get back to you.
540.36 -> Thanks for watching and
see you again next time.
543.12 -> - If you like these video,
544.744 -> subscribe to our YouTube channel,
546.33 -> and if you're really serious
547.59 -> about advancing your
Microsoft Excel skills,
550.98 -> so you can stand up from the crowd,
552.69 -> and get the jobs, promotion,
554.46 -> and pay rises that you deserve,
556.44 -> then click up here and join our
academy online course today.
Source: https://www.youtube.com/watch?v=wvt6GyksdvQ
