The PROBLEMS with using the CTRL key to select cells around a merged cell: 1. You often don't realise/remember there's a merged cell nestled in the data you're selecting until it's too late. By then you've already tried to select the cells and then the merged cell gets in the way. A waste of time that could be avoided if the cells were formatted with 'center across selection'. 2. You can't use the keyboard to select cells either side of the merged cells, so it's no good for all those keyboard shortcut users. 3. Having to hold down the CTRL key to select non-contiguous cells is more time consuming than just applying the 'center across selection' format in the first place.
@@MyOnlineTrainingHub Was that an Office pun?.. The question I have, is there a button to add to the ribbon to replace the merge and center with center across section?
I was in a job I couldn't stand and asked to do a rotation in another department. Fell in love with the job during my rotation but the one skill was lacking was using excel. Ran across your videos and I love the way you break every skill down and explain the steps. My rotation was a three month only rotation but I noticed many people lacked proficient excel skill so I decided to teach myself. But thanks to the skills I was gaining my three month rotation turned into a year and at the end of the year I was offered a job because of what I was able to do. One week ago I was promoted into a job that requires a minimum of five years of experience to qualify for. It took me half that time and I have never loved a job the way I love this one. Thank you for taking the time to make these videos and doing them in a way that's easy to follow.
This is one of those videos that remind me an Advanced Admin Class in Excell. I remember when I was stunned to find advanced features in one of the complex commands and asked: "Is this a new feature?". The instructor replied, "since 1997". Excel is such a huge boatload of functions and possibilities, it can be overwhelming and many don't use more than 1% of the functions anyhow. As a developer, it is always refreshing to see more and more usages for those features and functions. Thank you for the video. Loved it!
Tips #2 and #8 are part of normalization in databases. Mynda shows the proper normalization techniques in each case as if this were a database and not a spreadsheet. Bravo. There's nothing wrong with using Excel as a database, in spite of what some people will tell you. Especially if you have a low number of records and if you have issues with getting the know-how to create and use databases (such as in MS-Access) instead. Some people just don't have the time.
Hard disagree. Excel might be okay for small scale projects. Or as a way to write up your initial batch of data if the alternatives are lacking. But with bigger data, Excel gets inefficient real fast. Not to mention it is way too easy to mess things up. In many cases, Excel is a necessary evil as the managers don't wanna invest into better databases. But that is just incompetence; it doesn't make Excel suitable at all.
Great tips! I fully agree with table formatting (5:50) however, row- and column formatting are internally stored just like that - the (default) format for an entire row or column, and not as the formatting of a million cells for a single column. Still, it's adding formatting to unused cells which can be distracting and look sloppy, so it's good to avoid it where better alternatives are available.
This has to be in my top 5 most useful videos ever! We get in such ruts and ways of doing things, we don't realize what a profound impact, such simple changes can make for ourselves and others. Thank you!
I am very often preaching some of the very same things to colleagues. Now I will simply share this video. However OFFSET was new to me - I’ll watch the video dedicated to it! Thanks and have a great week!
This is a video to my heart, I have been trying to explain it to users that they're making life very difficult by using merged cells. I've managed to circumvent most using VBA but it is a p in the a when I run into them Great video (as always)
I actually learned a lot of the Tipps while doing your courses but definitely the merge and center cells is the most annoying thing I usually see in lots of files. Thanks for sharing this video Mynda!
Center across selection: there is no way I'm falling for this post-modernist vomit. Al Quinn will continue to merge cells while being mindful of and mitigating against the potential pitfalls of doing so.
If you write or use any VBA in your workbooks, merged cells mess with the code there as well. Where I work, we had to ensure that everyone on our small team that modified any of our many workbooks (over 200) did so using Center Across Selection. When someone forgot, the reports for that day came to a grinding halt!
Thanks for this - glad to see some of my personal fixations mentioned - especially Merge/Centre and External Links. For the automatic updating of rows included in a sum I find it simpler to set my SUMs up to include a blank row and to include that in the sum. e.g. if the data is in A1:A8 the the sum will be in A10 and it will be '=SUM(A1:A9)'. Then if I need to insert a row in the sum I just insert at A9 (not A10) and the sum updates automatically.
This video should be mandatory viewing for all accountants. Your point about data that is difficult to aggregate likely being in stored in the wrong shape is spot on. The amount of time I spend re-factoring other people's spreadsheets in Python into 1st normal form so that they can be pivoted! The same thing kind-of applies to merged cells - it's a giveaway sign your data is in the wrong shape. And how did I not know about ctrl-1 after all these years! Thank you!
Glad to hear you agree 🙏 you might like to try Power Query as an alternative to Python for automating gathering and cleaning data: www.myonlinetraininghub.com/introduction-to-power-query
So many vids claim to solve all your problems in Excel, but this is the first vid in which I actually learned something. Very well explained and paced - thanks!
Brilliant. Most of them are my pet peeves. Almost all of them, I would say. Been a long time user of excel since the past two decades but never met "center across selection". Just feel happy to keep learning something anew from you. Please keep up the good work. Thanks a lot.
I've been through quite a few comments and the biggest hit is the merged cell problem. I never new center across selection existed and the ;;; trick was also new to me. Good stuff.
@7:15. Yes you can.... Function GetFontColor (ByVal Target As Range) As Integer GetFontColor = Target.Font.ColorIndex End Function Function GetFillColor (ByVal Target As Range) As Integer GetFillColor = Target.Interior.ColorIndex End Function I do get what you mean that you "can't" natively in Excel. I use the font color formula all the time to check report ouputs to see if someone entered a value in the system or if what is displayed is a system derived value meaning someone didn't do their job. It's quite helpful to easily isolate true missing data vs. what appears to be missing but is not.
Another technique I use for formatting a worksheet. I don't insert unnecessary columns or rows. Adding columns at the left (column A...) and/or rows at the top (row 1...) complicate print margin adjustments. Also, avoid inserting intermediate columns/rows that are empty but color formatted/filled for visual separation purposes. Format cells containing data with borders. Keeping data as contiguous as possible helps when using keyboard shortcuts for worksheet navigation (Ctrl+Home, Ctrl-End, etc.), and helps when creating/troubleshooting formulas.
I will add my voice to the choir and say thanks for Format Across Selection. I used it today! One comment on #6 Formatting to Encode Data, I make the number in the cell 'disappear' by changing the font color to match the fill color. Easier for me then messing with the number format (a topic I need to become more comfortable with).
If I may add a comment, the benefit to using three semicolons is that you never see the "ink" in the cells when selecting them. In older workbooks, setting the foreground (font) color to match the background would allow anyone who selected all of the cells to the see the entries. That is where the custom formatting came from - to hide the contents and present a uniformly pleasant report. With the newer versions of Excel, that no longer applies, so matching the colors works just fine, and is still a really good idea.
This video help alot and I learned about not to Merge Cells and Sum Offset,Conditional Table formatting & Days calculation from Date formatting. Thank alot
2:23 I prefer the first layout, on the left: you use the vertical dimension for category and product, and the horizontal dimension for time. In your second layout, you use the vertical dimension for everything, just as if the sheet were a display of the contents of a table in a database. A sheet is not a database table. There are two dimensions to use for layout.
The problem with the first layout is that you cannot easily use the built in tools, like many of the functions and PivotTables. If you want to report on your data with the 2 dimensions then you can quickly whip up a PivotTable to do that and more, but when your data is already Pivoted, your options are limited.
Relevant tips. Thanks for sharing. We have come across all these scenarios. The 'dates formatted as text' is the most common one for me. Whenever customers reach out to me asking why formula results are not as expected, the first thing I check is if the date inputs are treated as text. Best wishes.
Not sure how I didn't know about the offset function, but that is incredibly useful. So is the "center across selection" functionality as well. Thanks for this video.
Great video - thankyou. I prefer to keep my financial data in table format - for readability, then unpivot in PowerQuery. The times I have unpivoted table-form data into tabular data has always generated questions and confusion from users -- better to keep the data-analysis formatting hidden from your audience, I think.
Wow! First time watcher, just subscribed! What a pleasant woman. **I am going to post this here in the hopes that it will help SOMEONE besides me! I recently exported a bunch of data from our internal database and was trying to do a simple column values match function in a VLOOKUP. No matter what I did, I kept getting that #N/A thing. It was driving me nuts because I could see that I had the same value in both columns and was attempting to find rows that were missing, etc. After what seemed like HOURS of trying to figure it out, I discovered that all of the data in one column came out of our database with four space characters after each entry!!! WHAT THE HECK! After doing a simple Find/Replace, eliminating the spaces, the VLOOKUP formula worked fine!
5:50 Cells only contain data when the cell is modified with data. This applies to formatting. The cell only contains formatting data when it contains actual data. Empty (untouched) cells inherit formatting from their row or column header information. That's why applying formatting to an entire row or column doesn't drastically expand the file.
When a colleague puts an external link into an Excel file is when I reach for my horse whip. My other colleague from our other site was telling me about his work moving people's files from shared folders to share point, including a lot of excel files with external links. The cracking of my whip sounds a lot better than that tale. People should get a livid appreciation of what "deprecated" means. I'm always happy to help them improve.
@@MyOnlineTrainingHub and Your channel is seriously helping me! I can tell You that after watching some of Your videos on power queries we made quite an impression on the job! E.g. passing parameters to an SQL server to collect production data from the MES over the specified range of dates, aggregating data exported to folders to analyze the permanence of trucks and external workers in our site, and even monitoring the epidemic by reading data directly from the internet. I really like power queries. Thank You a lot!
I just stumbled across this today and it's brilliant. It covers a lot of my pet peeves, but I also learned a lot about Excel I didn't know before--and I thought I was sort of a power user!
Brickin and Frankston would be proud. We used to use Visicalc on Apple II computers, open a cell full-screen and it became a primitive word processor! Or scientific calculator. Or database. Or...But that was 1979. Excel has become a force of nature. Nicely done.
I suddenly have found this treasure. Excel was always an interest of mine. But I never dared. Now you have given me the courage after 15 years. Thank you very, very much.
Thank you, I picked up a couple of tips, and will make use of "center across selection". At 10:00, I see why you used OFFSET rather than just typing "J12", but I'll probably just stick with my habits which include never adding a new row at the very bottom of a range, even if that's where the new data belongs. Instead, I add a row at the next-to-bottom (so that the range extends itself), and then COPY VALUES from the bottom row up one. Alternatively, I extend the formula down to the blank row between the data and the sum. I like your way, too; I just developed a different habit.
Great to hear you found it helpful, James! By all means use whatever works for you with the SUM formulas. Habits are difficult to change...but sometimes it's worth it :-)
Thank you. I learnt something by watching this. The Centre across selection got my attention straight away and the rest of the video kept it. Registration on your website returned a Wordpress error but the registration has been created successfully anyway.
Some great tips here but I would hope to convince you otherwise with column and row formatting at 5:50. All you are doing in reality is setting a default format for the column or row, one extra piece of data. Just as there is a default format for the whole spreadsheet, the column format overrides that sheetwide default and any individual cell formatting overrides that in turn. Only as cell content is rendered, on screen or in print, is the formatting applied, the program checks the cell for formatting then the column/row then the sheet. (Cells affected by both column and row formatting would require some individual treatment). Quite the opposite to your suggestion, column and row formatting should result in less data being added to your file than would be by selecting and applying formatting to the range of cells as you did. It certainly doesn't add data to the million cells in the column etc, that is just the illusion.
It is crazy to me how much I've learned off the world wide web. Thank you much for this video. I got some bits of useful information. Now to apply this knowledge and get me a job.
This was recommended to me, and I needed to watch something hands-free while I ate, so, uh -- why not? I did not expect that every single one of these tips would be so useful. Great tips. I also didn't expect to be liking and subscribing by the end, but I did.
Love the video even though I was familiar with most items. However, you are incorrect about #5. I was sure you were just by logic, but tested it out. Logically if you highlight an entire column or row the metadata behind it could be something like "Range(B:B).HighlightColor=Blue" whereas a specific range would be "Range(B2:B876).HighlightColor=Blue". In my simple example it's 4 extra characters to describe what is highlighted, so I'd argue either way is fine and wouldn't kill performance or take up much space. However, I compared four files and have them in order of file size (note, the first three take the same amount of space on disk): - my base file: 453,739 bytes - my base file with columns highlighted: 453,853 bytes (F:F in tab 1 and H:H in tab 2) - my base file with two specific ranges highlighted: 453,862 bytes (F1:F1325 in tab 1 and H1:H1081 in tab 2) - my base file with created tables, removed formatting, highlighted table columns: 456,809 bytes (column F in table in tab 1 and column H in table in tab 2) No noticeable difference from the base file, but full column technically no unnecessary data. In fact your suggestion adds size to the file, but the table feature is useful for other things. Edit: slightly more readable.
Thanks Mynda - as unusal my knowledge of Excel has grown after watching this video I like the offset trick with the Sum formula and I need to learn a lot more about Power Query. Also, had a recent issue with external links but after modifying all formulas to use the current workbook, I still encountered this message when opening the workbook. After persevering with the issue for a few days, I opened the Named Manager to modify a reference and was relieved to find a number of external references hiding in there.
100% Mynda! Absolutely! I sometimes wonder what life would be like if everyone followed these best practices. However, in some twisted way, I'm sure that it would mean that the 'Excel gurus' of the world would be less valued. Great content as always! 😊
How is it possible that after decades of intensive advanced Excell usage, you still are able to learn me stuff that is really useful? Like the center across selection! How much more did I miss?! Great video, thanks.
Oh as an excel user who is reasonably good as far as I thought I'm 1 minute into this video and you've already shown me the shortcut for format cells. Now I shall watch the rest. Edit: Many shortcuts very good thank you.
I was happy to hear "merge and center cells" being mentioned first, because IT IS MOST DEFINITELY THE WORST! Whenever I receive a s/sheet to analyze or debug, it's the first thing I try to get rid of, and replace it with "Center across selection" if it is still need to preserve presentation.
3:24 I agree! The cells' values should *be* dates (not merely be strings formatted like dates). That way, you can sort into chronological order. And correct their format from that misbegotten MMM D, YYYY you show there, to a user-friendlier D MMM YYYY, or a computer-friendlier YYYY-MM-DD. And add or remove the day of the week merely by reformatting (you don't have to hack each string separately). And people can localise the names of days of the week and months to their own language.
Item 4 is smart. Use powerr query to reference other workbooks. But downside is my colleagues need to remember to refresh data, and changes in the other file will not automatically show in the main file. So pros and cons to both approaches.
I was relieved that I already had all of these on my radar (especially #7), BUT that doesn't mean I didn't learn anything. ';;;' was a new one for me. Thanks for the great video!
Good advice. The thing about range formatting was a bit simplified, though. Excel does not generally save formatting for an entire row on a cell by cell basis but of course excessive formatting and copying and pasting and cutting and inserting can cause problems. But one thing I feel is missing is data tables stored as range with autofilter where people add new columns without updating the autofilter. Result is a partial filter that will make a total mess of those added columns next time someone does a sort with the autofilters.
Auto filter is the devil... Especially when using macros on the data, the amount of time spent on making code that counteract all possible user induced errors take up 90% of the time. The errors aren't always obvious
Good video. The Center Across Selection really should be an icon on the ribbon, as I'm sure you'll agree. That has seemed like a no-brainer improvement for ever. Maybe now that they've tackled unhiding multiple sheets they can address that! I was surprised to hear what you said about applying formatting to a whole column. Agreed that using a table is the best solution, but absent that, the file size is actually LARGER when you format only some of the cells rather than the whole column.
For me, using OFFSET in a SUM function is complicated if I don’t wish to create table data. I use a helper column (generally to the left) with numbers in each cell. The numbers may be sequential (1,2,3,…) or a preferred sort order that overrides/augments the data itself. When I add a row of data that will be at the bottom (or top) of the SUM’ed range, I insert the row within the existing range and enter a number in the helper column’s cell that will reflect where the new data should fall after a re-sort. This method also automatically copies the formatting of the range above the newly inserted row.
The only macro in my personal work book does Centre across Selection, for which I have a button on my very bloated QUAT (I do miss context sensitive toolbars). Thanks for a very useful video
Yes, but it's super annoying when you selecting a load of cells in a column and all of a sudden you've selected multiple columns because one of the cells is merged. Then you have to start all over again with CTRL.
Excel'lent (get it?) tutorial and presentation. I really liked the explanation about putting too much data in a single cell; I've spent a lot of time showing people the benefit (as you did) with keeping the data separated so that it can be better worked with. Keep up the good work. Oh, also, thank you for reminding me about the "center across selection" feature. I had completely forgotten about that one!!! :)
Retired now, and happily so. Offset() is a nice addition that really would have been useful long ago (in the '90s). Excel came with apparently handy tool kits that were usually useful. I have to admit though, that it was Excel that drove me away from spreadsheet use almost completely. We used statistics extensively for archaeological data. We discovered that Excel at the time would not calculate a variance properly with certain data sets, and not large ones either. Evidently certain combinations of numbers caused problems. These days Excel handles that data properly, but back then the production of a negative variance lead to the adoption of data base management systems and statistical packages. I still am asked to analyze dreadfully formatted excel files similar to the "one year per column" example used in the video. I have come to save the data, usually saved in flat, single table files, as text files, which are imported into R and reshaped into a proper format that can be easily normalized, query and analyzed statistically.
Great to hear you're still making use of Excel. I recommend you check out Power Query for cleaning and reshaping your data as it's built into Excel: www.myonlinetraininghub.com/introduction-to-power-query
@@MyOnlineTrainingHub I was interested in that when you mentioned and looked up the add on for Office 10. It would have been useful to know ten or more years ago. The teams I worked with frequently had conversations about why certain tools were so very hard to locate. There's nothing quite as slow and boring as reshaping a spreadsheet by hand. What I concluded was that many amateurs (in assembling data) tended to structure their sheets with what amounted to built in query results. They then would become frustrated when they wanted some other kind of result and could not figure out why "you can't there from here." I've had problems with commercial statistical software as well, when a simple tool like Fisher's exact test produced erroneous results. Some companies would thank you for notifying them that there was a problem, others would go into denial, and not correct the problem until their competitors did.
"Center across selected" looks great, though I am not sure how much I will use it instead of "merge and center". Why? Usually I use it at the table head, when when it also help with formatting of cells next and not interfere with any functions. And as the table head is 2-3 rows high (sometimes column group names; column names, units), then "format as a table" do not work properly in many cases and simple hand sorting as "select all, standard lines everywhere + wide lines around" and afterwards doing whole head with "all wide lines" or just widening sections you need to differentiate in groups. Conditional formating is great, too. Love your ;;; input, love it and will save this vid for the future. And, btw, nice shirt :)
I agree with all your tips except to use the offset function. There are two big problems with Offset; 1, it makes it hard to “audit” the formula; whereas with sum you can easily audit which cells are being summed by pressing F2 when selecting the F2, you can’t do that with OFFSET. 2. Using the offset function many times within large excel sheets (ie when modelling) significantly impacts performance of the sheet. Avoid OFFSET when you can
Great tip on Center Across Selection instead of merge and center. Hopefully MS will replace that merge and center shortcut in the ribbon with the Center Across Selection instead or at least make it available to add to the ribbon.
Hello, Mynda -- the comment about formatting whole rows and whole columns was contrary to what I had learned years ago: that whole row or whole column formatting was a row or column property, and not cell properties. So I just ran a small test. An empty xlsx workbook is 9,569 bytes on my machine. A workbook with Sheet1!1:1 and Sheet1!A:A filled red (1,064,959 cells) is 10,081 bytes. A third workbook with Sheet1!A1:Z1000 filled red (26,000 cells) is 74,122 bytes. In other words, a workbook with 3% of the cells formatted is 7.35 times the size. I believe it is still true that it is more efficient to format whole rows or columns when available.
Thanks, I use power query a lot to read files stored in folders. If the sheet names are different, it will not load if your sample file is Source{[Item="Sheet1",Kind="Sheet"]}[Data]. Instead if you have Source{0}[Data] it will read in the first sheet, no matter what it is called. Then you don't have to go around writing macros to change sheet names in folders!
Modifiers? You can use CTRL to select non contiguous ranges, but that doesn’t fix the sorting and filtering problems that also come with merge & center.
@@MyOnlineTrainingHub Yes, your solution is obviously the right way to do it for many reasons. But you made it sound like its impossible to select beyond the first merged cell, which it is not.
Been using excel for a long time, but in a relatively simplistic way. I have long known there was way more functionality than I knew how to use, so it is great to see some of those demonstrated with straightforward explanations. And, at least half of this video is of features I will be able to use, hopefully more effectively than my current workarounds. Subscribed, liked, and following.
Calendar data as text you can't avoid when you need historical dates prior to the year 1900. That's a bug in the handling of calendar data not fixed by MS since version 1 and all other spreadsheet program mimics it due to compatibility. There are calendar and time formats out there that can handle other calendars than the gregorian too, and that support different time zones within a single spreadsheet, but MS refuses to use them for Excel.
What a fantastic tip video! Just to learn what you shared in tip #1 alone was worth the watch (but the rest was really enlightening as well.) Thanks for all that info!
Some excellent good practice tips here, but I have to quibble with the use of OFFSET, which while an extremely useful formula, is a bit of a sledgehammer to crack a nut for SUM formulae. I've been using spreadsheets for nearly 40 years, and for all that time simple spreadsheet good practice is to keep a spacer row or column just before the SUM and sum to that. You can reduce the row/column height/width of the spacer to make it look neat. Also, while you are at it, you may as well do similar with the start of the SUM range, in this case sum to text column heading. Inserting a spacer row/column is a simple tip you can give a complete beginner from day one.
I agree, keeping a spacer row is perfect if you're the only one entering data in the spreadsheet and you know to leave a row, but when you're sharing the file with others who are less disciplined, the OFFSET function is a more robust option.
@@MyOnlineTrainingHub If you reduce the height of the spacer so it's thin but visible and put a top border on the SUM cell it makes it harder for anyone to mess it up. But yes, I've had colleagues who can mess almost anything up 😄
The PROBLEMS with using the CTRL key to select cells around a merged cell:
1. You often don't realise/remember there's a merged cell nestled in the data you're selecting until it's too late. By then you've already tried to select the cells and then the merged cell gets in the way. A waste of time that could be avoided if the cells were formatted with 'center across selection'.
2. You can't use the keyboard to select cells either side of the merged cells, so it's no good for all those keyboard shortcut users.
3. Having to hold down the CTRL key to select non-contiguous cells is more time consuming than just applying the 'center across selection' format in the first place.
Been years working with excel, but just found out "center across selection". Thank you
Glad I could spread the word :-)
@@MyOnlineTrainingHub Was that an Office pun?..
The question I have, is there a button to add to the ribbon to replace the merge and center with center across section?
I also didn't know about ctrl+1
@@court2379 Check out this video from MrExcel (link provided from the relevant time in the video): ua-cam.com/video/gyui_D5g5MI/v-deo.html
Same here. My jaw dropped.
I was in a job I couldn't stand and asked to do a rotation in another department. Fell in love with the job during my rotation but the one skill was lacking was using excel. Ran across your videos and I love the way you break every skill down and explain the steps. My rotation was a three month only rotation but I noticed many people lacked proficient excel skill so I decided to teach myself. But thanks to the skills I was gaining my three month rotation turned into a year and at the end of the year I was offered a job because of what I was able to do. One week ago I was promoted into a job that requires a minimum of five years of experience to qualify for. It took me half that time and I have never loved a job the way I love this one.
Thank you for taking the time to make these videos and doing them in a way that's easy to follow.
Wow, congratulations on your success! Just goes to show what you can achieve with hard work 👍
Cracking story ! Well done you !
This is one of those videos that remind me an Advanced Admin Class in Excell. I remember when I was stunned to find advanced features in one of the complex commands and asked: "Is this a new feature?". The instructor replied, "since 1997". Excel is such a huge boatload of functions and possibilities, it can be overwhelming and many don't use more than 1% of the functions anyhow. As a developer, it is always refreshing to see more and more usages for those features and functions. Thank you for the video. Loved it!
Thanks so much, Ron!
much like our brains...if only we understood HOW to use it. 😁
I love ASAP Utilities. Saves me tons of labor.
Tips #2 and #8 are part of normalization in databases. Mynda shows the proper normalization techniques in each case as if this were a database and not a spreadsheet. Bravo.
There's nothing wrong with using Excel as a database, in spite of what some people will tell you. Especially if you have a low number of records and if you have issues with getting the know-how to create and use databases (such as in MS-Access) instead. Some people just don't have the time.
Thanks, Scott! Well said 😊
Hard disagree. Excel might be okay for small scale projects. Or as a way to write up your initial batch of data if the alternatives are lacking. But with bigger data, Excel gets inefficient real fast. Not to mention it is way too easy to mess things up. In many cases, Excel is a necessary evil as the managers don't wanna invest into better databases. But that is just incompetence; it doesn't make Excel suitable at all.
0:12 Center Across Selection - simply Wow! :) Thank you for this very elegant and proper approach!
Glad you liked it, Max!
Great tips! I fully agree with table formatting (5:50) however, row- and column formatting are internally stored just like that - the (default) format for an entire row or column, and not as the formatting of a million cells for a single column. Still, it's adding formatting to unused cells which can be distracting and look sloppy, so it's good to avoid it where better alternatives are available.
Cheers, Bart. Thanks for the feedback.
This has to be in my top 5 most useful videos ever! We get in such ruts and ways of doing things, we don't realize what a profound impact, such simple changes can make for ourselves and others. Thank you!
Well said, Chris!
I am very often preaching some of the very same things to colleagues. Now I will simply share this video. However OFFSET was new to me - I’ll watch the video dedicated to it! Thanks and have a great week!
Great to hear, Benoit! Thanks for sharing my video too 😊
This is a video to my heart, I have been trying to explain it to users that they're making life very difficult by using merged cells.
I've managed to circumvent most using VBA but it is a p in the a when I run into them
Great video (as always)
Hope you can share it with others, Hans 😊
@@MyOnlineTrainingHub I always point to your site when people ask where they can find clear and concise explanations.
This video is golden for the first tip. Center across selection?!? Where has that been my whole Excel life? Thank you.
:-) glad you liked it, Traci!
I actually learned a lot of the Tipps while doing your courses but definitely the merge and center cells is the most annoying thing I usually see in lots of files. Thanks for sharing this video Mynda!
Great to hear, Maria!
Centre across selection???? 🤯 I've been Excelling in the stone-age! Plus the other tips and tricks are super-helpful! Thanks Mynda!
this feature also blew my mind
Glad you picked up some new ideas, Jatin!
Center across selection!!! That was an amazing tip!!
Center across selection: there is no way I'm falling for this post-modernist vomit. Al Quinn will continue to merge cells while being mindful of and mitigating against the potential pitfalls of doing so.
If you write or use any VBA in your workbooks, merged cells mess with the code there as well. Where I work, we had to ensure that everyone on our small team that modified any of our many workbooks (over 200) did so using Center Across Selection. When someone forgot, the reports for that day came to a grinding halt!
Thanks for this - glad to see some of my personal fixations mentioned - especially Merge/Centre and External Links.
For the automatic updating of rows included in a sum I find it simpler to set my SUMs up to include a blank row and to include that in the sum. e.g. if the data is in A1:A8 the the sum will be in A10 and it will be '=SUM(A1:A9)'. Then if I need to insert a row in the sum I just insert at A9 (not A10) and the sum updates automatically.
Glad you could relate, Angela. Your SUM fix is fine if you're the only person using the workbook and you know never to insert data in the empty row! 😉
Just showed my excel guru your first tip and they were quite excited and impressed! Thanks for sharing these tips.
Great to hear!
7:00 Alternatively, you can format the text the same color as the fill which will eliminate typing.
Thank for the whole video.
Cheers 🍻
Yes, formatting the same colour is a great alternative 👍
This video should be mandatory viewing for all accountants. Your point about data that is difficult to aggregate likely being in stored in the wrong shape is spot on. The amount of time I spend re-factoring other people's spreadsheets in Python into 1st normal form so that they can be pivoted! The same thing kind-of applies to merged cells - it's a giveaway sign your data is in the wrong shape. And how did I not know about ctrl-1 after all these years! Thank you!
Glad to hear you agree 🙏 you might like to try Power Query as an alternative to Python for automating gathering and cleaning data: www.myonlinetraininghub.com/introduction-to-power-query
This video was worth it as soon as I saw Center Across Selection. :)
Great to hear, Dayve!
I as well wasn't aware of this. Thank you.
Centre accross selection was the option well before cell merging was introduced. Merging has its uses but has side effects as this lady has shown.
So many vids claim to solve all your problems in Excel, but this is the first vid in which I actually learned something. Very well explained and paced - thanks!
So great to hear 🙏
Brilliant. Most of them are my pet peeves. Almost all of them, I would say. Been a long time user of excel since the past two decades but never met "center across selection". Just feel happy to keep learning something anew from you.
Please keep up the good work. Thanks a lot.
Great to hear, Ravi!
I've been through quite a few comments and the biggest hit is the merged cell problem. I never new center across selection existed and the ;;; trick was also new to me. Good stuff.
Great to hear 😊
@7:15. Yes you can....
Function GetFontColor (ByVal Target As Range) As Integer
GetFontColor = Target.Font.ColorIndex
End Function
Function GetFillColor (ByVal Target As Range) As Integer
GetFillColor = Target.Interior.ColorIndex
End Function
I do get what you mean that you "can't" natively in Excel. I use the font color formula all the time to check report ouputs to see if someone entered a value in the system or if what is displayed is a system derived value meaning someone didn't do their job. It's quite helpful to easily isolate true missing data vs. what appears to be missing but is not.
Yeah, that's VBA though...requires the file to be saved as .xlsm etc.
Another brilliant video, Mynda! I've been working with excel for years and found I still have more to learn. TY and sharing!
Thanks so much!
I have been using excel for 14 years but never knew the first trick. Thanks for showing such a fascinating feature.
Glad you liked it!
Another technique I use for formatting a worksheet. I don't insert unnecessary columns or rows. Adding columns at the left (column A...) and/or rows at the top (row 1...) complicate print margin adjustments. Also, avoid inserting intermediate columns/rows that are empty but color formatted/filled for visual separation purposes. Format cells containing data with borders. Keeping data as contiguous as possible helps when using keyboard shortcuts for worksheet navigation (Ctrl+Home, Ctrl-End, etc.), and helps when creating/troubleshooting formulas.
Yes, good tip 👍
I will add my voice to the choir and say thanks for Format Across Selection. I used it today! One comment on #6 Formatting to Encode Data, I make the number in the cell 'disappear' by changing the font color to match the fill color. Easier for me then messing with the number format (a topic I need to become more comfortable with).
Great to hear! Good idea on using the same colour for the font :-)
If I may add a comment, the benefit to using three semicolons is that you never see the "ink" in the cells when selecting them.
In older workbooks, setting the foreground (font) color to match the background would allow anyone who selected all of the cells to the see the entries. That is where the custom formatting came from - to hide the contents and present a uniformly pleasant report.
With the newer versions of Excel, that no longer applies, so matching the colors works just fine, and is still a really good idea.
This video help alot and I learned about not to Merge Cells and Sum Offset,Conditional Table formatting & Days calculation from Date formatting. Thank alot
Great to hear, Shahi!
2:23 I prefer the first layout, on the left: you use the vertical dimension for category and product, and the horizontal dimension for time. In your second layout, you use the vertical dimension for everything, just as if the sheet were a display of the contents of a table in a database. A sheet is not a database table. There are two dimensions to use for layout.
The problem with the first layout is that you cannot easily use the built in tools, like many of the functions and PivotTables. If you want to report on your data with the 2 dimensions then you can quickly whip up a PivotTable to do that and more, but when your data is already Pivoted, your options are limited.
Relevant tips. Thanks for sharing. We have come across all these scenarios. The 'dates formatted as text' is the most common one for me. Whenever customers reach out to me asking why formula results are not as expected, the first thing I check is if the date inputs are treated as text.
Best wishes.
Cheers, Indzara!
Kindly share Power query for external link
Not sure how I didn't know about the offset function, but that is incredibly useful. So is the "center across selection" functionality as well. Thanks for this video.
Great to hear!
Great video - thankyou.
I prefer to keep my financial data in table format - for readability, then unpivot in PowerQuery. The times I have unpivoted table-form data into tabular data has always generated questions and confusion from users -- better to keep the data-analysis formatting hidden from your audience, I think.
I agree, users don't need to see the analysis if that's not their job.
Wow! First time watcher, just subscribed! What a pleasant woman. **I am going to post this here in the hopes that it will help SOMEONE besides me! I recently exported a bunch of data from our internal database and was trying to do a simple column values match function in a VLOOKUP. No matter what I did, I kept getting that #N/A thing. It was driving me nuts because I could see that I had the same value in both columns and was attempting to find rows that were missing, etc. After what seemed like HOURS of trying to figure it out, I discovered that all of the data in one column came out of our database with four space characters after each entry!!! WHAT THE HECK! After doing a simple Find/Replace, eliminating the spaces, the VLOOKUP formula worked fine!
Thanks for sharing, James! Pesky leading and trailing spaces can be a pain.
5:50 Cells only contain data when the cell is modified with data. This applies to formatting.
The cell only contains formatting data when it contains actual data.
Empty (untouched) cells inherit formatting from their row or column header information.
That's why applying formatting to an entire row or column doesn't drastically expand the file.
I always considered myself to be competent in Excel; but most of these I never knew. This will improve my worksheets significantly! Thank you!
Great to hear 😊
When a colleague puts an external link into an Excel file is when I reach for my horse whip.
My other colleague from our other site was telling me about his work moving people's files from shared folders to share point, including a lot of excel files with external links.
The cracking of my whip sounds a lot better than that tale. People should get a livid appreciation of what "deprecated" means. I'm always happy to help them improve.
:-) great to hear you're helping pave the way to better practices, Stefano.
@@MyOnlineTrainingHub and Your channel is seriously helping me!
I can tell You that after watching some of Your videos on power queries we made quite an impression on the job! E.g. passing parameters to an SQL server to collect production data from the MES over the specified range of dates, aggregating data exported to folders to analyze the permanence of trucks and external workers in our site, and even monitoring the epidemic by reading data directly from the internet. I really like power queries.
Thank You a lot!
I just stumbled across this today and it's brilliant. It covers a lot of my pet peeves, but I also learned a lot about Excel I didn't know before--and I thought I was sort of a power user!
Glad you enjoyed it!
Brickin and Frankston would be proud. We used to use Visicalc on Apple II computers, open a cell full-screen and it became a primitive word processor! Or scientific calculator. Or database. Or...But that was 1979. Excel has become a force of nature. Nicely done.
Cheers, David!
I suddenly have found this treasure. Excel was always an interest of mine. But I never dared. Now you have given me the courage after 15 years. Thank you very, very much.
That's wonderful to hear!
Thank you, I picked up a couple of tips, and will make use of "center across selection". At 10:00, I see why you used OFFSET rather than just typing "J12", but I'll probably just stick with my habits which include never adding a new row at the very bottom of a range, even if that's where the new data belongs. Instead, I add a row at the next-to-bottom (so that the range extends itself), and then COPY VALUES from the bottom row up one. Alternatively, I extend the formula down to the blank row between the data and the sum. I like your way, too; I just developed a different habit.
Great to hear you found it helpful, James! By all means use whatever works for you with the SUM formulas. Habits are difficult to change...but sometimes it's worth it :-)
Learned a lot from this one, some of which I am doing. But, other things I had no clue about. Thank you as always!
Great to hear, Jennifer 😊
Thank you. I learnt something by watching this. The Centre across selection got my attention straight away and the rest of the video kept it. Registration on your website returned a Wordpress error but the registration has been created successfully anyway.
Great to hear you found the video helpful. Thanks for the feedback about the Wordpress error. We'll look into it.
Some great tips here but I would hope to convince you otherwise with column and row formatting at 5:50.
All you are doing in reality is setting a default format for the column or row, one extra piece of data. Just as there is a default format for the whole spreadsheet, the column format overrides that sheetwide default and any individual cell formatting overrides that in turn. Only as cell content is rendered, on screen or in print, is the formatting applied, the program checks the cell for formatting then the column/row then the sheet. (Cells affected by both column and row formatting would require some individual treatment).
Quite the opposite to your suggestion, column and row formatting should result in less data being added to your file than would be by selecting and applying formatting to the range of cells as you did.
It certainly doesn't add data to the million cells in the column etc, that is just the illusion.
Thanks for sharing, Ross!
I don't usually use the built-in features of excel but center across selection is one of the awesome features that i have learned. Thank you. 😁
Great to hear!
I don't seem to have the same problem. Selects fine across merged cells in Libre Office.
There is something, always something, that those ex-"Excel experts" can learn. I learn and enjoy. Thank you, Mynda.
Glad you liked it, Ernesto!
It is crazy to me how much I've learned off the world wide web.
Thank you much for this video. I got some bits of useful information. Now to apply this knowledge and get me a job.
Glad it was helpful!
SUPER Useful! I am guilty of many of these myself but now I'm a bit wiser! Thank you so much.
Glad it was helpful!
This was recommended to me, and I needed to watch something hands-free while I ate, so, uh -- why not? I did not expect that every single one of these tips would be so useful. Great tips. I also didn't expect to be liking and subscribing by the end, but I did.
So pleased you enjoyed it, John!
Love the video even though I was familiar with most items. However, you are incorrect about #5. I was sure you were just by logic, but tested it out. Logically if you highlight an entire column or row the metadata behind it could be something like "Range(B:B).HighlightColor=Blue" whereas a specific range would be "Range(B2:B876).HighlightColor=Blue". In my simple example it's 4 extra characters to describe what is highlighted, so I'd argue either way is fine and wouldn't kill performance or take up much space.
However, I compared four files and have them in order of file size (note, the first three take the same amount of space on disk):
- my base file: 453,739 bytes
- my base file with columns highlighted: 453,853 bytes (F:F in tab 1 and H:H in tab 2)
- my base file with two specific ranges highlighted: 453,862 bytes (F1:F1325 in tab 1 and H1:H1081 in tab 2)
- my base file with created tables, removed formatting, highlighted table columns: 456,809 bytes (column F in table in tab 1 and column H in table in tab 2)
No noticeable difference from the base file, but full column technically no unnecessary data. In fact your suggestion adds size to the file, but the table feature is useful for other things.
Edit: slightly more readable.
Thanks for testing and sharing 🙏
ctrl d - that was a new one for me, made this whole time watching worth it
the center across selection is a bonus as well
Pleased to hear that, Mike :-)
Thanks Mynda - as unusal my knowledge of Excel has grown after watching this video
I like the offset trick with the Sum formula and I need to learn a lot more about Power Query.
Also, had a recent issue with external links but after modifying all formulas to use the current workbook, I still encountered this message when opening the workbook.
After persevering with the issue for a few days, I opened the Named Manager to modify a reference and was relieved to find a number of external references hiding in there.
Great to hear, Mark! Yes, those pesky external links can hide in all sorts of places including data validation and conditional formatting!
I was worried this was a real clickbaity title, but the video absolutely delivered. Great tips, thanks!
Great to hear!
Well done. Clearly explained without being super long and wordy.
Thanks for your kind words!
100% Mynda!
Absolutely!
I sometimes wonder what life would be like if everyone followed these best practices.
However, in some twisted way, I'm sure that it would mean that the 'Excel gurus' of the world would be less valued.
Great content as always! 😊
😊 the Excel help forums would certainly be quieter.
How is it possible that after decades of intensive advanced Excell usage, you still are able to learn me stuff that is really useful? Like the center across selection! How much more did I miss?! Great video, thanks.
Glad it was helpful 😊
Oh as an excel user who is reasonably good as far as I thought I'm 1 minute into this video and you've already shown me the shortcut for format cells. Now I shall watch the rest. Edit: Many shortcuts very good thank you.
Glad it was worth your time!
I was happy to hear "merge and center cells" being mentioned first, because IT IS MOST DEFINITELY THE WORST! Whenever I receive a s/sheet to analyze or debug, it's the first thing I try to get rid of, and replace it with "Center across selection" if it is still need to preserve presentation.
Great to hear 🙏
Your videos are really helpful & easy to understand, thanks Mynda! I just decided to watch all videos in this channel starting from the oldest one
Wow, thanks so much!
3:24 I agree! The cells' values should *be* dates (not merely be strings formatted like dates). That way, you can sort into chronological order. And correct their format from that misbegotten MMM D, YYYY you show there, to a user-friendlier D MMM YYYY, or a computer-friendlier YYYY-MM-DD. And add or remove the day of the week merely by reformatting (you don't have to hack each string separately). And people can localise the names of days of the week and months to their own language.
Yep, 100% agree, dates should never be entered as text.
I so wish that the YYYY-MM-DD format were the default! Not just for dates in spreadsheets but for file names as well. Anyway…preaching to the choir!
You can use the cell formatting in a formula with a VBA function
Wow, the "center across selection", the first tip, already so useful and makes a ton of sense! I haven't gone to tip No.2 yet. Thanks and great video.
Great to hear!
Thank so much for “Center across selection”. It is so helpful!
Great to hear!
Item 4 is smart. Use powerr query to reference other workbooks. But downside is my colleagues need to remember to refresh data, and changes in the other file will not automatically show in the main file. So pros and cons to both approaches.
Yes, there is a learning curve for this improved approach.
I was relieved that I already had all of these on my radar (especially #7), BUT that doesn't mean I didn't learn anything. ';;;' was a new one for me. Thanks for the great video!
Glad it was worth your time :-)
Oh my goodness! The first tip was so amazing. I never heard of center across the selection.
Glad you liked it!!
Good advice. The thing about range formatting was a bit simplified, though. Excel does not generally save formatting for an entire row on a cell by cell basis but of course excessive formatting and copying and pasting and cutting and inserting can cause problems.
But one thing I feel is missing is data tables stored as range with autofilter where people add new columns without updating the autofilter. Result is a partial filter that will make a total mess of those added columns next time someone does a sort with the autofilters.
Thanks for sharing 🙏
oh, yes, had this problem. several weeks of important data all jumbled around... :s
Auto filter is the devil... Especially when using macros on the data, the amount of time spent on making code that counteract all possible user induced errors take up 90% of the time. The errors aren't always obvious
Good video. The Center Across Selection really should be an icon on the ribbon, as I'm sure you'll agree. That has seemed like a no-brainer improvement for ever. Maybe now that they've tackled unhiding multiple sheets they can address that!
I was surprised to hear what you said about applying formatting to a whole column. Agreed that using a table is the best solution, but absent that, the file size is actually LARGER when you format only some of the cells rather than the whole column.
Cheers, Colin! Agree, it seems whole column formats are not as evil as I had been lead to believe!
No! Center across selection? SO MUCH LIFE WASTED!! MY YOUTH!!!!
For me, using OFFSET in a SUM function is complicated if I don’t wish to create table data. I use a helper column (generally to the left) with numbers in each cell. The numbers may be sequential (1,2,3,…) or a preferred sort order that overrides/augments the data itself. When I add a row of data that will be at the bottom (or top) of the SUM’ed range, I insert the row within the existing range and enter a number in the helper column’s cell that will reflect where the new data should fall after a re-sort. This method also automatically copies the formatting of the range above the newly inserted row.
Interesting technique 👍
The only macro in my personal work book does Centre across Selection, for which I have a button on my very bloated QUAT (I do miss context sensitive toolbars).
Thanks for a very useful video
That's a great idea!
As for the selection around merge and center cells, ctrl-select allows selection of discontinuous ranges.
Yes, but it's super annoying when you selecting a load of cells in a column and all of a sudden you've selected multiple columns because one of the cells is merged. Then you have to start all over again with CTRL.
Excel'lent (get it?) tutorial and presentation. I really liked the explanation about putting too much data in a single cell; I've spent a lot of time showing people the benefit (as you did) with keeping the data separated so that it can be better worked with. Keep up the good work.
Oh, also, thank you for reminding me about the "center across selection" feature. I had completely forgotten about that one!!! :)
So glad you enjoyed it, David!
Retired now, and happily so. Offset() is a nice addition that really would have been useful long ago (in the '90s). Excel came with apparently handy tool kits that were usually useful. I have to admit though, that it was Excel that drove me away from spreadsheet use almost completely. We used statistics extensively for archaeological data. We discovered that Excel at the time would not calculate a variance properly with certain data sets, and not large ones either. Evidently certain combinations of numbers caused problems. These days Excel handles that data properly, but back then the production of a negative variance lead to the adoption of data base management systems and statistical packages. I still am asked to analyze dreadfully formatted excel files similar to the "one year per column" example used in the video. I have come to save the data, usually saved in flat, single table files, as text files, which are imported into R and reshaped into a proper format that can be easily normalized, query and analyzed statistically.
Great to hear you're still making use of Excel. I recommend you check out Power Query for cleaning and reshaping your data as it's built into Excel: www.myonlinetraininghub.com/introduction-to-power-query
@@MyOnlineTrainingHub I was interested in that when you mentioned and looked up the add on for Office 10. It would have been useful to know ten or more years ago. The teams I worked with frequently had conversations about why certain tools were so very hard to locate.
There's nothing quite as slow and boring as reshaping a spreadsheet by hand. What I concluded was that many amateurs (in assembling data) tended to structure their sheets with what amounted to built in query results. They then would become frustrated when they wanted some other kind of result and could not figure out why "you can't there from here."
I've had problems with commercial statistical software as well, when a simple tool like Fisher's exact test produced erroneous results. Some companies would thank you for notifying them that there was a problem, others would go into denial, and not correct the problem until their competitors did.
"Center across selected" looks great, though I am not sure how much I will use it instead of "merge and center". Why?
Usually I use it at the table head, when when it also help with formatting of cells next and not interfere with any functions. And as the table head is 2-3 rows high (sometimes column group names; column names, units), then "format as a table" do not work properly in many cases and simple hand sorting as "select all, standard lines everywhere + wide lines around" and afterwards doing whole head with "all wide lines" or just widening sections you need to differentiate in groups.
Conditional formating is great, too.
Love your ;;; input, love it and will save this vid for the future. And, btw, nice shirt :)
Cheers, Jan!
I agree with all your tips except to use the offset function. There are two big problems with Offset; 1, it makes it hard to “audit” the formula; whereas with sum you can easily audit which cells are being summed by pressing F2 when selecting the F2, you can’t do that with OFFSET. 2. Using the offset function many times within large excel sheets (ie when modelling) significantly impacts performance of the sheet.
Avoid OFFSET when you can
I agree to avoid over-use of OFFSET, but in this context it's unlikely it would be over-used enough to create performance issues.
I posted an alternative to OFFSET.
One of the best videos I’ve watched on excel in a very long time!!
Thanks so much, Simon 🙏
you had me at "centre across selection" - brilliant!
:-) great to hear, Martin!
I did an entire statistics course in Excel and I saved a lot of time by having some of the ideas you showed - now I have some more 😁
So pleased to hear that, Kevin!
I have used the offset function before. I like your simple example...
Cheers, Brad 😊
That OFFSET trick is very cool! Thanks.
Glad you like it!
Great tip on Center Across Selection instead of merge and center. Hopefully MS will replace that merge and center shortcut in the ribbon with the Center Across Selection instead or at least make it available to add to the ribbon.
I hope so too, but I'm not holding my breath as we MVPs have been asking for this for years!
Do a macro and add it to your QAT - Or to your custom ribbon tab if you have created one.
How did I miss this video, plenty of good stuff, Thanks Mynda
Glad you enjoyed it!
Never knew about “center across selection” - these tips are brilliant!
Glad you like them!
I've been using Excel since 1995 and didn't know any of those! You got my like on the first tip :D
Glad you liked it 😊
Thank you so much for your tutorials. I have found them very useful.
Glad to hear that 🙏
This was pretty cool, thanks for sharing it with your calm and pleasant voice. :-)
My pleasure 😊
Small Tips but very key, thanks very much for sharing.
My pleasure, Victor!
Great refresher on some of these important nuances and even some great new tips as well. Thanks!
Glad you enjoyed it!
Hello, Mynda -- the comment about formatting whole rows and whole columns was contrary to what I had learned years ago: that whole row or whole column formatting was a row or column property, and not cell properties. So I just ran a small test. An empty xlsx workbook is 9,569 bytes on my machine. A workbook with Sheet1!1:1 and Sheet1!A:A filled red (1,064,959 cells) is 10,081 bytes. A third workbook with Sheet1!A1:Z1000 filled red (26,000 cells) is 74,122 bytes. In other words, a workbook with 3% of the cells formatted is 7.35 times the size. I believe it is still true that it is more efficient to format whole rows or columns when available.
Hi Michael, that's interesting. Thanks for sharing.
Thanks, I use power query a lot to read files stored in folders. If the sheet names are different, it will not load if your sample file is Source{[Item="Sheet1",Kind="Sheet"]}[Data]. Instead if you have Source{0}[Data] it will read in the first sheet, no matter what it is called. Then you don't have to go around writing macros to change sheet names in folders!
Great tip! Thanks for sharing 😊
0:48 there is something you could do. You can use modifiers to make multiple selections
Modifiers? You can use CTRL to select non contiguous ranges, but that doesn’t fix the sorting and filtering problems that also come with merge & center.
@@MyOnlineTrainingHub Yes, your solution is obviously the right way to do it for many reasons.
But you made it sound like its impossible to select beyond the first merged cell, which it is not.
Been using excel for a long time, but in a relatively simplistic way. I have long known there was way more functionality than I knew how to use, so it is great to see some of those demonstrated with straightforward explanations. And, at least half of this video is of features I will be able to use, hopefully more effectively than my current workarounds.
Subscribed, liked, and following.
Great to hear, Al 😊
Years and years to find the center across selection. Thank you
Better late than never 😊
I love your video's so informative and to the point. As always great job and thank you so much for these videos.
So pleased to hear that 🙏
Calendar data as text you can't avoid when you need historical dates prior to the year 1900. That's a bug in the handling of calendar data not fixed by MS since version 1 and all other spreadsheet program mimics it due to compatibility.
There are calendar and time formats out there that can handle other calendars than the gregorian too, and that support different time zones within a single spreadsheet, but MS refuses to use them for Excel.
True. Thankfully not many people are working with data pre 1900.
As someone who lives in Excel and often receives files from others, it brings a smile to my face that very first rule is to avoid merging cells 👍
Glad we're on the same page 😊
This is very useful. Thanks for posting. Keep up the good work
Glad it was helpful!
Dates as text is a common problem with data from my experience especially when importing from multiple sources in multiple formats
I like to use Power Query to import my data so I can automate cleaning it up as it comes in: www.myonlinetraininghub.com/introduction-to-power-query
What a fantastic tip video! Just to learn what you shared in tip #1 alone was worth the watch (but the rest was really enlightening as well.) Thanks for all that info!
Glad you enjoyed it!
Awesome excel navigation. Will use them for work. Thank you
Great to hear!
Thanks for tip with offset function. 😊
My pleasure 😊
Some excellent good practice tips here, but I have to quibble with the use of OFFSET, which while an extremely useful formula, is a bit of a sledgehammer to crack a nut for SUM formulae. I've been using spreadsheets for nearly 40 years, and for all that time simple spreadsheet good practice is to keep a spacer row or column just before the SUM and sum to that. You can reduce the row/column height/width of the spacer to make it look neat. Also, while you are at it, you may as well do similar with the start of the SUM range, in this case sum to text column heading. Inserting a spacer row/column is a simple tip you can give a complete beginner from day one.
I agree, keeping a spacer row is perfect if you're the only one entering data in the spreadsheet and you know to leave a row, but when you're sharing the file with others who are less disciplined, the OFFSET function is a more robust option.
@@MyOnlineTrainingHub If you reduce the height of the spacer so it's thin but visible and put a top border on the SUM cell it makes it harder for anyone to mess it up. But yes, I've had colleagues who can mess almost anything up 😄