How To Create A Navigation Menu Pane In Excel - No VBA
Vložit
- čas přidán 11. 07. 2024
- Bring the Power of AI to Excel:
➤ www.pinexl.com/pine-ai-excel-...
Pro-top: If you are going to hide or delete rows in the document, this could also impact the navigation menu sheet names. To avoid that, you can try using rectangular shapes with text in them, instead of writing in the actual cells. Just add shapes with the same size as the cells and with the same colour, as the navigation menu background, write the sheet names in them, and link them to the respective places in the document. To lock them in place and to avoid hiding them when filtering data, right-click on a shape, select Format Shape..., go to Size & Properties, Properties and select Don't move or size with cells. This way, the navigation menu items won't get hidden when you hide or delete rows.
Create this Navigation Menu Pane in seconds with our Add-In Pine BI:
➤ www.pinexl.com/pine-bi-excel-...
--
Learn how to create a navigation menu pane in Microsoft Excel. Perfect for organizing your Spreadsheets and Dashboards in a modern, professional and engaging way.
➤ If you found this video useful, please Like, Share and Subscribe!
Need amazing Excel Dashboards and Charts? We've got you covered!
We can support you by updating your existing Spreadsheet visualizations or creating new, based on your data from scratch! For more information, visit our Services page:
➤ www.pinexl.com/consulting
Project Management Template, which was featured in the video:
➤ www.pinexl.com/product-page/p...
Check out our other premium Excel Templates:
➤ www.pinexl.com/templates
More free tutorials are available on our website:
➤ www.pinexl.com/excel-tutorials
Follow us on:
➤ Facebook: / pinexl
➤ LinkedIn: / pinexl
Modern & Professional. No Macros. Completely Unlocked.
Music: www.bensound.com
This is exactly what I was looking for. Thank you so much for creating this. Your steps were so easy to follow I'm well on my way to making a menu driven dashboard! Thank YOU!!
Glad it helped!
Same here
THANKS A BILLION!!! MADE MY SHEET CLUTTER FREE AND AMAZING!
Happy you found it so useful!
Very creative! Thanks
This smart, simple and elegant !!
Thank you very much!
Thanks a lot mate, excellent explanation!
Glad you liked it!
Your videos are so amazing and has inspired a lot. Please keep up the spirit.
Thank you very much! Really appreciate it!
Nice! Thanks for sharing! can't wait to try this for my next report
Happy to help! It should look amazing!
WOW, very impressive. Thank you.
Thank you very much! We're happy you like it!
Thank you for this tutorial. This is really impressive!
Thank you for watching!
Beautiful work
Simple but very rich. Thanks
Thank you very much!
Awesome 👍👍👍
Very simple Technic
Result is fantastic!!
Thanks for idea
Expecting more tricks and animation Technic in future
More to come! Thank you for your comment!
You're a genius man! I love you!
Thanks a lot! We love you too!
Excellent presentation 🙂
Amazing idea!!
Thank you very much!
Very Helpful. Thank you
Thank you for watching!
Just found and subscribed .. This is what I'm looking for.. Tqvm
Thanks! Happy you find the content useful!
WOW I NEED TO DO IT NOW
very well done!!
Thanks a lot!
Thanks for such nice demonstration...
And thank you very much for your comment!
Just because you used Game of Thrones character as team member names, you got my like!!!
Thanks a lot!
Thank you very much! Glad you are finding it useful!
So helpful, Thank you so much
Glad it was helpful!
Thank you so much!!!
Glad it helped!
Thanks
Nice thanks
Just amazing.
Thank you very much!
Perfect
That was beautiful
Thank you!
Thanks for this Amazing tutorial, it looks like a web page
Glad it was helpful!
Nice lesson thank so much
Glad you liked it!
A way to alter this is to create the dashboard on one tab, and then place a link in every other tab that links back to the dashboard tab. This might help some ppl with the kind of sheets/data you may have. I also would place this just above my headers and then freeze the two top rows to make it able to scroll through my data easily and filter easily with no problems whilst keeping the link back to the main dashboard handy at all times .
Thanks ! Please share how you’d freeze those rows, as you had suggested (I.e., to keep them at the top of the window even during scrolling). I was not aware freezing rows was possible . Thank you.
Very impressive 👏 👌 👍
Thank you! Cheers!
This video made me to subscribe.... 👍
Thank you subscribing! More videos are coming soon!
Yes. I could learn something new.
Hope it helps!
Thank you
You're welcome!
Thanks bro
Happy to help!
Very Good
Thank you!
Nice bro
Happy you like it!
Hi, The tutorial is simple and easy to understand. I wanted to ask that how to hide the sub menus of "Data Input" from the other two menus.
Hello and thanks for the comment! The submenus are only shown in the Data Input sheet. If you don't add them in the others, they won't be visible!
I NEED HELP! PLZZ
im trying to do something similar but more specific i guess.
im a server and ive been working on an excel file to track my tips and sales percentages tip outs etc, it's very complex and im completely new to excel so it took me like two months.
i'm trying to recreate an app that i was using but doesn't track enough details.
in this app there are tabs on top for sorting the data yearly, monthly, weekly and biweekly and "all"
and then at the bottom you can toggle between all of the data with arrows, between the 2 arrows there are 2 dates based off of the filter (the corresponding dates of the month, the week etc) but youre not limited to just "this month" and "last month", but you can continue to toggle between all months by clicking the arrow.
i've tried with slicers and it kind of works but not as efficient or specific as i'd like it to be
PLEASE TELL ME if you think that is at all possible on excel
This is a huge help, however, is there a way to prevent the menu pane from being distorted page-to-page because of row height changes in the datasets (e.g. lots of wrapped text creating a larger row height)?
Good question! Unfortunately, except for locking the sheet, so that the height of the rows is not changed, there really isn't a way using this method. As an alternative, you could use containers, objects or text boxes with hyperlinks, which you can adjust, so that they are not sized with cells.
Nice
Thank You!
Does this work on excel for ios? Cause it looks like it doesn't (at least not with shape buttons) and vba doesn't work either there. I would like to have buttons on my excel sheets on the ipad but so far no luck. They behave differently than on windows and when protecting a sheet they don't work/react at all.
Hi, thank you for the nice tip. In my case, each tab contains table, whose rows I want to filter eventually. Unfortunately the filtering function will hide certain rows, messing up the navigation pane. Any idea on how to fix this?
Good question! Unfortunately, this is the disadvantage of using this method.
You have two alternatives:
1. Build the navigation menu horizontal in the first rows, not columns. This way you could filter data without breaking the navigation menu. However, if you hide columns, you'd have similar issues as in the beginning.
2. Instead of using cell values to write the sheet names, you can insert buttons - some simple square shape for example, and add a link in it to the other sheets.
Hope this helps!
Hello there, I just inserted a text box and attached the link. Then I changed the parameter of object positioning: move and size with cells to don't move or size with cells. Afterward while filtering it does not disappear or move. Generally, it looks the same as shown in this tutorial and you can apply some additional effects in shape tool. I hope it helps you.
@@dorzhimunkuev3959 sure I did same using text box for everything. Then modify textbox size and properties to not move with the cells
How to give different datas based on the dates in one row in data entry form
Is there a way to freeze the navigation menu, so it would stay visible when we scroll vertically? Thanks
Hi and thanks for the question! You could build the navigation menu in the top rows horizontally, instead of vertically, and then freeze the rows. This way it will stay in place when scrolling down.
Then how to take Print out, because when i tried, the print is coming with Dashboard, kindly advice
Hello! You can hide the navigation panel by hiding the columns it is in. This way you can print the spreadsheet without it.
How to lock your template and only use the slicer and the navigation panel?
Hi and thanks for the comment! You can explore the Protect Sheet option in the Review tab. You can for example lock everything apart from selecting a cell.
I have also this annoying white flash between the sheets are loaded, maybe its because of tons of hyperlinks. Any solution?
Maybe this depends on the Excel version you're using. If you try it in a new blank document, this will show if it is because of the version of something else.
Its Ok, but problem is that when we scroll down the sheet, navigation cells navigate too, any solution?
Hi and thank you for your comment!
You could make the menu horizontal, not vertical and freeze the top rows. This will make sure the menu is always visible when scrolling down.
What if the excel tabs are not hidden in another pc. Would it still hide the tabs?
Hi Mark, thanks for asking!
This is a workbook-level setting, so yes - the sheets will stay hidden on all PCs.
Do you have this written out step by step ?
Hello and thanks for the comment! At the moment this information is available only on CZcams.
How do you call your forms or dashboards from those menu items?
Hi and thanks for the question! The menu items are actually text cells, which have a hyperlink leading to different sheets in the workbook.
I think is time for Pwer BI ;)
Power BI is indeed an amazing tool! However, there are sill many organizations, where Excel is still the main data analysis, reporting and visualizations tool. This is why we want to help people make the best of it!
when we copy the sheets 1:39 then subsequently, whatever changes made in first sheet same is being appear in the all sheets. we need to remove the link of the cell copy.
how to do that?
Hello, it seems like you've selected more than one sheets at the same time. This is why the changes are applied to all of them! Make sure a single sheet is selected after copy/pasting them.
@@Pinexl Thank you. i think its working now. but i dont remember how did. i think i selected first sheet and copied same in all sheets. But what are saying is not not clear to me by saying "...you have selected more than one sheet...". Anyway, that part is OK. i am trying to use this excel book as a template for my write-ups for drafting. Is there a word limit option for the merged cell? i mean i want to select say 20x30 cells and merge them like a text box. then use it like a text box, with word limit. that helps me.
Sir I already made my project and now I have to do this thing... Please help me sir
Hi and thank you for your comment!
Check out our brand new Excel add-in Pine BI, which allows you to create the navigation menu from the video in seconds: www.pinexl.com/pine-bi-excel-add-in
I’m just wondering. What if I scrolled down? Won’t the pane also scroll down? Thanks in advance. :)
Hi, the pane will stay there, but only the colored part. The menu links won't scroll down. Depending on your needs, you can create the navigation in a similar way at the top rows, instead of having it vertically on the left. This way it will be hidden when you scroll down, unless you freeze the top rows.
@@Pinexl, thanks. I’ve managed to use only five main “buttons” that “collapses and expands.” I froze them so they’d stay. It may just be an illusion, but it looks great.
This idea of yours is was an eye-opening game changer.
Thanks, I'm happy you you're finding this useful!
I appreciate your knowledge and effort to teach--- but for the rest of us who have never experienced this, please slow down, bud--way too fast and you did not explain what you did here 2:06-2:21
this section is not seen anymore--where did it go ?
Hello and thank you for your comment! We'll keep the pace in mind for future videos. Regarding your question - the selected cells where simply merged, in order to have enough space for the title.
Why you hide column A
Hello and thanks for asking! When you go to a new worksheet by clicking a hyperlink, cell A1 is automatically selected. By hiding column A, this way it appears that no cells are selected when you jump to the new worksheet. Of course, this is totally up to you if you want to implement or not.
@@Pinexl Nice trick bro 👍
i want your help.. I am working on a dashboard of employees monthly salary... I have one sheet contain employees name and gross salary. Other 12 sheets for monthly attendance. I want that i just select employee name and month in dashboard and it gives me that month earned salary according to total working days on that month. i did this for one month salary. I changed employee name from drop down and i get selected month salary but this happen only for month. How i link employees name to different months..... Please give some idea
@@kuldeepsinghbhati1156 You can use an index-match formula based on two conditions, in your case on employee name and month. We have used such formula in our Dynamic Controls tutorial. A free download with examples is also available. You can find the tutorial here: www.pinexl.com/excel-dashboard-controls-tutorial
@@Pinexl thanks bro... You are so helpful... God bless you
@@kuldeepsinghbhati1156 You're welcome!
Very Interesting but way too fast for learning from it... That is for slow people like myself....
Thank you for your comment and suggestion. We'll keep it in mind!
to fast
Way too fast. Not good. 😏