10 Hidden Features in Microsoft Excel (You’ll Wish You Knew Sooner)

Поделиться
HTML-код
  • Опубликовано: 22 дек 2024

Комментарии • 295

  • @ThioJoe
    @ThioJoe  Год назад +69

    How many of these did you know about? 👀 And did I miss any really good ones?

    • @NigelMelanisticSmith
      @NigelMelanisticSmith Год назад +15

      I actually took Microsoft's Official Excel Expert certification a while back, and this still taught me some new tricks. Same for the last episode, great series so far.

    • @ArtflPhenix
      @ArtflPhenix Год назад +4

      would you do google sheets?

    • @KryzysX
      @KryzysX Год назад +1

      All

    • @Platinum_XYZ
      @Platinum_XYZ Год назад +6

      you should link the microsoft word video you mentioned in the description, so we can find it quickly

    • @intelcorei7gaming
      @intelcorei7gaming Год назад +2

      never have I used excel in my life

  • @lordelliott42
    @lordelliott42 Год назад +47

    9:12 Here's a tip. You can use the INDIRECT formula as the source of your data validation list. This is really helpful if you make the list of things a table, because when you add anything to the table, it will show up in the data validation dropdown.
    For example, you have a cells that you need to type names of employees in. Create a table that list all their names, and when you hire or fire people, all you have to do is update the table and the data validation list updates automatically. So, if you have a table called "Names" with a column called "Employees", put the formula =INDIRECT("Names[Employees]") as the data validation list source. If the table is running a power query, even better. If the table is running a power query to pull from an pre-existing company database, even better.

    • @stryp
      @stryp Год назад +1

      This is great. A dirtier solution might be to reference a custom Named Range. But you need to highlight more cells than you currently have if you expect your list to grow, so your way is more elegant.

    • @overyourheadtampa
      @overyourheadtampa Год назад

      ​@@stryp yea, the lack of dynamic growth in Data Validation is one downside, hoping it will support named ranges and tables along with dynamic selections in a future release.

    • @LDFFTV
      @LDFFTV Год назад +3

      My only concern about INDIRECT is that it's a volatile formula so, for larger workbooks, it could slow the performance down.

  • @NigelMelanisticSmith
    @NigelMelanisticSmith Год назад +78

    Power Query is a seriously underrated tool in Excel. If you ever have to work with SharePoint, CSVs, JSON, SQL, or lots others, it is the strongest tool to use within the Office ecosystem.
    For JSON specifically, I even prefer Power Query to most text editors, since Power Query makes it easy to turn JSON data from hierarchical to tabular.
    Additionally, once you move from Excel to Power BI and the Power Platform, all your Power Query knowledge transfers. Your Power Queries can even be replicated by others, since Power Query is a front-end for "M" Code.

    • @2bczar4u
      @2bczar4u Год назад +2

      Well! Going to have to learn that one. Thanks. :)

    • @lonestarparrot
      @lonestarparrot Год назад +2

      Anyone know how to read-in and format syslogs with Excel?

  • @dragons_advocate
    @dragons_advocate Год назад +36

    re: 0:26 Apart from the "camera" feature, which is a bit confusing, you can copy cells and paste them as "paste special" - "linked graphic", which does the same and is much quicker.

    • @YouGotPropofol
      @YouGotPropofol Год назад +7

      Yup. I thought the same thing. Probably why they hid the photo option deep in the settings. One thing I don’t like about excel is that it’s got too many menus and a lot of them are either duplicate or identical choices from other menus. They really need to clean up this monstrosity.

    • @Subjagator
      @Subjagator Год назад +2

      @@YouGotPropofol
      I agree, they are way too messy. You can customise the menus in excel if it really bothers you.
      They probably keep duplicate options around because different people use the different ones and if you were to pick just one and remove the rest you would just be asking for complaints. Not everyone who uses excel is actually tech savvy enough to figure stuff out, they were likely shown how to do a specific thing with specific menus and if the menus or options change they will be f...ked. Again, not a problem when it is a regular Joe Soap but a massive problem with it is a C-level exec from a big company that suddenly can't make those pretty graphs for the shareholders.
      Unfortunately there are probably some massive legacy issues around removing a feature entirely. For regular users not a big problem if you remove a feature or replace it with something else or move some things around, but some companies could have vital business processes dependent on their excel workbooks working correctly and giving specific predictable outputs. I imagine they would get quite annoyed when all of their automated systems start spewing out garbage data because excel changed or removed something they used. So Microsoft keeps it there but just hides it from the menus. Legacy systems keep working, new users only see the new option, sort of a decent solution but at the cost of added bloat with every update.

    • @nettlesoup
      @nettlesoup Год назад +1

      ​@@YouGotPropofol I disagree that they should clean up multiple routes of access to features. I've complained on the Excel help forms years ago that the function (and help) wasn't in the place i expected, so I had to search the Web to find where the heck they'd placed it. Much better the way they have it now with different ways for how different people think.

    • @2bczar4u
      @2bczar4u Год назад

      Hmmm, put the camera just on the QA menu. Select the cells to copy, click the camera, then immediately go to your paste area and click. Extremely easy and quick. Unless I am missing something in your comment.

  • @xntumrfo9ivrnwf
    @xntumrfo9ivrnwf Год назад +18

    Circular references / iterative calculation: useful if you have a complicated financial model, e.g. merger model with full 3 statements, etc, etc. However remember to turn it off when adding data tables

    • @andrerenault
      @andrerenault Год назад +8

      Circular references also come in handy in certain self-referential equations in engineering.

    • @overyourheadtampa
      @overyourheadtampa Год назад +3

      Circular references can also be used to do a col/row return based on dynamic criteria. I cant think of a specific use case off top of my head but its another way to cross reference data in other sheets. Say you want to know what row(s) or columns has the same information on other spreadsheets in the same workbook or even partial information. A circular reference can be used to quickly return all row or column numbers that contain the information. With this you can use mid with find/search and also indirect and a lookup formula to return the data. I used this when some of the data had a punctuation or space which would not be found in a duplicate check.

  • @RobBulmahn
    @RobBulmahn Год назад +23

    One that really is a game-changer: ListObject tables (and subsequently, structured references). This is when you use Insert > Table on your data (or Ctrl+T or Ctrl+L). Once you turn your data into a table, you can start using structured references. Instead of needing to remember the precise row/col address of a cell (particularly annoying when you have huge tables), it will instead be addressable by column name in the header (first row). Additionally, tables can be named, so it becomes really simple to do lookups and whatnot across tables, since you can just use everything's name (e.g. "Sales[Unit Cost]"). Also great, it makes it much more difficult to screw up formulas. When you're adding/removing stuff, it's too easy to have that address get off by a row/column, and suddenly everything's messed up. If you use structured references, it automatically assumes you're referring to the same row that you're currently on* (minus aggregate functions like SUM), and also auto-applies this formula down the entire column.

    • @lordelliott42
      @lordelliott42 Год назад +6

      I'm surprised how many people don't know what an Excel _table_ actually is. Structured references are the best!

    • @overyourheadtampa
      @overyourheadtampa Год назад +1

      The only real downside to tables is the performance hit when they get well into 100k rows over dozens of columns.

  • @wooddog007
    @wooddog007 Год назад +1

    Thanks

  • @exoticlol
    @exoticlol Год назад +12

    Excel is a really powerful tool. I have been experimenting with it lately and it offers a lot of niche features. I have found myself attempting to code something complicated and finding out there's an Excel feature which does what I'm looking for.

  • @Roobitz
    @Roobitz Год назад +71

    One trick I learned: when you get that error message that you have numbers stored as text in a cell, you can use "Paste: Multiply" to apply a multiplication of one to all of the cells to instantly convert them all into being stored as numbers instead of text. Just select the value 1 in another cell, copy it, then select the range of cells you want it to apply to and Paste as Multiply. Way faster than fixing each cell one at a time.

    • @lordelliott42
      @lordelliott42 Год назад +6

      Didn't know about that one. Neat! The alternative I've used is selecting the whole column and then going to Data > Text To Columns. Select delimited and click finished. It won't actually do anything except update all the cells of the column as if you went into each one an hit enter.
      Your way is better as long as you can get 1 into the clipboard easily. Text to Columns forces you to do it to a whole column.

    • @FromGambit
      @FromGambit Год назад +5

      Very useful! Also you can copy a blank cell; Paste Special/Add to the target cells, as Excel will interpret a blank cell as 0 in Maths.

    • @overyourheadtampa
      @overyourheadtampa Год назад +2

      Another option is using hot keys for excel. ALT+H+V loads the paste menu, select which option to use by pressing the corresponding letter shown for the option.
      Alt+H+V+v pastes just text without formatting and has the pasted range autoselected, then press Ctrl+1 (not numpad) to open the cell format menu. Select Number and remove the .00 if those are not desired. This way you don't have unnecessary calculations which can take a performance hit when dealing with sheets containing 300,000 rows across 20+ columns with other formulas or filters applied. Probably 75% of my daily excel usage doesn't utilize the mouse, which if did, would take 4 times longer.
      Other hot keys that will help for selecting large ranges
      CTRL+A +A (If in a table, pressing A a third time selects the whole sheet)
      Or try CTRL+SHIFT+DOWN ARROW (also works with up, left and right arrows)
      F2 allows to edit the selected cell even as formula.

    • @GeeThevenin
      @GeeThevenin Год назад

      Is that better than left clicking a range, pick format cel- number, or hitting the format as number button in the ribbon? You can do that for a range or column(s)

    • @lordelliott42
      @lordelliott42 Год назад

      @@GeeThevenin
      Unfortunately, changing the format of the cells won't update the data to change the numbers stored as text into numbers stored as numbers, if that makes sense.

  • @marxmaiale9981
    @marxmaiale9981 Год назад +8

    Indirect is normally matched with ADDRESS, to be able to use a calculation to determine the source of the data to be displayed. It should be used sparingly as it is a 'volatile' function that recalculates on every change (the more it is used the worse the workbook performs similar to the other lookup functions).

    • @ThioJoe
      @ThioJoe  Год назад +3

      Interesting

    • @lordelliott42
      @lordelliott42 Год назад +5

      It takes a *lot* of INDIRECT functions to actually notice lag on a modern computer. Most people won't use enough to notice, but it is good to know it could be an issue. I try to avoid them mainly because volatile functions are a pain to troubleshoot.

  • @Tyranitar66501
    @Tyranitar66501 Год назад +9

    Yes I NEEDED the named ranges for my school science project. Very useful to just name whatever values and reference it instead of loosing track. Also make one for Powerpoint too!

  • @ChrisMelville
    @ChrisMelville Год назад +9

    ThioJoe - I am an advanced VBA developer. But you still taught me a few nifty Excel tricks I did not previously know. Thank you :)

    • @ClickClack_Bam
      @ClickClack_Bam Год назад

      I'm self taught with VBA.
      I love using that language.
      I was able to tie batch files in it.

  • @Sembazuru
    @Sembazuru Год назад +3

    A simple pair of keyboard shortcuts that I seem to constantly use are "+;" and "+:". The control with the semi-colon inserts the current date and the control with the colon (which on US keyboards is actually "++;") inserts the current time. I at times that this was a more universal keyboard shortcut, but it does also work in LibreOffice Calc, and partially works in Google Sheets. In Google Sheets it only seems to work for direct cell entry, not in the cell text entry box. I.E., if you have a cell in Sheets selected the keyboard shortcut works, but if you click into the formula bar for a cell it doesn't work (unlike in Excel and Calc where the shortcut also works in the formula bar).

  • @edutenorio
    @edutenorio Год назад +3

    I think I’m a heavy Excel user and still I didn’t know many of the features presented in this video. Great stuff!

  • @parlor3115
    @parlor3115 Год назад +2

    One underrated feature is recognizing and parsing date values. I can't get enough of this feature ngl.

  • @exoticlol
    @exoticlol Год назад +3

    I was waiting for this one. Thanks for making it! Next is PowerPoint, I'd love to see it.

  • @andrewmoncada2712
    @andrewmoncada2712 Год назад +1

    the first one of these “hidden/advanced” excel features that’s impressed me!

  • @Martin_WDSK
    @Martin_WDSK 8 месяцев назад

    The change for all selected at once really surprised me. Will definitely use it in my job because I always have multiple sheets with similar data that need to have the same changes applied to at once. You have earned a new subscriber, I really like your simple and straightforward way of describing things. Keep it up!

  • @VincentHardwick
    @VincentHardwick Год назад +31

    Just a note of caution - keep any use of the INDIRECT function to an absolute minimum, and avoid it if possible.
    INDIRECT is a 'volatile' function - its output is recalculated every time a change is made anywhere else that causes Excel to recalculate, whether any of its input arguments have changed or not.
    This can cause severe drag on the performance in a workbook if it's not used sparingly and judiciously.
    There is usually an alternative to using INDIRECT involving defining a name in the Name Manager to represent a dynamic range, using COUNTIF or COUNTA in conjunction with INDEX and the range operator (:).

    • @stickinthemud23
      @stickinthemud23 Год назад

      I hate defining names. Makes it impossible to know, within the formula, what I'm referencing. But I hate INDIRECT() even more.

    • @ragnarok7976
      @ragnarok7976 Год назад +1

      ​@@stickinthemud23Found out recently (while working on a sheet I didn't create) that you can define dynamic ranges in the name manager that won't show up on the sheet even if you type their name into the name bar (left of the formula bar).
      That was a nice little run around that wasn't a complete piss off at all 😂

    • @lordelliott42
      @lordelliott42 Год назад

      @@ragnarok7976
      Was that a case of the name being scoped to a different sheet?

  • @Cossieuk
    @Cossieuk Год назад +5

    F12 for File Save As
    CTRL+` will shows any formulas as text rather than the result of the formula, great for finding formulas in large sheets
    If you make a drop down list it can be useful to hide column with the data so it doesnt get deleted accidentally, or if the cells are protected make the text white so it doesnt show up

  • @dragon67849
    @dragon67849 Год назад +2

    Conditional form,atting is also one of my favorites in clarifying the sheet data as well as making it more presentable. And interactive as well. My god it's such a beautiful tool. The dropdown list I also knew but forgot how to so this was a nice reminder. The indirect formula is new to me and imma use the hell out of it in a few sheets

  • @AalimOnlineOfficial
    @AalimOnlineOfficial Год назад +2

    Can we import Amazon data like this? 1:57

  • @Rick_In_Melb
    @Rick_In_Melb Год назад +3

    One handy feature with range names is pressing F3 to get a list that you can then click and entry and insert it. Saves trying to type them and making spelling mistakes.

  • @Peter_Riis_DK
    @Peter_Riis_DK Год назад +1

    5:36 - Circular References
    This example will put the date of today in the cell it's written in, A1, when something is written in the adjacent cell, B1. But only if _Iterative calc._ is enabled. Quite useful.
    =IF(B1"",IF(A1="";NOW();A1);"")

  • @Robertganca
    @Robertganca Год назад +4

    Very useful video! Hopefully you will continue this with PowerPoint next.

  • @jkjosaas
    @jkjosaas Год назад

    I would consider myself an "advanced" user (subjectively) but there were a lot of tips here that i did NOT know about. You're precise, to the point and clear = OUTSTANDING video! Thank you taking the time to make this tutorial.

  • @antemilardovic71
    @antemilardovic71 Год назад +2

    3D references.... nice! Learned something new today! 🙂 Well done!

  • @TiborCES
    @TiborCES Год назад +1

    iterative calculation is great!! thanks Joe

  • @AlexanderCrawford
    @AlexanderCrawford Год назад +2

    Circular references are useful if you are calculating interest in a cash flow model based on the average cash balance. The year end cash is affected by the interest paid. Since the formula uses ½ of the year end cash balance (as it is an average of two numbers), the result will resolve with iterations.

  • @wayneyadams
    @wayneyadams Год назад +3

    There is a very handy hidden formula in Excel that does not show in the list of formulas. It is "Datedif. It is formatted like this.
    =DATEDIF(cell1,cell2,"X")
    cell1 = start date
    cell2 = end date
    X = ""y" Number of full years between dates.
    X = "m" Number of full months between dates.
    X = "d" Number of days between dates.
    X = "ym" gives the number of months left in the final partial year, for instance 6 for 3 years 6 months.
    X = "yd" gives the number of days left in the final partial year, for instance 180 for 3 years, 180 days.
    X = "md" Gives the number of days left in the final partial month
    Here is an example from one of my spreadsheets.
    C6 = 06/01/1960
    F6 = 12/07/1974
    =DATEDIF(C6,F6,"y")&" years, "&DATEDIF(C6,F6,"ym")&" months, "&DATEDIF(C6,F6,"md")& " days"
    Output: 14 years, 6 months, 6 days

  • @MageThief
    @MageThief Год назад +1

    Most of these where new to me and I will use several of them, great video.

  • @jwparkin
    @jwparkin Год назад +2

    Conditional formatting is something I regularly use, but most of the others were new to me. One tip in conditional formatting - if you want to highlight a cell only when data is present, use the formula “greater than” and set value as 0 (zero)

  • @RoboChemist
    @RoboChemist Год назад +2

    Powerpoint next? Also, a video about lesser-known miscrsoft office programs wold be awesome!

  • @christopher8220
    @christopher8220 Год назад +1

    Circular references are used quite a bit in Investment Banking in creating pro-forma finacial statements when the acquiring company has an established line of credit referred to as a 'revolver'.

  • @lorettademaio4334
    @lorettademaio4334 9 месяцев назад

    I didn't know about inserting a tab or adding the range name by typing it into the location box. Thank you for sharing!

  • @Kappe619
    @Kappe619 Год назад +3

    You missed to talk about the power of macros. I found out you can bind them to shapes inside your sheet. Eg you can inserted a rectangle etc, add text "do stuff" and bind it to a macro. Now I can execute it by using this self-made button.
    I din't know you can create your own lists for autocomplete, that's really cool!

    • @marxmaiale9981
      @marxmaiale9981 Год назад +1

      Making a good VBA Program on the back end is far more than a 'Macro', you can interface with the rest of MS Office, Active Directory, Windows, Databases, and much more. I cringe when I have to fix a 'Macro' that is full of relative references that break workbooks such as 'Selected', 'ActiveSheet', or are full of scrolling. When building functionality, don't take shortcuts in the code someone will have to edit it in the future.

    • @ragnarok7976
      @ragnarok7976 Год назад

      You can also create keyboard shortcuts linked to your macros. I made a ctrl+shift+v to paste values as I liked that functionality in Google sheets better than how it was implemented in excel.

    • @ClickClack_Bam
      @ClickClack_Bam Год назад

      ​@@marxmaiale9981You can use a macro to help set up your VBA program.

  • @Freddy_Guy
    @Freddy_Guy Год назад +1

    Perfect time for this vid, since i was just looking for this stuff

  • @michaelrudert3406
    @michaelrudert3406 Год назад +1

    Everytime really useful tips and hints. I like this channel👍

  • @travisos7199
    @travisos7199 10 месяцев назад

    thank you.
    I have been searching for this INDIRECT function for quite some times without knowing it's name, but never find it until now.
    thank you very much

  • @TonyP9279
    @TonyP9279 Год назад +1

    The INDIRECT modifier will come in handy! Thanks for the intel!

  • @power-reconcile
    @power-reconcile 7 месяцев назад

    Excel is getting more powerful features every day, thanks for the video and sharing!

  • @henry23z
    @henry23z Год назад +1

    Nice job Thio! This was very useful

  • @Graham6410
    @Graham6410 Год назад +2

    I knew about a few of the options but it is always a good idea to have a refresher :)

  • @glenw3814
    @glenw3814 Год назад

    "Indirect" function and circular reference features look super useful. These are new to me.

  • @CalebAstle
    @CalebAstle Год назад

    This was awesome! I have a very specific problem that I'm trying to find an answer for, and the 3-D referencing tip, almost got me there! Can't wait to see more!

  • @Stef.Cata051
    @Stef.Cata051 Год назад +8

    I was never, in my life, excited about Excel until I opened a business and now.... excel is like cocaine because I can't get enough

  • @tiagoferreira086
    @tiagoferreira086 Год назад +1

    Some i already knew and used often, but others i never had though in my wildest dreams. I found very interesting the: web data import, indirect formula, dropdown list, named ranges, forecast and sparklines!
    Next "secrets" video about Notepad++ ???

  • @Cromeman82
    @Cromeman82 Год назад +1

    Thank you for this video. You covered a lot of ground with very good stuff. Didn't know about the camera one, I always used formulas to point to cells on other sheets that I need to have visible. :)
    A word of warning on INDIRECT and Conditional Format as these are volatile formulas which might (if your sheet is big and complex enough) slow your calculations down.
    Also a word of warning on changing multiple sheets at once - there is a bug that consistently crashes Excel if you are doing formatting options (like cell styles or conditional formatting).

  • @joaum2009
    @joaum2009 Год назад +1

    This was really useful. Nice video.

  • @marcus_rigonati
    @marcus_rigonati Год назад +2

    It would be nice to have one video like this for Google Sheets and Docs :D

  • @silversurfer787
    @silversurfer787 Год назад +1

    I learnt cool features about Excel from this video,thanks

  • @kannathraymaker
    @kannathraymaker Год назад +1

    Thank you. Excellent information!

  • @0.Andi.0
    @0.Andi.0 Год назад +2

    Hi, can you please make a video also talking about libreoffice?

  • @thebesttechnical3608
    @thebesttechnical3608 28 дней назад

    Excellent documentation with the installation steps .Thanks a lot

  • @SamiSheikhOfficial
    @SamiSheikhOfficial Год назад +1

    Very informative video. Thanks

  • @alphanimal
    @alphanimal Год назад +7

    The most useful thing I ever learned in Excel: You can press F2 to edit cells

    • @RobBulmahn
      @RobBulmahn Год назад +3

      Some more super-useful ones:
      Ctrl+Enter to complete input, but not jump down a row.
      Ctrl+D to copy down what is in the cell above.
      Ctrl+R to copy right what is in the cell to the left.

    • @alphanimal
      @alphanimal Год назад +3

      @@RobBulmahn Didn't know about those! Thank you!

    • @ragnarok7976
      @ragnarok7976 Год назад

      You can use F2 to edit in windows explorer too. Really great for changing file names. I think you can also press tab in edit mode and it will let you edit the next file name in the list. Can save a lot of time over the more finicky alternatives.

    • @overyourheadtampa
      @overyourheadtampa Год назад

      F2 works in a lot of applications to edit the current selection. Even non Microsoft apps use it sometimes.

  • @Fifty1stState.
    @Fifty1stState. Год назад

    Excellent, there's always something really useful in the videos - The INDIRECT one's going to be useful.

  • @VSP4591
    @VSP4591 Год назад +1

    Thank you. Very useful tips

  • @FromGambit
    @FromGambit Год назад +2

    3D references are useful for making (say) a monthly summary from four weekly sheets. Just copy one 3D reference around the summary sheet. Just be sure W1,W2,W3 and W4 are adjacent tabs though...

  • @geniustic1541
    @geniustic1541 Год назад +1

    This video was indeed very helpful!

  • @barttitulaerexcelbart9400
    @barttitulaerexcelbart9400 10 месяцев назад

    Thanks Thio, Circular Refference is used in Finance (company validation). One other tip: select a part of a formula and press F9. for example select SEQUENCe(3;6) and then F9, to see what is happening " under the hood" .

  • @MbappeForMVP
    @MbappeForMVP Год назад +1

    Awesome video great job 👍

  • @claudiafischering901
    @claudiafischering901 9 месяцев назад

    That is cool tipps. I like the camera and watchdog feature.

  • @5H4D0WOfficial
    @5H4D0WOfficial Год назад +2

    I don't use Excel much, still saving this for using in the future

  • @SuperLovedoctor69
    @SuperLovedoctor69 Год назад +1

    Good as always congrats

  • @Phlegethon
    @Phlegethon Год назад +2

    actually circular references is incredibly helpful in a dcf analysis - and that formula that uses tab columns is useless it's too prone to user error of moving another tab inbetween

  • @ewald59
    @ewald59 Год назад +1

    Which version(s) of Excel do these features apply to?

    • @ThioJoe
      @ThioJoe  Год назад +1

      I used the latest version

  • @RobotMowerTricks
    @RobotMowerTricks Год назад +1

    Naming a cell is HUGE if you're adding lines to a sheet. That way your formula can always find a cell even after it's row and column have changed because you added rows and columns later.

  • @hughkelly9073
    @hughkelly9073 Год назад

    Very good! Thanks for that marvellous information.

  • @martinastos
    @martinastos Год назад

    A further tier useful feature in conditional formatting I learnt of recently and really love: there is a formula you can use to format alternate rows. Very useful if you have a multi user file that keeps receiving pasted data but needs improved row visualisation. (Can't remember formula but it involves MOD() )

  • @StayCoolKeto
    @StayCoolKeto Год назад +2

    *Some people spend 60 dollar son strawberries. We get imported ones from japan here and they can cost more than that. nice video, BTW I dont even use Excel now, but I just like your channel lol* 💪👍

  • @wayneyadams
    @wayneyadams Год назад

    3:00 You can accomplish the same task by doing a data sort and that will remove all the blank lines.

  • @JustPyroYT
    @JustPyroYT Год назад +1

    Do the advanced file info and save intervall settings also exist in word and powerpoint?

  • @marksnk
    @marksnk Год назад +1

    good tips.VERY GOOD.

  • @FerdinandCesarano
    @FerdinandCesarano Год назад +2

    This is great information, as usual. Thank you.
    I was hoping that you'd cover something that has frustrated me in Excel: the absence of the "Font" group, as exists in Word.
    In Word, you can go to the Font group and set the text in a cell or a line or a section (or in the whole document, if you want) to always be in caps. I have not found a way to do this in Excel.
    This is annoying because there are a few douments that multiple people can edit, but I have to maintain. The text is meant to be in all caps, but some people insist on writing in lower case. So I have to spot each instance of that and change it. If only I could set the entire document to all caps.

    • @lordelliott42
      @lordelliott42 Год назад

      If the spreadsheet is one that people are editing with the web version of Office (through their browser), that might not work, but if you have access to the actual file, you could make a macro that you run yourself to do the same thing. Sorry for the reply spam. I wanted to keep the code bits separated. It's weird that Excel doesn't just have all caps and a font option, but that's Microsoft for you.

    • @FerdinandCesarano
      @FerdinandCesarano Год назад

      @@lordelliott42 - Other people are editing the file in SharePoint.
      Thanks for the suggestion about the macro, but I don't think I have the chops to do what you suggest.
      Anyway, I'm glad that I am not the only one who finds Excel's lack of a Font group strange.

    • @overyourheadtampa
      @overyourheadtampa Год назад

      I haven't checked but I think there is an all caps checkbox in the font dialog box. CTRL +1 to open cell format and click the Font tab to see if it's there.

    • @FerdinandCesarano
      @FerdinandCesarano Год назад +1

      @@overyourheadtampa - Thanks for the suggestion. But in that group there are the options only for Strikethrough, Superscript, and Subscript. Lacking are the other options that appear in Word, the most important one being All Caps.

    • @overyourheadtampa
      @overyourheadtampa Год назад +2

      @@FerdinandCesarano you can add another column with =upper(a1) as a formula where a1 can be replaced with the cell that people enter the data into. Any cells that need to reference the upper case data can instead reference the formula cell.
      You can also limit the character input using data validation.
      Selecting the input cell, e.g. A1
      Click the Data ribbon (Alt+A) and select Data Validation
      Select Custom from the drop-down
      Type this in the formula source =Exact(A1,Upper(A1))
      On the Error tab, enter the message you want to appear when the entry is not all caps. Click ok.
      Test it out.
      This will not allow the user to enter lowercase characters into the cell otherwise it would produce an error, although copy and paste may be able to bypass it.

  • @zizoukurosaki7100
    @zizoukurosaki7100 Год назад +1

    Definitely amazing ❤

  • @char7680
    @char7680 Год назад

    That is amazingly awesome! I wish I would have watched this like... 10 years ago, but hey, it's better late than never!

  • @illawarriorhill70
    @illawarriorhill70 Год назад

    I have done some brilliant things with "Indirect"... it is GREAT!

  • @pharmdiddy5120
    @pharmdiddy5120 Год назад +1

    You might have given me back several hours of my life just now 😊😊

  • @Ptaaruonn
    @Ptaaruonn Год назад +1

    This is awesome, thank you!!!

  • @davidadams421
    @davidadams421 Год назад +1

    Outstanding!

  • @behrad9712
    @behrad9712 Год назад +1

    Thank you!🙏

  • @africa_everyday
    @africa_everyday Год назад +1

    I love teaching my students Excel a lot

  • @t0mn8r35
    @t0mn8r35 Год назад

    Very useful. Thank you!

  • @wolphin732
    @wolphin732 Год назад

    conditional formatting... missed one key item.
    if doing a formula, the cells go off of the top right, so if selecting cells in column C, the formula would need to reference c1, and the formula would be changing when it goes to c2, the details would be c2. if you entered d1, it would always be referencing the column that far over unless you use the $ to lock the column or row.

  •  Год назад

    Thanks! Advanced properties were a thing i thought was removed from the new version

  • @kodekube
    @kodekube Год назад

    What version of Excel are you using?

  • @mytciopinfo
    @mytciopinfo 6 месяцев назад

    Sparklines was new. Thanks!

  • @phizc
    @phizc Год назад

    Is there a way to have a real dark mode? Make the cell backgrounds dark gray, while still having the grid lines? I know it somewhat can be done manually with cell fill/text color/manual borders, but that breaks if you change the cell style, such as value based color range etc.

    • @overyourheadtampa
      @overyourheadtampa Год назад

      There are a few themes for Ms office. One is dark mode and there are gray versions as well. The cells are still white but there may be a setting to adjust the default cell color

    • @c17nav
      @c17nav Год назад +1

      You can modify the Normal style format. Change the cell Fill and Borders and the Font color so it contrasts appropriately.

  • @JoskMclaren
    @JoskMclaren Год назад

    I have to say you explain this better than most actual courses.
    ps I used to be an excel expert butthen they introduced the ribbon and ~I was cast back into the abyss.

  • @dlanodsknib
    @dlanodsknib Год назад

    Will you be covering "Publisher" as well? Thanks.

  • @JustPyroYT
    @JustPyroYT Год назад +1

    Cool and helpful video! :D

  • @szacsesz
    @szacsesz Год назад +1

    Nice ones. Still for me the biggest surprise was when I realized that you can actually set up shortcuts to commands with the "alt" + number keys.

  • @RockyAllenLane
    @RockyAllenLane Год назад

    When I follow your instructions for adding Camera, then click on the new tab, the Group shows up but the Camera icon is grayed out. Help please!

  • @adjekotazita952
    @adjekotazita952 Год назад

    I've been using excel for quite some time now and I just realised I could put a chart in a single column 😂 from this video. Thanks

  • @tzisorey
    @tzisorey Год назад

    Speaking of autosave:
    Is it just me, or does it only let you autosave if the document is on sharepoint/onedrive nowadays? Any way to get it to autosave documents that are local (eg. Thumb-drive, etc) - I looked into it a while ago, but didn't find anything.

  • @agentm10
    @agentm10 Год назад

    Thw word one was more interesting, this video contains very common knowledge for any intermediate excel user

  • @philosuit
    @philosuit Год назад +2

    What minimum version of Excel for all these features to have

    • @ThioJoe
      @ThioJoe  Год назад +1

      I think most of these features have been around for a while

    • @NigelMelanisticSmith
      @NigelMelanisticSmith Год назад +1

      I know for certain these all work on 2016

    • @chromerims
      @chromerims Год назад

      ​@@ThioJoe I haven't tried web query on Excel 2013, but the rest of these tips and tricks are on 2013. 90% of these were on 2005 edition. Kindest regards, friends.

  • @navneeths459
    @navneeths459 Год назад

    Would you be able to put these types of videos as a YT playlist, so that we can find and share it easily?

  • @keylanoslokj1806
    @keylanoslokj1806 Год назад

    Are any of those formulas NOT in the 2016 version?