How To Create A Navigation Menu Pane In Excel

How To Create A Navigation Menu Pane In Excel - No VBA

Рет қаралды 191,972

Күн бұрын

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

Пікірлер: 128

@alinabankal3018 3 жыл бұрын

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!!

@Pinexl 3 жыл бұрын

Glad it helped!

@syedashfaq7245 2 жыл бұрын

Same here

@noblestride 4 жыл бұрын

THANKS A BILLION!!! MADE MY SHEET CLUTTER FREE AND AMAZING!

@Pinexl 4 жыл бұрын

Happy you found it so useful!

@anthonibritto5409 4 жыл бұрын

Your videos are so amazing and has inspired a lot. Please keep up the spirit.

@Pinexl 4 жыл бұрын

Thank you very much! Really appreciate it!

@yobrunogabriel 2 жыл бұрын

Very creative! Thanks

@darkoldrocknroller 4 жыл бұрын

WOW, very impressive. Thank you.

@Pinexl 4 жыл бұрын

Thank you very much! We're happy you like it!

@DarA-by4fl 3 жыл бұрын

Thank you for this tutorial. This is really impressive!

@Pinexl 3 жыл бұрын

Thank you for watching!

@Mamounhefni 3 жыл бұрын

This smart, simple and elegant !!

@Pinexl 3 жыл бұрын

Thank you very much!

@Jennk-lb5no 3 жыл бұрын

Nice! Thanks for sharing! can't wait to try this for my next report

@Pinexl 3 жыл бұрын

Happy to help! It should look amazing!

@alonsozuniga101 3 жыл бұрын

Thanks a lot mate, excellent explanation!

@Pinexl 3 жыл бұрын

Glad you liked it!

@shaniwafa 4 жыл бұрын

Awesome 👍👍👍 Very simple Technic Result is fantastic!! Thanks for idea Expecting more tricks and animation Technic in future

@Pinexl 4 жыл бұрын

More to come! Thank you for your comment!

@lynasoua9209 2 жыл бұрын

Just because you used Game of Thrones character as team member names, you got my like!!!

@mnsoyan 4 жыл бұрын

Amazing idea!!

@Pinexl 4 жыл бұрын

Thank you very much!

@AbuTalha-eo7pr 4 жыл бұрын

Very Helpful. Thank you

@Pinexl 4 жыл бұрын

Thank you for watching!

@pv0315 Жыл бұрын

Beautiful work

@diassetefle9775 4 жыл бұрын

Thanks for this Amazing tutorial, it looks like a web page

@Pinexl 4 жыл бұрын

Glad it was helpful!

@ganeshshetepatil9829 4 жыл бұрын

Thanks for such nice demonstration...

@Pinexl 4 жыл бұрын

And thank you very much for your comment!

@manjunathk2767 4 жыл бұрын

Simple but very rich. Thanks

@Pinexl 4 жыл бұрын

Thank you very much!

@84rinne_moo 3 жыл бұрын

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 .

@recipehacker9752 3 жыл бұрын

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.

@helendemore2225 8 ай бұрын

Excellent presentation 🙂

@emtyas 5 жыл бұрын

Just amazing.

@Pinexl 5 жыл бұрын

Thank you very much!

@mydigitalquill8945 2 жыл бұрын

very well done!!

@Pinexl 2 жыл бұрын

Thanks a lot!

@eminimamoglu3072 4 жыл бұрын

Thanks a lot!

@Pinexl 4 жыл бұрын

Thank you very much! Glad you are finding it useful!

@luisenc789 Жыл бұрын

You're a genius man! I love you!

@Pinexl Жыл бұрын

Thanks a lot! We love you too!

@arijturner8419 Жыл бұрын

WOW I NEED TO DO IT NOW

@belalzaqaibeh5072 Жыл бұрын

Perfect

@Hermetic_ 11 ай бұрын

Nice thanks

@suhaimiabdazais 2 жыл бұрын

Just found and subscribed .. This is what I'm looking for.. Tqvm

@Pinexl 2 жыл бұрын

Thanks! Happy you find the content useful!

@ehsansepahsalari617 Жыл бұрын

So helpful, Thank you so much

@Pinexl Жыл бұрын

Glad it was helpful!

@Blue4736. 3 жыл бұрын

That was beautiful

@Pinexl 3 жыл бұрын

Thank you!

@hugyourdata1549 Жыл бұрын

Thank you so much!!!

@Pinexl Жыл бұрын

Glad it helped!

@hamedsougue9818 3 жыл бұрын

Nice lesson thank so much

@Pinexl 3 жыл бұрын

Glad you liked it!

@futuresuperstar4life 2 жыл бұрын

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

@myrtle411 2 жыл бұрын

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)?

@Pinexl 2 жыл бұрын

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.

@reinforcedmacky9270 Жыл бұрын

Thanks

