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.

would you do google sheets?

All

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

never have I used excel in my life

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

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

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.

​@@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.

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

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.

@@DeputyBurbank 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.

​@@DeputyBurbank 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.

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.

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

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.

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

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

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.

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.

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.

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)

@@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.

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

Interesting

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.

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.

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

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.

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

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.

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.

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

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

​@@stickoutofthemudFound 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 😂

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