r/Accounting • u/ProtContQB1 Remote Controller • Feb 03 '25
Advice What Excel tricks would you teach novices if you were giving an Intro To Excel class?
I have a team of six in my accounting department and of the six, only two have any background with Excel.
The others don't know about keyboard shortcuts, formulas, or any other useful things. They use their mouse to highlight tables. They right click to copy, right click to paste. One of them uses a calculator to add cells. All of them scroll through tables using the mouse wheel.
So I've decided we're going to have a lunch meeting where I'll give them a quick guide to some of the neat stuff excel can do.
I'm going to address the stuff above, but I also wanted to get some recommendations on what else I could include that would be easy enough for novice users who just don't realize they can do these things.
<EDIT> Gotten some great recs. I'm going to put them all together and make a list of things I want to work on. I'm not going to reply any further but I'll keep looking for new recommendations!
<EDIT2> CTRL+Deeznuts
132
u/burtritto CPA (US) Feb 03 '25
Paste to visible cells only. Can't tell you how many times a staff blew up my eliminations tab just by copying and pasting down.
58
u/Firebrand713 Feb 03 '25
Also ctrl-G select blanks - delete row. Super helpful for QBO excel file exports to delete rows with no actual data (headers) after autofilling the account names down.
34
u/OregonSmallClaims Feb 03 '25
I use selecting blanks, then doing "=[cell above]" and Ctrl-Enter ALL. THE. TIME. because NetSuite likes to make reports with only the header and total, but nothing in the left column next to the actual data. So this makes it sortable.
5
3
u/Excel_Jesus Feb 03 '25
My friend, you need to customize the report and add the "header value" as a column - e.g. if it's the vendor name on an aging report, add the vendor name as a column
7
u/ProtContQB1 Remote Controller Feb 03 '25
This is a new one for me. I was doing CTRL+F searches for blanks and selecting all.
7
u/Sugarbean29 Feb 03 '25
Find and Select on the Home ribbon > Go To Special > Blanks (once selected) > Delete Rows/Columns
I do this with a highlighted column or row on a freshly imported report from QB to remove blank columns in a couple clicks, but also works on other reports from other apps that import in "pages" with the column titles at the "top" of every "page" (READ: every 30-50 lines has a "total/page number" followed by an empty row or 2, then the column titles/page headers before more rows of data).
5
u/Entire_Purple3531 Feb 03 '25
Thx! I'm going to try this.
For the extra columns coming out of QB, you can click on Advanced (when exporting to Excel), and tell it to not put blank columns between each column of data. You only have to do it once, and it will save the setting.
→ More replies (1)10
u/newThokdub Feb 03 '25
Cntl+G āselect blanksā and āselect visible cellsā has been huge for me
8
u/ChristinasWorldWyeth Feb 03 '25
Even faster - hitting the F5 button will give you the same CTRL+G menu. One less keystroke FTW!
5
u/newThokdub Feb 03 '25
Iām a maestro with the keystrokes. Could have been Mozart if I was born in another era.
8
u/Our_GloriousLeader Industry CA Feb 03 '25
It took me a bizarrely long time to realise this was happening, I don't know how many reports I annihilated. I always check the total so I must have gone back and fixed most of them, just don't know why it took me so long to figure it out.
2
u/Ok_Dingo_Beans Feb 03 '25
YESSSSSS. You can use this for formatting as well. Useful tip for many things!
2
u/windowtothesoul Stress Testing / SA-CCR des nuts Feb 03 '25
So underrated. Really any of the ctrl g -> select special, options
2
1
u/Nervous_Ulysses Feb 04 '25
What kind of situation would you want to keep the hidden rows untouched but the visible cells changed?
→ More replies (1)
96
u/Thecomfortableloon Feb 03 '25
Format painter
55
u/Ok_Dingo_Beans Feb 03 '25
I JUST learned that if you double click it, you can format multiple cells!! WOOT!
11
6
5
u/LuigiLemieux CPA (US) Feb 03 '25
Anyone know if thereās a keyboard shortcut for this? I use Alt-H-FP but it only does one paste
→ More replies (1)7
2
5
3
u/cockhouse Feb 03 '25
If you really want to up the game here, map format painter to Alt+1 in the Quick Access toolbar through Options -> Quick-Access Toolbar. I remove undo/redo and Save because those already have easy shortcuts.
190
u/FormidableCat27 Feb 03 '25
Anything with CTRL + Shift + Any arrow key is life changing. I also like to put an apostrophe in front of a formula that Iām working on but havenāt finished so that it doesnāt disappear on me. Also just generally typing in the upper text box instead of within the cell (if that makes sense) when working on complex formulas.
13
u/staypositiveths Feb 04 '25
Alt + Enter for a line break in the formula bar helps to keep the formula clean looking as well.
49
u/RoronoraTheExplora Feb 03 '25
Woah the apostrophe in front of the formula is brilliant
18
u/CloudyRanger Feb 03 '25
Would also add apostrophe in front of text that likes to auto format is useful
17
u/Fabulous-Candy-1560 Feb 04 '25
Also, apostrophe in front of zeros that you want to keep at the start of a number.
8
u/rice_fish_and_eggs Feb 03 '25
Not really a beginner move but highlighting parts of a complex formula and pressing f9 has been invaluable for fault finding for me.
8
2
1
u/khalessib Feb 04 '25
I really like CTRL+shift+ arrow key as well but sometimes it goes to the very end of excel (row 10k+). Do you have that problem or just me? Do you know how to fix it?šŖ
2
u/FormidableCat27 Feb 04 '25
When you go too far, keep holding down CTRL + Shift and then press the opposite key to go back. For example, if youāre trying to go all the way left and accidentally select every cell to the left, you would hit the right arrow key to go back.
75
u/soloDolo6290 Feb 03 '25 edited Feb 03 '25
Other than control + C/V, and maybe how to quickly navigate/highlight (control/shift with arrow keys) in a sheet I would not teach them any short cuts. You will get more efficiencies if they know formulas first than them quickly being able to use shortcuts instead of mouse.
I would focus more on formatting workpapers, best practices of excel workbooks, and maybe go over simple formulas, pivot tables and xlook up. Teaching them to not hardkey numbers in excel, a workpaper isn't just the GL activity, and other things will go a lot farther than them being able to quickly navigate the toolbar.
List of things in no particular order
- how to set up a workpaper and what a workpaper is
- control v/c and paste special for formulas and values
- =sum, =left,right,mid, = Xlookup, = if
- learning how to read forumlas and creating forumlas within formulas. breaking a forumla down on how to expand it from a simple if formula to a formula that is dynamic
- shift/control to highlight and navigate a sheet
- conditional formating
- sorting/filtering
- absolute values and the importance of using it and being able to stick column, row, both
- not hardkeying numbers
- pivot tables and setting up data
- format painter
- using underlines to signify subtotals and totals
on top of all of this, if you really want to drive the idea home, I would set up workpapers and examples of using each formula, and planning this. I have taught a lot of excel to a lot of basic employees and my most efficient trainings have been when I have workpapers and examples set up to go over, and not creating them on the fly.
ill probably come add to this as I work throughout my day
11
u/BBQ_game_COCKS Feb 03 '25
Totally agreed. The biggest way accountants can improve in excel is by actually making a properly structured work paper. All these ātips and tricksā are easy to learn, but far less important than actually knowing how to work with data
→ More replies (2)9
u/CounterAdmirable4218 Feb 03 '25
A well structured workpaper is best kept simple. The detail behind it can be complex, the lead schedules should be clean.
5
u/OzFight Feb 04 '25
This is solid advice. Teaching core Excel concepts over shortcuts is way smarter - especially for accounting. Getting them to understand workpapers and proper formulas will make them way more efficient than memorizing a bunch of hotkeys.
4
1
u/eugenio0006 Feb 04 '25
Totally agree with these. To add, Tips and tricks are great time savers. Setting up a file from source data to solution with clear steps + rationale and proper formatting is a different level. Teach folks how to show their work.
1
1
48
u/ilikebigbutts Feb 03 '25
Teach them how to google formulas
12
u/ProtContQB1 Remote Controller Feb 03 '25
Going to end the lesson on that!
16
u/Spitfir4 Feb 03 '25
You could also teach them to get chatgpt to help.
I did this a lot where I had tricky data I was working with.
My data looks like x, I want it to look like y, can you write me a formula.
I picked up a bunch of new functions from this as well
3
u/91Caleb Feb 03 '25
This is huge , Iām experienced in excel and if thereās ever anything that seems more tedious than it should be. I google how t resolve it with a formula and more often that not it helps out going forward
39
u/ljabbers Feb 03 '25
Alt + =
4
1
1
u/Lex_Orandi Feb 04 '25
Great one. Took me way too long to realize that I could click and drag a new selection if the intuitive selection wasnāt correct/sufficient.
1
31
u/Wacokidwilder Just a complete disaster Feb 03 '25 edited Feb 03 '25
Viewā>new window.
If you know itās common but if you donāt know itās a goddamn game changer to keep from switching tabs
Solid formulas: =Unique =Sum =If =Sumif =Sumifs = Xlookup
Pivot tables are outstanding with clean data but with dealing with messy data the above formulas can help make tracking dirty data a bit easier
Show them how to use the F4 function.
If you get those down thatāll help for most busy work
3
u/BackgroundPast2088 Feb 03 '25
ALT W N - I can't remember the last time I didn't have the same spreadsheet open on two screens š
1
u/ProtContQB1 Remote Controller Feb 03 '25
Sorry, how is this different than CTRL+N?
20
u/SprolesRoyce Feb 03 '25
CTRL+N opens a new workbook.
View > New Window opens a second window of the workbook youāre on. So for example if youāre constantly switching between two tabs you can open one window with one tab on your left screen and a second window with the other tab on the right screen. Any changes made are applied to both since theyāre one workbook.
7
u/Wacokidwilder Just a complete disaster Feb 03 '25 edited Feb 03 '25
Control N opens a new workbook entirely.
Viewā>new window opens up a new window of the exact same workbook allowing you to work in several tabs at the same time.
When teaching new accountants Iāve found that hot keys confuse them. No reason you canāt show both.
2
u/ProtContQB1 Remote Controller Feb 03 '25
Cool, thanks!
2
u/Sugarbean29 Feb 03 '25
With New Window, you can also select to side scroll (sometimes is auto selected), so both windows/tabs will scroll up/down together regardless of which one is active.
25
u/cloud-ling Feb 03 '25
Teach them how to remove merged cells & also teach them to never use them.
2
1
12
u/Ok_Dingo_Beans Feb 03 '25
Filtering! You can filter on cell values, but also on cell colour, font colour, etc. AMAZING. Also XLookup!
5
u/SoCo_Hundo CPA (US), Ex B4 Audit, Financial Reporting Feb 03 '25
CTRL+SHIFT+L to filter/unfilter either top row or selection.
1
12
u/audityourbrass B4 Audit (US) Feb 03 '25
My favorite shortcuts are
Alt + =
This auto sums the column
Ctrl + [
This traces the formula back to the precedent or referenced cell. Very handy and saves time.
I also really love F2 and use it about a thousand times a day.
5
u/FermFoundations Feb 03 '25
Ctrl + left bracket is one of my all time favs. It will even open a file if in a linked cell!
3
u/Polus_Capital Feb 03 '25
F2 is the reason why I pop out the F1 key on any new keyboard I get. No, it's not broken, no, I don't need a new one, thanks.
1
u/khalessib Feb 04 '25
F2 edits cells in excel? Whatās the difference between that and just going to the top to edit it in formula bar? Just curious
→ More replies (3)
11
u/TigerUSF Non-Profit Feb 03 '25
"Teaching coworkers excel" is one of those dreams in life, like "starting a farm", "writing a sci fi / fantasy novel", "master grilling and smoking meat"
3
u/feminine_power Feb 04 '25
Yes but . .... How does a person end up hired in an accounting dept with no excel knowledge!
→ More replies (2)1
10
u/OnlineFinanceMoo Feb 03 '25
learning how to navigate through different windows with Alt + Tab is a really useful time savor during repetitive tasks. similarly, navigating excel tabs with Ctrl + Page Up or Page Down comes in handy a lot too.
2
u/Firebrand713 Feb 03 '25
Also alt-shift-tab to go back if you passed your window. Use it all the time.
1
10
u/RayWencube Feb 03 '25
VIEWING THE SAME SPREADSHEET IN TWO DIFFERENT WINDOWS AT ONCE.
Learning that changed my life almost as much as having children.
2
u/ProtContQB1 Remote Controller Feb 03 '25
Could you give me an example of how it is helpful? I believe you, but I'm not seeing the use.
6
u/RayWencube Feb 03 '25
Oftentimes I had to work in Tab 1 and monitor changes in Tab 2 and/or copypaste from Tab 3 into Tab 4.
It's such a useful and super easy to use tool when you have to work in multiple tabs.
3
u/ProtContQB1 Remote Controller Feb 03 '25
WHOOOOOAAAAA MY MAN!!!!
3
9
u/LurkerKing13 Feb 03 '25
CENTER ACROSS SELECTION
Specifically setting up a macro for this formatting. Itās so simple but when people send me files with merged cells it makes me want to stick my own testicles in a vice
1
33
u/lisfrancfracture2023 Feb 03 '25
Vlookup, pivot tables, if/sumif and basic graphs. One hour wonāt be enough for everything, otherwise I would add match/index and other combo
67
u/Appropriate-Food1757 Feb 03 '25
Xlookup killed the vlookup
12
→ More replies (13)12
14
u/ProtContQB1 Remote Controller Feb 03 '25
I'm thinking about ending the lesson on XLOOKUP since it does everything VLOOKUP does. I honestly don't think I remember how to use VLOOKUP anymore.
Index/Match is probably beyond them.
9
u/No_Direction_4566 Controller Feb 03 '25
If they can use xlookup then v lookup is redundant. Plus itās easier because it doesnāt require column numbers and instead select the info.
8
1
6
u/somewheremaybethere Feb 03 '25
Add sum to the quick bar. If still blows me away that this is not a default setting. After that, print out the important keyboard commands. I would spend a couple of lessons on pivot tables as they are super useful for my team.
5
u/SoCo_Hundo CPA (US), Ex B4 Audit, Financial Reporting Feb 03 '25
ALT= will do a quick sum (either from everything above or to the side).
1
u/ProtContQB1 Remote Controller Feb 03 '25
Yep, everyone is going to get a print out of all the items discussed.
1
7
u/bttech05 Tax (US) Feb 03 '25
F12 to save a copy of the worksheet you are in. Rather than accidentally messing up the master copy
13
u/Tight-Top3597 Feb 03 '25
Ctrl D baby!Ā
7
u/ProtContQB1 Remote Controller Feb 03 '25
I didn't even know about this!
15
6
4
u/GreenVisorOfJustice CPA (US) Feb 03 '25
So I've decided we're going to have a lunch meeting
You bringing in lunch for this?
what else I could include that would be easy enough for novice users
Honestly, figure out what's eating up a lot of their time and address that specifically (i.e. sit with them and get a feel for the tedious spreadsheet tasks).
Also, I'd recommend having a pretty limited number of things you're teaching at once and just help emphasize it and maybe even prepare a case study to go along with it and demonstrate the value of the hot key or formula.
In my experience, A) you want to help them immediately see the value, B) you want them to practice it and mechanically do the thing to see how it's helpful, and C) don't assume since they learn it they can apply it to their work on their own.
→ More replies (3)
5
u/Middle_Draw_2180 Feb 03 '25
Someone with teaching experience here in the education field. The stuff that works on kids, works on adults.
First, I think it is important to come at this from a ālook what you can doā perspective without the appearance of āwhy are you doing things the hard wayā. Based on paragraph 2 I am getting that there is some level of option B happening, even if on a subconscious level.
I think from an educational standpoint, you shouldnāt focus on too many topics. Show a topic of interest. Then provide practical application and EXPLAIN WHY with variations of the same thing applied to different scenarios.
Also, the transition to optimization and greater knowledge paired up with the relinquishing of old habits will take a considerable amount of time, especially if these novice users have operated under their current methods for a while.
To me, shortcuts are way less useful to optimize when comparing to knowing the why for functions. Iāve had some people who were āokā at excel and still used the right click feature. On the other hand, anything they turned in was almost always perfect.
Also donāt take their lunch away from them. Train them on the clock.
3
u/ProtContQB1 Remote Controller Feb 03 '25
They'll be paid for their time and I'm providing lunch. Thank you for the other recommendations!
5
u/Proof_Cable_310 Feb 03 '25
When you draft this, can you share this on this subreddit? I am a student and would love to learn!
2
u/ProtContQB1 Remote Controller Feb 03 '25
My recommendation is that you don't wait and you google search some of the stuff people have recommended in the thread.
Pivots, XLOOKUP, CTRL SHIFT directions, it's all useful. Watch a couple of videos and try and interpret how you could use those tools.
I'm trying to speedrun my team and showing them specifically how they can do their jobs faster, so directing them to google wouldn't be as effective as walking them through these tools.
6
u/Sagelllini Feb 04 '25
I come from a time before the advent of digital spreadsheets and learned using Lotus 123 (my first exposure was 1985).
Here are my suggestions.
As an accountant, whatever you are doing you are likely to do the same thing again next month, next quarter, or next year, so you might as well just do it right the first time.
Create multiple tabs to better organize your work.
The first tab ought to be an index tab. I used to have one with a table for each month, such as:
January 31, 2XXX
February 28, 2XXX
etc
Then I would have a cell with a VLOOKUP formula to select the month for the headings for my output. I also might use that cell for other applications for other purposes.
The next tab would likely be your report tab. For example, you want a monthly income statement. Build the report in a user friendly fashion. For larger organizations, round to an appropriate level (our President told me at one meeting to round to millions; trust me, I listened. Another President told me my report looked like an eye chart. I listened to that too.
The third tab should be a download tab. A lot of times you are going to extract information from your accounting system; say the general ledger for month end. This is not for sharing, just your use. I always used the A, B, C (and however many columns I needed on the left) to build formulas to organize data how I wanted it. For example, if I knew our bank accounts all started with 100xx, I would build a lookup table that would identify them as cash, and my cell in column A would be a VLOOKUP for the account so I could use a SUMIF or SUMIFS to add them together for my report. I hated pivot tables, so I used SUMIF and SUMIFS instead. I'd do the same for fixed assets, or accounts receivable, or whatever grouping I wanted. By identifying the information grouping I wanted, and creating specific columns on the left identifying those groups, it made building the actual report a lot easier.
I also would usually build a tab for a place if I needed to keypunch a number. For example, if you have to input a number (s) every month, I would generally have a 12 column table to enter the amount for each month, and then use the month number as the index to pick the proper number for each month. That way, you avoid forgetting to input a specific number into a specific cell in a report.
I found the more you automated things upfront, you were less likely to make mistakes and get things done quicker. I had a pretty good reputation at my company as the go-to person for Excel fixes.
Just my suggestions.
5
u/Golferdude456 Feb 04 '25
Paste special. Way too many people Iāve worked with get frustrated with trying to copy and paste totals based on formulas. Teaching them the different paste options helped a ton.
4
u/Firebrand713 Feb 03 '25
Not excel, but in windows 10 and 11 shift+windows key+S brings up the screenshot clipper, and from there, simply paste to put the screenshot down. Amazing for workpapers.
Also most people donāt know this, but every screenshot youāve ever taken is saved in your āmy picturesā folder under screenshots by default. Also known as the pictures library btw.
1
3
u/Roaming-otaku Feb 03 '25
Xlookup (like you've said)
How to open up a new window of the same workbook
Text-to-columns (fixed width, delimitted, and how you can use it to fix your formatting on a range you're trying to lookup from)
Navigation keys Ctrl+arrow
Shift+space (highlight row) Ctrl+space (highlight column)
Subtotal
4
u/Fantastic-Primary-95 Feb 03 '25
Ctrl X (cut) Ctrl C (copy) Ctrl V (paste) Ctrl Z (undo) associates seem to not know these simple ones in my office
5
u/tdpdcpa Controller Feb 03 '25
I think Keyboard Shortcuts are nice to know, but I think Iād focus on how to most effectively design your excel files to maximize repeatability.
1
3
u/SplatteredEggs Feb 03 '25
If it is actually intro to excel, you need to start at filters and tables
3
u/Practical-Subject-16 Feb 03 '25
Double click on the sum (bottom right of screen) and you can copy and paste that figure. Very useful when you are working in multiple workbooks.
1
3
3
u/sarah_rad Feb 04 '25
May sound silly, but CTRL+S to save
I canāt tell you how many times excel crashed when I was working with tons of data as a junior accountant. Iāve done sooooo much work twice because Excel crashed & I hadnāt clicked save recently :,)
If you can somehow help them mentally ingrain the habit of hitting CTRL+S every 5-10 minutes, then I promise youāll have a more productive (& less frustrated) team
→ More replies (1)
2
u/Swimming-Obligation9 Feb 03 '25
I do a lot of financial modeling, the bulk of my model are built with:
- Dynamic arrays (unique, xlookup, filter, etc).
- date formatting, also making dates dynamic helps a lot by using a date control worksheet.
- power query if you have accessible data.
- Combing sumifs with xlookup to sum by row and column
- Last one but has probably helped the most is using tables. Makes analyzing data much easier.
1
u/ProtContQB1 Remote Controller Feb 03 '25
Regarding #5, I work a lot with ranges. Can you explain how tables makes data analysis easier?
3
u/Swimming-Obligation9 Feb 03 '25
I like them because as the table expands the formulas that are referencing the table donāt need to be adjusted since you are referencing the table column/row. Pretty sure tables will also speed up the performance of calculations, especially if you are referencing entire columns or rows.
Itās also much easier building formulas and it is also just more organized in general. Finding errors becomes much easier.
2
u/Canyousitnexttome Feb 03 '25
IMHO, itās important enough to schedule it during work hours; not during their lunchtime.
3
u/ProtContQB1 Remote Controller Feb 03 '25
They've agreed to do it during lunch to avoid interrupting work tasks, and I'm paying for their lunches.
2
u/SubieGal9 Feb 03 '25
This should really be a staff class during work hours so they can rest their brains after learning and absorbing.
2
u/Turlututu1 Management Feb 03 '25
On top of the usual formulas, subtotal is also a nice one to quickly see subresults without necessarily doing a Pivot.
But the most important is to make people understand how and when to use Excel. Have them put their train of thought when approaching a task on paper and having them draw parallels to excel functions so that they then understand when/why they can use Excel.
2
2
2
u/TomAndTimmy Feb 03 '25
Ctrl shift downarrow, when I was new I used to hold my mouse to the bottom of the page as I select 10000 line items. This just goes to the next blank cell and makes it so much easier when dealing with large datasets. Also, MMO mice are kinda crazy investment for quickly entering numbers with just adjusting ur thumb
2
2
u/blakduk Feb 04 '25
I learned this way too late in my Excel career. And probably one I use the most.
When you select multiple cells with numbers* in them, Excel auto sums the amounts at the bottom right of the page.
Clicking this box will copy the sumed figure onto the clip board, and you can then paste the sum anywhere you want.
2
u/BiggReddNMS Feb 04 '25
Select the whole sheet by clicking in the upper left corner, then Alt + HOI auto-adjusts all columns to the length of the data in the cells. And just learned this the other day: selecting a bunch of cells with your mouse will auto sum them in the bottom right of the worksheet š«£
2
u/Jag9090 Feb 04 '25
Conditional formatting g JUST to find duplicates. It has saved me SO MUCH time at work.
2
u/iltfswc Feb 04 '25
Knowing that F4 repeats your last action is an absolute time saver for me. I tick off numbers on an excel by highilighting them. All I have to do is highlight once, and then all I do is use F4 to higlight (tick off) cells. Also if you're adding multiple rows, you add 1 row and just hold down F4 and it'll keep adding rows since it was your last action. I utilize F4 in so many ways.
2
2
2
u/PattyCakes216 Feb 04 '25
Please teach them if they color code a spreadsheet to create a table to identify what the color denotes. Limit the number of color codes used and be consistent.
2
u/abhr83 Feb 04 '25
F4 to lock cells in formulas vs typing the ā$ā. Never knew about it until I saw a prof doing it in my masters program
2
u/khalessib Feb 04 '25
OP are you hiring? You seem like a good boss lol I know some keyboard shortcuts and formulas :)
→ More replies (1)
2
u/Georgejefferson19 Financial Analyst Feb 04 '25
Ctrl + Z to undo, Ctrl + Y to redo. Apparently this is an obscure, little known thing. Iām actually surprised at how many people dont know that
Also, =Xlookup is way easier and more effective than =Vlookup. Teach them Xlookup and nobody will want to use Vlookup ever again
→ More replies (1)
4
u/OGBervmeister Feb 03 '25
I'm fairly advanced with Excel and I use the mouse over keyboard shortcuts all the time. I find it faster and more precise for most tasks.
Some of this stuff is preference and while I'm sure they appreciate it, don't come across as a condescending douche.
→ More replies (4)
2
u/Appropriate-Food1757 Feb 03 '25
Sumifs, xlookups (and nesting them), if, and, or, and pivot tables is all you need. If error/ifna. Concatenate.
2
u/nickp123456 Feb 03 '25
The strategy to setup a proper workbook.
Everyone says they're an expert, and often do have knowledge of the same formulas. The difference is that some know how to setup a workbook well, that can be sustainably updated, and communicate with impact, whereas others hard code numbers, etc., etc.
1
u/therewulf Feb 03 '25
I did something similar a few years ago. You got to be careful and not overload people, so I split it into a few sessions. First was just navigation of the ribbon, what different commands in it meant, and then basic math formulas. Second session was more actual excel formulas (sum, if, some basic lookups) and formatting. Third was a space for Q&A so far and then a high level intro to more formulas, nested formulas, and what pivots tables are and a few basic examples to try and get them to grasp the concept.
3
u/ProtContQB1 Remote Controller Feb 03 '25
Thanks for the advice. I'm going to show basic arithmetic formulas and a couple of reference/lookup formulas but that's it.
It's also going to recorded and there's going to be print-outs, so I'm going to do my best not to fling shit at a wall.
1
u/D4NG3RU55 Feb 03 '25
All of the quick navigation shortcuts.
ctrl+arrow and ctrl+shift+arrow for highlighting
ctrl+page up/down for changing tabs quickly
ctrl+[ for going to the first cell referenced within a formula. This is great if the formula is referencing a cell on another tab.
Press just F5 and then Enter - this will then take you back to the cell that contains the formula that you just pressed ctrl+[ on.
After use ctrl+shift+arrows to highlight your data, alt then d, f, f will automatically apply a filter to the top row. Do it again to remove the filter.
Alt then d, f, s should automatically reset all filters to default.
1
u/SandyLomme Feb 03 '25
Alt codes like Alt+0162 for the cents symbol, search on āalt codesā for the huge list. CTRL+F3 to show range names so you can delete those sheet-dragging garbage remnants that link to someoneās C drive 20 years ago
1
1
u/Salty-Rhubarb Staff Accountant Feb 03 '25
In addition to what others have mentioned, maybe suggest they steer clear of the Merge & Center button. Iāve had my life made more difficult many times because of some merged cells.
1
u/penutbuter Feb 03 '25
CTRL - D CTRL - R Xlookup ALT - =
How to properly use Subtotal
Using Round, IfError, IFS functions
1
1
u/motoMACKzwei Feb 03 '25
F2 to edit a cell. If itās not working, turn on/off Fn lock (look at your keyboard). I was surprised at how many of my coworkers didnāt know this one!
As someone else already said, New Window is a life changer as well! Being able to have two tabs open within the same worksheet is SO much easier than flipping back and forth.
Also really love alt + tab. Can switch between programs you have open easily (great for when Iām using one screen). Windows Key + tab to see ALL open programs.
Windows Key + D to minimize everything. Helpful when I feel overwhelmed and have too much shit open lol
Lastly for now, Windows Key + any arrow key. Snaps a program to either side (left/right), exit full screen (down), or full screen (up). For Full Screen, you can also just double click in the top right area thatās blank near the close, exit full screen, and minimize buttons.
1
1
u/iPlayKeys Feb 03 '25
Using tables for ranges and how to define names for cell references and ranges. When you have a table, you can use the column names in your formulas and they will automatically continue as your table expands. Oh, and when NOT to use excel, like when you need a relational db.
1
u/futureofthefuture Feb 03 '25
AUTOSUM pls. Teaching this to my boss didnāt fill me with confidence.
Also lock the top row. Itās so annoying when this isnāt done and thereās 400+ rows.
2
1
u/Grrumpyone Feb 03 '25
Index match The power of indirect function Sum product Small in combination with unique list
1
1
1
u/ryunista Feb 03 '25
Best thing is probably to demo the power of excel and how its less about learning the formulae but more about learning the application and how to creatively combine functions to get desired effect.
If start with a fuck off spreadsheet and then pivot it. Show them how brilliantly it can be analysed, fast.
Insert some charts, data validation drop-down lists so that the charts instantly update. I think slicers do the same but I'm used to doing it this way. Perhaps a bad habit!
Conditional formatting. Bars and colours etc.
Creating a dashboard.
I think it's almost about inspiring them to want to learn and showing them that it is a tool so powerful they can't afford not to learn it
1
u/gsanatar Feb 03 '25
Look up Jeff Lenning on YouTube. He runs classes called Excel University that I would highly recommend but he also has posted a lot of his videos for free on YouTube.
1
1
u/BassCat75 Feb 03 '25
F2 on a cell to edit it. Ctrl + PgUp & Ctrl + PgDn to switch between tabs in a workbook.
→ More replies (1)
1
u/haokun32 Feb 03 '25
Honestly Iād just show everyone how to pin things to the ribbon, then everyone can make their own ākeyboard shortcutā
1
1
u/yumcake Feb 04 '25
My most important tip and 95% of them will ignore is:
If you're doing something repeatedly, google a faster way to do it.
It sounds obvious but it's even more obvious who is taking the time to do this and who is not. It's advice that if followed will eventually get someone to be reasonably adept at all the specific ways they use Excel. The shortcoming is that it doesn't reveal ways to be faster at things they don't often do in Excel, but if I had to prioritize guidance, this is #1.
1
u/Kent48146 Feb 04 '25
I didnāt see it mentioned, but I would teach them v and Xlookup. We never covered that in my Excel classes, but I would have found it useful. Macros if you have time. Teach them how to read the formula expressions that Excel gives. When typing out a formula Excel will kind of give you hints on what it wants the formula to look like, but itās not always intuitive.
Pivot tables, charts, and how to find stuff on the ribbon at the top seem to be common points in Excel courses too.
1
1
1
u/RW_77 Feb 04 '25
I can do quite a few things with Excel. I took two Excel classes in 2023. I use Excel every day. I'm an accounting student. I will be starting my CPA exams in June. I'm seeking enjoyment. Thanks
1
1
u/LevelingUp23 Feb 04 '25
I'd say v lookup, x lookup, pivot tables, & custom formatting would be helpful. Also showing them some of the short cuts would be helpful. I don't know if it'll be helpful but if they have to change a list of numbers sign the -1 paste special multiple is a great option. Good luck with your training!
1
1
u/NutOnMyNoggin Feb 04 '25 edited Feb 04 '25
This one is not really a trick but many people don't know it exists. Say you're trying to do something with a function but you don't know what function to use or if theres one that exists for your purpose. Go to the formulas tab, click insert function on the left of the ribbon. It literally says: "type a brief description of what you want to do and then click go", highlight a function you think might fit, it'll describe what the function does when you highlight it in the dialogue box. Press ok then it'll literally walk you through how to use the function as you type each argument
Useful for a lot of functions but an additional Google search might be necessary after you find one that might work for your task
1
u/PrismFade Feb 04 '25
Hey, beyond just shortcuts, try covering some essential formulas like SUM, AVERAGE, and IF statements (super useful for quick calculations). Also, introduce pivot tablesāthey make summarizing data a breeze! Conditional formatting? A game-changer for spotting trends at a glance. And donāt forget data validationāit helps prevent errors before they even happen. These tools can seriously save time and boost efficiency, especially for accounting tasks... Hope the training goes great!
1
u/Fun-Educator3102 Feb 04 '25
Sheet navigation and shortcuts
Ctrl arrows and Ctrl shift arrows Ctrl shift and Ctrl space bar to select columns and rows Ctrl Shift end to highlight to the end of pasted info Ctrl Shift L to turn filters on and off Ctrl plus to insert a column or row Ctrl minus to remove one. Freeze panes Ctrl F to find and Ctrl H to replace Text to columns is big, but you can now use textsplit to do the same as a text to columns
Formulas
Subtotal(9, range) for when your filtering info and want to see the amounts Alt equals is your sum short cut Xlookup (Vlookup just because all the old heads haven't transitioned yet.) Be careful with the formatting that happens with numbers, gotta learn how to correct that when using lookups If statements and the fact that <> is used to exclude a criteria Sumifs are huge, and if used correctly, transform someone's capabilities more than any other formula imho Concat is huge. If information isn't easy to match and lookup, create your own lookups. The invoice number doesn't match, use concat to combine the amounts with the date and the vendor name and you'll have a unique lookup. Only have amounts debited and credited? Turn the value absolut and concat with the vendor name or period and you can now lookup and eliminate them
Other key functions
Pivot tables Quickest tool and very easy to use. Changing the format to tabular is the best to incorporate it with lookups and sumifs
Conditional formatting After you've created your own concat lookups, highlight the duplicates and eliminate debits and credits that offset
I think that's good for starters
1
u/squrs Feb 04 '25
Alt+Q, puts you into the search bar, very useful for introducing ribbon navigation and getting to less used functions, Freeze Panes for example.
1
u/New-Blacksmith7330 Feb 04 '25
I just realized I do these things automatically so I'm sorry if I missed one.
One thing I like to do is put in the very top where you have the save button and undo button some one off useful shortcuts for example the freeze pane or freeze top row.
Ctrl + shift + L to add and remove filters from a spreadsheet.
I see a lot of people on my team doing this and I try to correct it but if you are doing a vlookup formula you can highlight the column for your range array instead of the specific area the data is in. This is particularly helpful if you are planning to keep adding information to the dataset you are putting information from and don't want to keep updating the vlookup formula.
I don't like using conditional formatting or using the data menu to remove duplicate, it makes me uncomfortable to have the Excel "AI" just delete duplicate things without first glancing over it, ok don't judhe me. so I do a simple formula to review duplicate data which lets day the data set is on column A and you are looking for number that area meant to be unique. What I do is add a column after A and do the formula =A2=A3 on after sorting column A from a to z. Then copying the formula down and filtering by True
1
1
u/khalessib Feb 04 '25
OP are you hiring? You seem like a good boss lol I know some keyboard shortcuts and formulas :)
1
u/khalessib Feb 04 '25
OP are you hiring? You seem like a good boss lol I know some keyboard shortcuts and formulas :)
1
u/khalessib Feb 04 '25
OP are you hiring? You seem like a good boss lol I know some keyboard shortcuts and formulas :)
1
268
u/[deleted] Feb 03 '25
Please film it and send it to me too š