@allen6031 4 жыл бұрын

This video made me to subscribe.... 👍

@Pinexl 4 жыл бұрын

Thank you subscribing! More videos are coming soon!

@MsBoolo 2 жыл бұрын

Very impressive 👏 👌 👍

@Pinexl 2 жыл бұрын

Thank you! Cheers!

@mansiagarwal3715 3 жыл бұрын

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.

@Pinexl 3 жыл бұрын

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!

@dorsoolms4684 Жыл бұрын

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.

@kadirbasar1547 4 жыл бұрын

Thank you

@Pinexl 4 жыл бұрын

You're welcome!

@clarissaconvertino4742 4 жыл бұрын

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?

@Pinexl 4 жыл бұрын

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!

@dorzhimunkuev3959 4 жыл бұрын

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.

@shimonkephaonwuneme3326 2 жыл бұрын

@@dorzhimunkuev3959 sure I did same using text box for everything. Then modify textbox size and properties to not move with the cells

@victormerino4439 4 жыл бұрын

Thanks bro

@Pinexl 4 жыл бұрын

Happy to help!

@arulpriyacreations 2 жыл бұрын

How to give different datas based on the dates in one row in data entry form

@NixelPresentation 3 жыл бұрын

Yes. I could learn something new.

@Pinexl 3 жыл бұрын

Hope it helps!

@rajnishbedi7470 3 жыл бұрын

Very Good

@Pinexl 3 жыл бұрын

Thank you!

@chanchaldas6229 4 жыл бұрын

Nice bro

@Pinexl 4 жыл бұрын

Happy you like it!

@inglesfuncional 3 жыл бұрын

Is there a way to freeze the navigation menu, so it would stay visible when we scroll vertically? Thanks

@Pinexl 3 жыл бұрын

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.

@ITCE2018 4 жыл бұрын

Nice

@Pinexl 4 жыл бұрын

Thank You!

@harriscastro535 5 жыл бұрын

What if the excel tabs are not hidden in another pc. Would it still hide the tabs?

@eclippse 5 жыл бұрын

Hi Mark, thanks for asking! This is a workbook-level setting, so yes - the sheets will stay hidden on all PCs.

@millet2963 3 жыл бұрын

How to lock your template and only use the slicer and the navigation panel?

@Pinexl 3 жыл бұрын

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.

@user-ou2wu7iy7k Жыл бұрын

Then how to take Print out, because when i tried, the print is coming with Dashboard, kindly advice

@Pinexl 9 ай бұрын

Hello! You can hide the navigation panel by hiding the columns it is in. This way you can print the spreadsheet without it.

@AD-qb2kr 3 жыл бұрын

I have also this annoying white flash between the sheets are loaded, maybe its because of tons of hyperlinks. Any solution?

@Pinexl 3 жыл бұрын

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.

@kkm5384 3 жыл бұрын

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?

@Pinexl 3 жыл бұрын

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.

@kkm5384 3 жыл бұрын

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

@julie92114 3 жыл бұрын

Do you have this written out step by step ?

@Pinexl 3 жыл бұрын

Hello and thanks for the comment! At the moment this information is available only on KZfaq.

@tomite2001 2 жыл бұрын

How do you call your forms or dashboards from those menu items?

@eclippse 2 жыл бұрын

Hi and thanks for the question! The menu items are actually text cells, which have a hyperlink leading to different sheets in the workbook.

@mazhariqbal3654 4 жыл бұрын

Its Ok, but problem is that when we scroll down the sheet, navigation cells navigate too, any solution?

@Pinexl 4 жыл бұрын

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.

@allabout1135 5 жыл бұрын

I think is time for Pwer BI ;)

@Pinexl 5 жыл бұрын

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!

@harshitgupta8844 4 жыл бұрын

Sir I already made my project and now I have to do this thing... Please help me sir

@Pinexl 4 жыл бұрын

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

@TheLionOfJudahTheLambOfYHWH 3 жыл бұрын

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 ?

@Pinexl 3 жыл бұрын

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.

@kuldeepsinghbhati1156 3 жыл бұрын

Why you hide column A

@Pinexl 3 жыл бұрын

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.

@kuldeepsinghbhati1156 3 жыл бұрын

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

@Pinexl 3 жыл бұрын

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

@kuldeepsinghbhati1156 3 жыл бұрын

@@Pinexl thanks bro... You are so helpful... God bless you

@Pinexl 3 жыл бұрын

@@kuldeepsinghbhati1156 You're welcome!

@OrdinaryCritic 2 жыл бұрын

I’m just wondering. What if I scrolled down? Won’t the pane also scroll down? Thanks in advance. :)

@Pinexl 2 жыл бұрын

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.

@OrdinaryCritic 2 жыл бұрын

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

@Pinexl 2 жыл бұрын

Thanks, I'm happy you you're finding this useful!